How to get all data from 2 tables using foreign key

Question

This is the result of separating a single table in two:

Table users:
    user_id (pk, ai)
    email
    password
    last_login

Table data:
    user_id (fk to users.user_id)
    data_1
    data_2

To select a single record when there was only one table:

SELECT users.email, users.password, data.data_1, data.data_2
FROM users,data 
WHERE users.email='$user_email' AND users.user_id=data.user_id";

How do I get all records from both tables having the rows connected by users.user_id=data.user_id?

Row1: email, password, data_1, data2
Row2: email, password, data_1, data2
Row3: email, password, data_1, data2
Row4: email, password, data_1, data2
...

`SELECT users.email, users.password, data.data_1, data.data_2 FROM users JOIN data ON users.user_id=data.user_id` — juergen d, Jun 20 '14 at 17:05
This question has been asked a lot before. heres 1 example just from a quick google search. http://stackoverflow.com/questions/9853586/sql-join-multiple-tables — Chase, Jun 20 '14 at 17:06
This only grabs a single record based on an given email. I am looking for way to join the 2 tables and get all rows (but only the email, password, data_1, and data_2 columns). — lr100, Jun 20 '14 at 17:07

score 31 · Accepted Answer · answered Jun 20 '14 at 17:08

Using explicit join syntax could help you. Rewrite your query to:

SELECT 
    users.email, users.password, data.data_1, data.data_2
FROM 
    users
INNER JOIN 
    data 
ON
    users.user_id=data.user_id
WHERE 
    users.email='$user_email'

and get all rows without a WHERE condition:

SELECT 
    users.email, users.password, data.data_1, data.data_2
FROM 
    users
INNER JOIN 
    data 
ON
    users.user_id=data.user_id

It separates the concerns: conditions that join tables from conditions that restricts the result set.

score 5 · Answer 2 · answered Jun 20 '14 at 17:14

have you tried this?

SELECT users.email, users.password, data.data1, data.data2
FROM users,data 
WHERE users.user_id=data.user_id

or this?

SELECT users.email, users.password, data.data1, data.data2
FROM users inner join data on users.user_id=data.user_id

M.Usman · Answer 3 · 2020-07-14T17:43:22.163

3

We can do it as follow...

SELECT users.email, users.password, data.data_1, data.data_2
FROM users,data 
WHERE users.user_id=data.user_id AND users.email='$user_email'

just copy and past above query you may get expected result..

edited Jul 14 '20 at 17:43

answered Aug 19 '16 at 00:21

M.Usman

2,049
22
25

1

Its also a join query. Its called an implicit join where JOIN keyword is not used. – Ankit Pundhir Jun 01 '17 at 07:40

score 2 · Answer 4 · answered Jun 20 '14 at 17:07

To join the userData table to Users try this:

SELECT u.user_id, u.email, u.password, u.last_login
FROM users u
JOIN userData ud ON (u.userID = ud.userID)

This will return all data where the User ID in the Users table matches the User ID in the userData table.

Edit In addition, there are different kinds of joins:

INNER
OUTER
LEFT
RIGHT

For more information on this and their differences check out this handy reference: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

score 0 · Answer 5 · answered Jun 20 '14 at 17:08

0

SELECT users.email, users.password, data.data_1, data.data_2 FROM 
users JOIN data ON  users.user_id=data.user_id
WHERE users.email='$user_email';

answered Jun 20 '14 at 17:08

Tbone

129
1
9

score 0 · Answer 6 · answered Jun 20 '14 at 17:08

you have to use inner join which returns records when there is a common field matches in both tables.. for ex in your case

select * from table1 
  inner join table2 on table1.user_id=table2.user_id

will return all the records in both the table by matching the common fields

How to get all data from 2 tables using foreign key

6 Answers6

Linked