CODEWITHSUNDEEP
mysqlsql
lr100
lr100

Reputation: 600

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

Upvotes: 12

Views: 113326

Answers (6)

M.Usman
M.Usman

Reputation: 2119

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

Upvotes: 3

flo_badea
flo_badea

Reputation: 784

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

Upvotes: 5

vivek
vivek

Reputation: 329

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

Upvotes: 0

Tbone
Tbone

Reputation: 129

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';

Upvotes: 0

VMai
VMai

Reputation: 10336

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.

Upvotes: 33

Caleb Palmquist
Caleb Palmquist

Reputation: 458

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/

Upvotes: 2

Related Questions