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
...

Answer 1

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.

Answer 2

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/

Answer 3

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

Answer 4

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..