SQL to show even null record

Question

Below is my sql statement

SELECT a.purchase_id,
       b.username,
       a.purchase_packageid,
       a.purchase_tradelimit,
       a.purchase_pincode,
       a.purchase_datetime,
       c.packages_name ,
  FROM purchase a,
       accounts b,
       packages c 
 WHERE a.purchase_userid=b.UserId 
   AND c.packages_id=a.purchase_packageid

Basically the issue is I got 3 tables

Accounts
Purchase
Packages

The main table is Purchase, inside the table there is purchase_userid , which I need to use it to get username from table accounts

So the problem now is I got rows where the purchase_userid is blank, because its blank, it won't draw its record as null.

The only record that show in this sql statement is only those with purchase_userid,

As the value for purchase_userid will be fill up later for my web app, I still want to select rows without purchase_userid and those with purchase_userid

Thanks for helping !!

score 2 · Answer 1 · edited May 23 '17 at 10:26

You need to use a left join to load all records in Purchase even when no matching records are found in Accounts.

SELECT a.purchase_id, b.username, a.purchase_packageid, a.purchase_tradelimit,   a.purchase_pincode, a.purchase_datetime, c.packages_name 
FROM purchase a LEFT JOIN accounts b 
ON a.purchase_userid=b.UserId 
JOIN packages c
ON c.packages_id=a.purchase_packageid

This post explains the different kinds of joins pretty well: What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

score 1 · Accepted Answer · answered Sep 10 '14 at 23:46

You're using the old-timey syntax for INNER JOINs between your tables, when what you need is LEFT JOIN operations. Try this:

   SELECT a.purchase_id,
          b.username,
          a.purchase_packageid,
          a.purchase_tradelimit,
          a.purchase_pincode,
          a.purchase_datetime,
          c.packages_name
     FROM purchase AS a
LEFT JOIN accounts AS b  ON a.purchase_userid    = b.UserId
LEFT JOIN packages AS c  ON a.purchase_packageid = c.packages_id

This works better for you because the kind of JOIN you were using suppresses records from your a table when they weren't matched in your b or c table. This LEFT JOIN will leave the a table records in place, and put NULL values where you are calling for data from the other two.

SQL to show even null record

2 Answers2