469,887 Members | 1,275 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,887 developers. It's quick & easy.

join or subselect ???

kjc
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.

Jul 20 '05 #1
2 2901
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" <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.

Jul 20 '05 #2
[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
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by John Larsen | last post: by
6 posts views Thread by Dave | last post: by
1 post views Thread by Tom Schindl | last post: by
2 posts views Thread by Morten K. Poulsen | last post: by
2 posts views Thread by frederikengelen | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.