I think you could do away with your right outer join of table B (which will include all rows from B whether or not they match to C), since you are explicitly filtering for C.State like 'Yes'. The intermediate result doesn't necessarily need to be stored in a temporary table. You can include multiple tables in a single query by specifying each table and the join condition. Also, If you just want a distinct list of values, you could use the "DISTINCT" clause as opposed to grouping. Something like the following query should produce the results you're looking for:
select distinct a.val from tablec c inner join tableb b on b.id = c.id inner join tablea a on a.num = b.num where c.state = 'Yes'; -Travis -----Original Message----- From: Michael Stroh [mailto:st...@astroh.org] Sent: Monday, August 30, 2010 4:39 PM To: MySql Subject: Nested join query? 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
