--- In firebird-support@yahoogroups.com, "women_lover_best"
<talbronstien@g...> wrote:

> I have 3 tables saleshead(pk-tranID,other fields +
> trandate),salesdetails(fk-tranid) and payment(fk-tranid)..now i

have 2

> get all transaction from saleshead for a particular date
> (trandate)..which is easy..but i also need to get all the rows from
> salesdetails and payment based on tranid i get from saleshead for a
> date..what are my options..joins,nested queries,using procedures..?
> what i want is all this data i should be able to get in one trip to

my

> application..where i have fill 3 collections
> thks

If you need to fill three collections, why do it in one hit? If there
are two salesdetails records matching the tranid, but only one
payment, the payment fields would be duplicated.

select ...
from saleshead sh
left join salesdetails sd on (sh.pk-tranID=sd.fk-tranID)
left join payment p on (sh.pk-tranID=p.fk-tranID)
where sh.Trandate = :SomeDate

I assume you need left join so you still get the records if either
salesdetails or payment contains no records for the given transaction.

A more sensible approach would be to query in the salesdetails and
payment separately.

select sd.*
from saleshead sh
left join salesdetails sd on (sh.pk-tranID=sd.fk-tranID)
where sh.Trandate = :SomeDate

select p.*
from saleshead sh
left join payment p on (sh.pk-tranID=p.fk-tranID)
where sh.Trandate = :SomeDate

Assuming you have an appropriate index on saleshead.trandate, this
will be very quick (except fetch time if there is lots of data).

Adam