How to get all data from 2 tables using the foreign key

advertisements

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


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.