[Top posting is annoying and confusing. Rearranging ...]
"Big Time" <bi************@remove-for-spam-hotmail.com> wrote in
news:cg***********@lettuce.bcit.ca:
"kjc" <ks*****@elp.rr.com> wrote in message
news:On*******************@fe2.texas.rr.com... Not sure if this is the right group to post this to but.
This is the current query that I have.
select tableA.id,tableB.artist,tableB.image,from tableA,tableB where
tableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20
order by tableB.price DESC'
What I need is, for each row returned I need information from a third
and fourth table. tableC, and tableD.
tableC has information ( the tableA.id = tableC.eventId) that I need
to obtain tableC.accountId = tableD.accountId in order do select the
the binding information in tableD between a
Vendor(name,address..etc..) and tableB.image
Any help would be greatly appreciated.
If I'm reading you correctly, you should be able to do this pretty
easily. In the SELECT statement, list all the fields from each table
that you want to see. In the FROM statement list the tables. In the
WHERE statement list all the parameters and relationships. So:
SELECT
tableA.fields
tableB.fields
tableC.fields
tableD.fields
FROM
TableA
TableB
TableC
TableD
WHERE
tableA.image = tableB.image AND
tablea.id = tableC.eventID AND
talbeC.accountID = tableD.accountID AND
tableB.price >0 AND
tableB.price < 20
ORDER by tableB.price DESC
This will indeed work fine, and the optimizer should have no problem
(given sufficient foreign key constraints and indexing) rewriting it to
run with maximum efficiency. However, future programmers may thank you
if you separate the join relationships from the filtering clauses:
SELECT
tableA.fields,
tableB.fields,
tableC.fields,
tableD.fields
FROM
tableA
INNER JOIN tableB on tableA.image = tableB.image
INNER JOIN tableC on tableA.id = tableC.eventID
INNER JOIN tableD on tableC.accountID = tableD.accountID
WHERE
tableB.price > 0 AND tableB.price < 20
