Query for 3 table comparison

[memkam]

The scenario is: I have 3 tables.

Commercial: NewPrice, OldPrice, Weight, Zone, ServiceType
Residential: NewPrice, OldPrice, Weight, Zone, ServiceType
TransactionReport: Price, Weight, Zone, IsItRes (a boolean)


Task at hand: Write a query, which takes all the records in the transaction table, first of all checks whether it is a residential or Commercial by looking at IsItRes field in the TransactionReport table.

If a specific record is a residential delivery, then matches the service type, weight and zone in the TransactionReport table to the Residential table and displays the Weight, Zone, Price, NewPrice from Residential table and OldPrice from Residetial table.

If record is not residential, then prints similar information from the commercial table.

I have attched my SQL code, any help or suggestions to improve logic will be greatly appreciated.


Select IIF ((TransactionReport.IsItRes = TRUE),

(TransactionReport.Zone , TransactionReport.Weight, TransactionReport.ServiceType, TransactionReport.Price, ResPrice.Weight, ResPrice.Zone, ResPrice.Contractprice, ResPrice.HistoricPrice FROM TransactionReport, ResTable WHere TransactionReport.Zone = ResPrice.Zone AND TransactionReport.Weight = ResPrice.Weight AND TransactionReport. ServiceType = ResPrice.ServiceType),

(TransactionReport.Zone, TransactionReport.Weight, TransactionReport.ServiceType, TransactionReport.Price, ComPrice.Weight, ComPrice.Zone, ComPrice.Contractprice, ComPrice.HistoricPrice FROM TransactionReport, ComTable WHere TransactionReport.Zone = ComPrice.Zone AND TransactionReport.Weight = ComPrice.Weight AND TransactionReport. ServiceType = ComPrice.ServiceType
))

For some reason I am getting a syntax error stating missing operator.

Any help will be greatly appreciated.

Thanks,

Maria

 

[Crowley16]

yeah, you can't use iif or select like that...

try something along the lines of:

select *, iif(IsItRes, (select * from residential where ...), (select * from commercial where ...))
from transactionReport;

not sure if this will work or not though...

 

[PHV]

Why not simply use an Union query, one for residential, the other for commercial ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244