ConFusInG MYSQL JOINs

[diskhub]

 

Above there are 5 tables.

 

I have tried many joins but it didn't work.

 

 

SELECT a.***, a.**, b1.b1_name FROM tablea a, tableb b, tableb1 b1, table c WHERE (a.b_id = b.b_id AND b.b1_id = b1.b1_id AND a.c_id= c.c_id) ORDER BY a.a_id ASC

 

 

a.*** and a.** are two other colums of TABLE A.

 

 

However, it doesn't seem to work. Cos no row is produced. (0 row)

But i have double check and all tables have entry well...

 

please help

[diskhub]

no one can help in this?

 

 

[!--quoteo(post=326990:date=Dec 13 2005, 06:48 PM:name=diskhub)--][div class=\'quotetop\']QUOTE(diskhub @ Dec 13 2005, 06:48 PM) 326990[/snapback][/div][div class=\'quotemain\'][!--quotec--]

 

Above there are 5 tables.

 

I have tried many joins but it didn't work.

SELECT a.***, a.**, b1.b1_name FROM tablea a, tableb b, tableb1 b1, table c WHERE (a.b_id = b.b_id AND b.b1_id = b1.b1_id AND a.c_id= c.c_id) ORDER BY a.a_id ASC

a.*** and a.** are two other colums of TABLE A.

However, it doesn't seem to work. Cos no row is produced. (0 row)

But i have double check and all tables have entry well...

 

please help

 

[ryanlwh]

I'm suprised that it didn't generate an error. The "table c" part should be "tablec c" if I'm correct. Change that and see what happens.

 

Also try this:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] a.***, a.**, b1.b1_name FROM tablea a LEFT JOIN tableb b ON a.b_id = b.b_id LEFT JOIN tableb1 b1 ON b.b1_id = b1.b1_id LEFT JOIN tablec c ON a.c_id= c.c_id ORDER BY a.a_id ASC [!--sql2--][/div][!--sql3--]

[diskhub]

[!--quoteo(post=327294:date=Dec 14 2005, 01:24 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Dec 14 2005, 01:24 PM) 327294[/snapback][/div][div class=\'quotemain\'][!--quotec--]

I'm suprised that it didn't generate an error. The "table c" part should be "tablec c" if I'm correct. Change that and see what happens.

 

Also try this:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] a.***, a.**, b1.b1_name FROM tablea a LEFT JOIN tableb b ON a.b_id = b.b_id LEFT JOIN tableb1 b1 ON b.b1_id = b1.b1_id LEFT JOIN tablec c ON a.c_id= c.c_id ORDER BY a.a_id ASC [!--sql2--][/div][!--sql3--]

 

yes that's my typo. It is tablec c.

Nope, it produces no row.

 

Try it if u think it works... it fails badly on me. please help.

[shoz]

I'll take a look at it if you post a small dump of the tables.

]$ mysqldump dbname table1 table2 table3 --opt  > file.sql

create identical tables renamed with made up data.

 

Remember to post only a small dump.