469,626 Members | 1,025 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

Same Table in LEFT JOIN possible?

I have this working fine in Access and MSSQL. While trying to convert
to MySQL the result is not what I'm looking for to get..

Two tables:

T1
----------------
ID1|cName |
----------------
10 |Electronics|
----------------
20 |Computers |
---------------

T2
-------------------
ID2 |cName |ID1|
-------------------
11 |CD |10 |
-------------------
12 |DVD |10 |
-------------------
13 |VCR |10 |
-------------------
14 |TV |10 |
-------------------
21 |HP |20 |
-------------------
22 |COMPAQ |20 |
-------------------
23 |IBM |20 |
-------------------
24 |DELL |20 |
------------------
221 |Desktop |22 |
------------------
222 |Server |22 |
------------------
111 |Sony |11 |
------------------
112 |JVC |11 |
------------------
The Query is:

strsql = "SELECT TB1.cName, TB2.cName TB3.cName "
strsql = strsql & "FROM T1 AS TB1 "
strsql = strsql & "LEFT JOIN (T2 AS TB2 LEFT JOIN T2 AS TB3 ON TB3.ID1
= TB2.ID2) "
strsql = strsql & "ON TB2.ID1 = TB1.ID1"
strsql = strsql & "ORDER BY TB1.cName, TB2.cName, TB3.cName;"
The Resulted format from Access/MSSQL (well after filtering):

Lev 1 * - Electronics

Lev 2 ** - CD
Lev 3 *** - SONY
Lev 3 *** - JVC
Lev 2 ** - DVD
Lev 2 ** - VCR
Lev 2 ** - TV

Lev 1 * - Computers

Lev 2 ** - HP
Lev 2 ** - Compaq
Lev 3 *** - Desktop
Lev 3 *** - Server
Lev 2 ** - IBM
Lev 2 ** - DELL

On MySQL I have ALL content from T2 listed under each entry from T1.
Not sure if LEFT JOIN of the same table (T2 AS TB2 LEFT JOIN T2 AS
TB3) is legal, or it's a problem in nesting... Can someone help?
Jul 20 '05 #1
2 6629
Vector wrote:
I have this working fine in Access and MSSQL. While trying to convert
to MySQL the result is not what I'm looking for to get..

Two tables:
[snip]
The Query is:

strsql = "SELECT TB1.cName, TB2.cName TB3.cName "
strsql = strsql & "FROM T1 AS TB1 "
strsql = strsql & "LEFT JOIN (T2 AS TB2 LEFT JOIN T2 AS TB3 ON TB3.ID1
= TB2.ID2) "
strsql = strsql & "ON TB2.ID1 = TB1.ID1"
strsql = strsql & "ORDER BY TB1.cName, TB2.cName, TB3.cName;"


The Resulted format from Access/MSSQL (well after filtering):
[snip]
On MySQL I have ALL content from T2 listed under each entry from T1.
Not sure if LEFT JOIN of the same table (T2 AS TB2 LEFT JOIN T2 AS
TB3) is legal, or it's a problem in nesting... Can someone help?


Try it like this instead (untested)

SELECT TB1.cName, TB2.cName**TB3.cName
FROM T1 AS TB1
LEFT JOIN T2 AS TB2 ON TB2.ID1 = TB1.ID1
LEFT JOIN T2 AS TB3 ON TB3.ID1 = TB2.ID2
ORDER BY TB1.cName, TB2.cName, TB3.cName

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 20 '05 #2
Thank you Chris! Worked like a charm...
Chris Hope <bl*******@electrictoolbox.com> wrote in message news:<11*************@216.128.74.129>...

Try it like this instead (untested)

SELECT TB1.cName, TB2.cName TB3.cName
FROM T1 AS TB1
LEFT JOIN T2 AS TB2 ON TB2.ID1 = TB1.ID1
LEFT JOIN T2 AS TB3 ON TB3.ID1 = TB2.ID2
ORDER BY TB1.cName, TB2.cName, TB3.cName

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Charles Haven | last post: by
1 post views Thread by dan | last post: by
6 posts views Thread by pb648174 | last post: by
9 posts views Thread by shanevanle | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.