List:General Discussion« Previous MessageNext Message »
From:Michael Stroh Date:August 30 2010 11:42pm
Subject:Re: Nested join query?
View as plain text  
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:stroh@stripped] 
> 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=1
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

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