lr100 lr100 - 3 months ago 8
MySQL Question

How to get all data from 2 tables using foreign key

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

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.