David,
Thanks. The three left joins worked out perfect for me.
>Elgin,
>
>You can join to your camrule table 3 times:
>
>
>select tenant.scode, tenant.sLastname, ;
> cr1.dEstimated as rent, ;
> cr2.dEstimated as cam, ;
> cr3.dEstimated as tax, ;
> from tenant ;
> left join camrule as cr1 ;
> on tenant.hmyperson = cr1.htenant and cr1.chargetype = rentno ;
> left join camrule as cr2 ;
> on tenant.hmyperson = cr2.htenant and cr2.chargetype = camno ;
> left join camrule as cr3 ;
> on tenant.hmyperson = cr3.htenant and cr3.chargetype = taxno ;
> ... ;
>
>
>where rentno, camno and taxno are the appropriate foreign key values
>
>>OK,
>>I have two tables, TENANT t and CAMRULE cr. I'm doing a LEFT OUTER JOIN camrule ON t.hmyperson = cr.htenant. I am wanting to SELECT t.scode, t.slastname from my tenant table and cr.destimated from my camrule table. Here's the catch. In my camrule table, I have 'rent' type camrules, 'cam' type camrules, and 'tax' type camrules. These camrule types are stored in a third table called CHARGTYP. The camrules table has a field, hchargecode that is the pointer back to the chargtyp table for each type of charge code.
>>
>>In the camrule table there may be more than one record of each type for each tenant. In other words, one tenant may have three RENT type camrules and three CAM type camrules that are for different date ranges.
>>
>>In my result set I want the varying types of camrules that match a certain date criteria to appear on the same record line as separate columns. For example I want the result like this:
>>
>>
>> RENT CAM TAX
>>t.scode t.slastname (cr.destimated) (cr.destimated) (cr.destimated)
>>mwest midwest $8.04 $1.12 $0.93
>>
>>
>>
>>... not like this:
>> RENT CAM TAX
>>t.scode t.slastname (cr.destimated) (cr.destimated) (cr.destimated)
>>mwest midwest $8.04 $0.00 $0.00
>>mwest midwest $0.00 $1.12 $0.00
>>mwest midwest $0.00 $0.00 $0.93
>>
>>
>>
>>I tried doing a SELECT statement for each of the fields, but in some cases I may get more than one record returned, and that's not acceptable when doing a subquery.
>>
>>What's the best way to handle this?
>>
>>Thank you for your help.
