Travis, Thanks a lot! That seems to work perfectly and also cleans up the syntax a bit so I think it's more understandable.
Michael On Aug 30, 2010, at 7:21 PM, Travis Ard wrote: > 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=st...@astroh.org > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
