MySql query join return result with empty column

Question

hello i have this query i want it to also return rows from table j(jobcard) where j.articleId,j.statusId,j.hcwsId,j.gpId are null any help plz?

SELECT jobcardId,createDateTime,jobNo,companyId,
                         customerId,serialNo,rsvdDate,model,promiseDate,
                         readyDate,deliveryDate,cashMemoNo,dealer,
                         dop,status,warrantyCardno,batchNo,
                         employeeId,hcws,gp,cdId,
                         collectionDate,remarks,article 
                FROM jobcard j, articles a, statuses s, hcws h, gp g
                WHERE j.articleId=a.articleId AND
                      j.statusId = s.statusId AND
                      j.hcwsId = h.hcwsId AND
                      j.gpId=g.gpId"

Do you mean that jobcard has actual NULL values, or do you mean that there are no matches for the row in the other tables? — Barmar
– Barmar, Commented Apr 30, 2013 at 15:48
i mean that i want all rows from jobcard table even if they r null. because initially i dont put ids in jobcard columns(articleid,statusid...)... it only returns rows where these columns are not null — Wasif Khalil
– Wasif Khalil, Commented Apr 30, 2013 at 15:52
to be simple i want records where j.articleId=a.articleId and j.articleId= null from jobcard table — Wasif Khalil
– Wasif Khalil, Commented Apr 30, 2013 at 15:53
try to read about LEFT JOIN. here: Visual Representation of SQL Joins — John Woo
– John Woo, Commented Apr 30, 2013 at 15:55
@RelevantUsername If you don't use a LEFT JOIN, that will perform a cross-product with every row in articles, not likely what he wants. — Barmar
– Barmar, Commented Apr 30, 2013 at 15:58

Ed Gibbs · Accepted Answer · 2013-04-30 15:56:28Z

3

You're not getting NULL values for those columns because your inner joins are excluding them - they don't match up with anything in the other tables.

To include nulls, use OUTER JOIN:

SELECT jobcardId,createDateTime,jobNo,companyId,
  customerId,serialNo,rsvdDate,model,promiseDate,
  readyDate,deliveryDate,cashMemoNo,dealer,
  dop,status,warrantyCardno,batchNo,
  employeeId,hcws,gp,cdId,
  collectionDate,remarks,article 
FROM jobcard j
LEFT OUTER JOIN articles a ON j.articleId=a.articleId
LEFT OUTER JOIN statuses s ON j.statusId = s.statusId
LEFT OUTER JOIN hcws h ON j.hcwsId = h.hcwsId
LEFT OUTER JOIN gp g ON j.gpId=g.gpId

answered Apr 30, 2013 at 15:56

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wasif Khalil Over a year ago

i should hv known this :D thanxxxx (Y)

GolezTrol · Accepted Answer · 2013-04-30 15:56:19Z

Use a LEFT JOIN. The query you have now effectively works as an INNER JOIN, exluding rows where there are no matches. With LEFT JOIN you return all rows from jobcard, and return NULL for columns from other tables if they contain no matching records (i.e. when jobcard contains NULL for that column).

SELECT jobcardId,createDateTime,jobNo,companyId,
                         customerId,serialNo,rsvdDate,model,promiseDate,
                         readyDate,deliveryDate,cashMemoNo,dealer,
                         dop,status,warrantyCardno,batchNo,
                         employeeId,hcws,gp,cdId,
                         collectionDate,remarks,article 
FROM 
  jobcard j
  LEFT JOIN articles a on j.articleId=a.articleId
  LEFT JOIN statuses s on j.statusId = s.statusId
  LEFT JOIN hcws h j.hcwsId = h.hcwsId 
  LEFT JOIN gp g on j.gpId=g.gpId

Collectives™ on Stack Overflow

MySql query join return result with empty column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related