Jump to content


Photo

ConFusInG MYSQL JOINs


  • Please log in to reply
4 replies to this topic

#1 diskhub

diskhub
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 13 December 2005 - 10:48 AM

[img src=\"http://img233.imageshack.us/img233/1198/tbldb3pi.jpg\" border=\"0\" alt=\"IPB Image\" /]

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

#2 diskhub

diskhub
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 14 December 2005 - 04:14 AM

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) View Post[/div][div class=\'quotemain\'][!--quotec--]
[img src=\"http://img233.imageshack.us/img233/1198/tbldb3pi.jpg\" border=\"0\" alt=\"IPB Image\" /]

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
[/quote]


#3 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 14 December 2005 - 05:24 AM

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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#4 diskhub

diskhub
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 14 December 2005 - 09:00 AM

[!--quoteo(post=327294:date=Dec 14 2005, 01:24 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Dec 14 2005, 01:24 PM) View Post[/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--]
[/quote]

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.

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 15 December 2005 - 03:15 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users