List:General Discussion« Previous MessageNext Message »
From:Michael Stroh Date:August 30 2010 10:39pm
Subject:Nested join query?
View as plain text  
Hello everyone. I'm trying to perform a query that acts on 3 tables at once. I'm thinking
I need to produce a set of joins between all three tables to get the results that I want,
but am not sure how to go about it with nesting or maybe there's even a better way. I need
to check TableC for cases where the State is set to 'Yes' and then find the Num fields in
TableB that correspond to the matching IDs between the two. I currently have a query that
performs a right join on the two that will give me the results. 

The problem is that I now have this single column table (TableBC listed below) from the
first query that I then need to perform a query on TableA to find the matching records.
I'd like to make this into a single query but am not sure the proper way to combine them
all or how to perform a join on the values in this temporary table that I'm using.

TableA
Val     Num
1         2
2         3
3         3
4         4
5         4
6         7
7         3

TableB
Num    ID
1          1
2          2
3          1
4          2
5          1
6          1
7          4
8          3
9          5

TableC
ID      State
1        No
2        Yes
3        No
4        Yes
5        No

Currently to get the single column list from TableB and TableC that I want to use to query
TableA, I'm using:

SELECT `TableB`.`Num` FROM `TableC` RIGHT JOIN `TableB` ON `TableC`.`ID` = `TableB`.`ID`
WHERE (`TableC`.`State` LIKE 'Yes') GROUP BY `TableB`.`Num` ORDER BY `TableB`.`ID`

It is possible to have multiple instances of Num in TableB, that is why I'm also doing
some groupings. In this simplified example, the result I get from this query is:

TableBC
Num
2
4
7

And the preferred result once I add in TableA would be:

TableABC
Val
1
4
5
6


So I believe the problem is now is how to insert or merge this into a query that will look
for the results in TableA that I'm really interested in. It looks to be similar to the
query I've already performed, but I'm not sure how to perform a join like this since I'm
referencing a column that only exists in this temporary table that is being built and I
don't want to accidentally reference the fields in TableB or TableC.

Thanks in advance!

Michael

Thread
Nested join query?Michael Stroh31 Aug
  • RE: Nested join query?Travis Ard31 Aug
    • Re: Nested join query?Michael Stroh31 Aug