MySQL SELECT with Multiple Tables

Question

I want to select some rows from my database from multiple tables

First table:

 Table name: ctud
 --------------------------------
 id  +   user_id   +  dep_id  
 1          1            1
 2          2            1
 3          3            2
 4          4            3

Second table:

 Table name: cdot
 -------------------------------
 id   +    username   +   name
 1          Hello         Emre
 2          Merhaba       Emma
 3          Aloha         Micheal
 4          Yup           Test

I want to take data from both tables and for this, I am using this code:

 select * 
 FROM ctud,cdot
 where ctud.user_id = cdot.username
 and ctud.user_id = 1;

but I get a blank screen.. What could be the reason for this?

ctud.user_id is the user's id, a number, cdot.username is a string, the user's name. You cannot join based on this. You need to use another table that would have both user_id and username and use that table also in your SQL. — Nivas
– Nivas, Commented Jul 21, 2014 at 18:01

juergen d · Accepted Answer · 2014-07-21 18:02:18Z

1

You join your tables on this condition

ctud.user_id = cdot.username

but ctud.user_id is for instance 1 and cdot.username Hello.

That does not match and won't return results. You need 2 columns in those tables that contain the same values to make a connection of these tables.

You probably wanted to do

 select * 
 FROM ctud
 join cdot on ctud.user_id = cdot.id
 where ctud.user_id = 1;

answered Jul 21, 2014 at 18:02

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jaime Rocha · Accepted Answer · 2014-07-21 18:05:19Z

0

You only need this code

 select * 
 FROM ctud,cdot
 where ctud.user_id = cdot.id and ctud.user_id = 1;

answered Jul 21, 2014 at 18:05

Jaime Rocha

971 silver badge7 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT with Multiple Tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related