joining records from 3 tables

Question

i tried to join records from two tables which have the following schema

users
----
name
phone
email

sales
----
seller_id
buyer_id
amt
date

here is my sql

Select
  sales.amt,
  sales.date,
  buyer.phone as buyer_phone,
  buyer.name as buyer_name,
  buyer.email as buyer_email,
  seller.phone As seller_phone,
  seller.name As seller_name,
  seller.email As seller_email
From
  users borrower Inner Join
  sales On borrower.id = sales.buyer_id Inner Join
  users seller On seller.id = sales.seller_id

but i get an empty record

the expected output is suppose to be

amt,
date,
buyer_phone,
buyer_name,
buyer_email,
seller_phone,
seller_name,
seller_email

any help or suggestion

Answer 1

You current query is correct if the two columns of table sales that will be used to join table user are not nullable. However, if they are nullable, use LEFT JOIN instead of INNER JOIN.

SELECT sales.amt,
       sales.date,
       buyer.phone as buyer_phone,
       buyer.name as buyer_name,
       buyer.email as buyer_email,
       seller.phone As seller_phone,
       seller.name As seller_name,
       seller.email As seller_email
FROM   sales
       LEFT JOIN users borrower 
          ON borrower.id = sales.buyer_id 
       LEFT JOIN users seller 
          ON seller.id = sales.seller_id

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins