CODEWITHSUNDEEP
mysqlsqldatabase
Vin
Vin

Reputation: 169

How to display all the data in MySQL with WHERE clause

I want to display all the values in my column even the null value.

Below is my code that displays all the columns data included null but it doesn't display the latest date.

 SELECT 
  MEMB.LAST_M,
  MEMB.MEMB_N,
  PrintDate
FROM
  MEMB 
  INNER JOIN tblPrint 
    ON MEMB.MEMB_N = tblPrint.MEMB_N

img1

This code however displays the latest dates but doesn't display all the columns even the null values.

    SELECT 
  MEMB.LAST_M,
  MEMB.MEMB_N,
  MAX(PrintDate)
FROM
  MEMB 
  INNER JOIN tblPrint 
    ON MEMB.MEMB_N = tblPrint.MEMB_N 
 WHERE tblPrint.`PrintDate` IN (SELECT tblPrint.`PrintDate` FROM tblPrint) GROUP BY MEMB.`LAST_M`

enter image description here

Upvotes: 0

Views: 412

Answers (3)

Buchiman
Buchiman

Reputation: 320

The problem to your query is your WHERE clause you could try removing it and you will get the NULL values.

SELECT 
  MEMB.LAST_M,
  MEMB.MEMB_N,
  MAX(PrintDate)
FROM
  MEMB 
  INNER JOIN tblPrint 
    ON MEMB.MEMB_N = tblPrint.MEMB_N 
GROUP BY MEMB.LAST_M, MEMB.MEMB_N

Upvotes: 0

Parfait
Parfait

Reputation: 107737

With MySQL 8, window functions were implemented, so you can now include inline aggregates within a unit level query. Specifically you can add a new column to original query for Latest_Date:

SELECT 
  m.LAST_M,
  m.MEMB_N,
  p.PrintDate,
  MAX(PrintDate) OVER(PARTITION BY m.MEMB_N) AS Latest_Date
FROM
  MEMB m
INNER JOIN tblPrint p
  ON m.MEMB_N = p.MEMB_N

Upvotes: 0

Zizzyzizzy
Zizzyzizzy

Reputation: 319

You likely need to use LEFT JOIN instead of INNER JOIN. Without seeing the DB structure, I have no way to test to be sure, but I've run into the same issue before. LEFT JOIN fixed it for me.

Upvotes: 1

Related Questions