CODEWITHSUNDEEP
mysqljoin
Wasif Khalil
Wasif Khalil

Reputation: 2247

MySql query join return result with empty column

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"

Upvotes: 1

Views: 4572

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26333

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

Upvotes: 3

GolezTrol
GolezTrol

Reputation: 116100

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

Upvotes: 0

Related Questions