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.=20

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` =
=3D `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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Nested join query?

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@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_ard@hotmail.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Nested join query?

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:
>=20
> select distinct a.val
> from tablec c
> inner join tableb b on b.id =3D c.id
> inner join tablea a on a.num =3D b.num
> where c.state =3D 'Yes';
>=20
> -Travis
>=20
>=20
> -----Original Message-----
> From: Michael Stroh [mailto:stroh@astroh.org]=20
> Sent: Monday, August 30, 2010 4:39 PM
> To: MySql
> Subject: Nested join query?
>=20
> 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.=20=

>=20
> 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.
>=20
> TableA
> Val Num
> 1 2
> 2 3
> 3 3
> 4 4
> 5 4
> 6 7
> 7 3
>=20
> TableB
> Num ID
> 1 1
> 2 2
> 3 1
> 4 2
> 5 1
> 6 1
> 7 4
> 8 3
> 9 5
>=20
> TableC
> ID State
> 1 No
> 2 Yes
> 3 No
> 4 Yes
> 5 No
>=20
> Currently to get the single column list from TableB and TableC that I =
want
> to use to query TableA, I'm using:
>=20
> SELECT `TableB`.`Num` FROM `TableC` RIGHT JOIN `TableB` ON =
`TableC`.`ID` =3D
> `TableB`.`ID` WHERE (`TableC`.`State` LIKE 'Yes') GROUP BY =
`TableB`.`Num`
> ORDER BY `TableB`.`ID`
>=20
> 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:
>=20
> TableBC
> Num
> 2
> 4
> 7
>=20
> And the preferred result once I add in TableA would be:
>=20
> TableABC
> Val
> 1
> 4
> 5
> 6
>=20
>=20
> 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.
>=20
> Thanks in advance!
>=20
> Michael
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dtravis_ard@hotmail.com
>=20
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dstroh@astroh.org=

>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg