join or subselect ??

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

"kjc" <ksitron@.elp.rr.com> wrote in message
news:OnIXc.52730$xi6.21027@.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.|||[Top posting is annoying and confusing. Rearranging ...]

"Big Time" <big-time-grizz@.remove-for-spam-hotmail.com> wrote in
news:cgo06l$10ga$1@.lettuce.bcit.ca:

> "kjc" <ksitron@.elp.rr.com> wrote in message
> news:OnIXc.52730$xi6.21027@.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