Same Table in LEFT JOIN possible?

[Vector]

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?

[Chris Hope]

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/

[Vector]

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