CODEWITHSUNDEEP
mysqlsqljoin
john smith
john smith

Reputation: 759

basic join on mysql

I am having a hard time understanding joins on mySQL, and I cannot find any similar example to work with.

Suppose I have two tables: users and users_info.

in users I have id, email and password fields while, in users_info I have all their information, like name, surname, street, etc.

so, if I am getting a user like this: 

SELECT * FROM users WHERE id = 43

and their information like this:

SELECT * FROM users_info WHERE id = 43

I will basically get 2 results, and 2 tables.

I understand now that I need to use join so that they are all together, but I just can't figure out out.

Any help?

Upvotes: 1

Views: 77

Answers (3)

Bgi
Bgi

Reputation: 2494

You can also do:

SELECT users.*, users_info.*
FROM users, users_info
WHERE users.id = users_info.id AND users.id = 43;

This means: "Get me all the columns from the users table, all the columns from the users_info table for the lines where the id column of users and the id column of users_info correspond to each other"

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

It seems like both tables users and user_info are related with each others by the column id therefore you need to join them using this column like this:

SELECT 
  u.id,
  u.email,
  u.password,
  i.name, 
  i.surname, 
  i.street
FROM users           AS u
INNER JOIN user_info AS i ON u.id = i.id;

This will only select the fields id, email, ... etc. However, if you want to select all the columns from both the tables use SELECT *:

SELECT *
FROM users           AS u
INNER JOIN user_info AS i ON u.id = i.id;

If you want to input the id and get all of these data for a specific user, add a WHERE clause at the end of the query:

SELECT *
FROM users           AS u
INNER JOIN user_info AS i ON u.id = i.id
WHERE u.id = 43;

For more information about JOIN kindly see the following:

Upvotes: 2

Bhavik Shah
Bhavik Shah

Reputation: 5183

Here's an example

SELECT * FROM users u
INNER JOIN users_info i
ON u.id=i.id

this means, you are joining users table and users_info table

for example

users

id     name
----   -------
 1      abc
 2      xyz

users_info 

id     email 
---    ------
 1      [email protected]
 2      [email protected]

the query will return 

id    name   email
---   -----  -------
 1     abc    [email protected]
 2     xyz    [email protected]

Here's a nice tutorial

Upvotes: 0

Related Questions