0

I'm using a field class that will filter a lookup list of companies to only show those that provide a certain service.

The 4 tables used are: PURCHVIEW,PCSRVCS,SRVCFETCH and COMPANIES and the method I'm using is:

void    setRelationship(java.lang.String objectName, java.lang.String whereClause)

so I'm restricted to just one object to link to and I've chosen COMPANIES since I want the company information from that table to be displayed in the lookup.

PURCHVIEW has a 'contractnum' attribute which is also included in PCSRVCS

PCSRVCS has the 'contractnum' and 'servicesid'

And finally SRVCFETCH has 2 attributes: 'servicesid' and 'company'

I'm quite lost with how to create the connection after doing:

"contractnum = (select contractnum FROM PCSRVCS where contractnum = :contractnum"

To summarize: Given the contractnum in PURCHVIEW, I need to check it with the contractnum in PCSRVCES --> Check the servicesid in PCSRVCES with that in SRVCFETCH --> Match the companies that have that serviceid in SRVCFETCH with the information in the COMPANIES table.

AuthenticReplica
  • 870
  • 15
  • 39

2 Answers2

1

You can use the sub-query approach you have already started to join multiple tables together.

This query may not have the correct table and field names, but it should be very close, based on the information supplied in your question.

contractnum in (
    select contractnum from
            srvcfetch sf
            inner join pcsrvces ps on ps.serviceid = sf.serviceid
            inner join purchview pv on pv.contractnum = ps.contractnum
        where
            pv.contractnum = :contractnum
)
Dex
  • 1,241
  • 6
  • 13
0

You might be able to accomplish this by creating custom view in the SQL management studio for setup/filter the dataset that you want from the 4 tables. This view will only have contractnum field based on the filtering criteria that you want. (I.e. create view CustomViewName as select contractnum from table1 join table2 join table3 etc. ). Then in your whereclause contition all you need to do is contractnum in (select contractnum from CustomViewName).

user3642087
  • 1