CODEWITHSUNDEEP
phpmysqlsql
Shahab
Shahab

Reputation: 694

mysql tables have two foreign keys from another same table

I have two table.

  1. invoice
  2. user

In user table I have 

user_id
user_name
user_email    
user_address

In invoice table I have

invoice_id
invoice_to_user_id
invoice_by_user_id
invoice_date

In invoice table I have two foreign key from user table.
1. invoice_to_user_id
2. invoice_by_user_id

now I want to make single query that get the user info of both foreign keys.e.g,
1. invoice_to_user_name
2. invoice_to_user_email
3. invoice_to_user_address
4. invoice_by_user_name
5. invoice_by_user_email
6. invoice_by_user_address

Thanks In advance.

Upvotes: 0

Views: 80

Answers (2)

Adam Pollock
Adam Pollock

Reputation: 111

Join the invoice table with the user table twice, once on invoice_to_user_id and then again on invoice_by_user_id, e.g.

SELECT
INV.invoice_id,
US1.user_name AS invoice_to_user_name,
US1.user_email AS invoice_to_user_email,
US1.user_address AS invoice_to_user_address,
US2.user_name AS invoice_by_user_name,
US2.user_email AS invoice_by_user_email,
US2.user_address AS invoice_by_user_address
FROM
[invoice] INV
INNER JOIN [user] US1 ON INV.invoice_to_user_id = US1.user_id
INNER JOIN [user] US2 ON INV.invoice_by_user_id = US2.user_id

Upvotes: 0

Merlin Denker
Merlin Denker

Reputation: 1418

SELECT t.user_name    AS invoice_to_user_name,
       t.user_email   AS invoice_to_user_email,
       t.user_address AS invoice_to_user_address,
       b.user_name    AS invoice_by_user_name,
       b.user_email   AS invoice_by_user_email,
       b.user_address AS invoice_by_user_address
FROM invoice i
LEFT JOIN user t ON t.user_id = i.invoice_to_user_id
LEFT JOIN user b ON b.user_id = i.invoice_by_user_id

Upvotes: 1

Related Questions