MySQL between dates selecting Null values

Question

I have an Sp to select some values from a table and there is a left outer join, and the condition of the join is to select with respect to from and to dates.

I need to select data b/w these two dates.

this is how the part looks like

SELECT *// all the needed columns
 FROM  mytable            
     //other joins   
     LEFT OUTER JOIN myview ON              
        //some conditions         
        myview .soldDate between @fromdate and @todate

The selection works fine, it selecting only data b/w those dates, but also selcting null dates.

How do I avoid selecting these null values?

better lose `OUTER` as well or you'll end up with `INNER OUTER JOIN` :-) — But those new buttons though.., Dec 17 '18 at 08:14
btw - in your question you *say* you have an INNER join but your sql uses a LEFT join which is why you get null rows. I'd recommend brushing up on the different join types: https://stackoverflow.com/questions/6294778/mysql-quick-breakdown-of-the-types-of-joins — But those new buttons though.., Dec 17 '18 at 08:16
I am sorry with the confusion, I tried with inner join, then I get the same output — J M, Dec 17 '18 at 10:35

D-Shih · Accepted Answer · 2018-12-17T08:04:57.490

1

You can try to let condition in where instead of on, because you are using outer join

SELECT *// all the needed columns
 FROM  mytable            
     //other joins   
     LEFT OUTER JOIN myview ON              
        //some conditions         
WHERE 
    myview.soldDate between @fromdate and @todate

Or just use INNER JOIN instead of LEFT OUTER JOIN

edited Dec 17 '18 at 08:04

answered Dec 17 '18 at 08:03

D-Shih

44,943
6
31
51

What would be the join condition for the left join? – Salman A Dec 17 '18 at 08:04
From the result set shown, we can't be sure that `soldDate` is `NULL` because of JOIN or by design. – Kordonme Dec 17 '18 at 08:07
I added the condition in where and it worked, but changing into INNER JOIN not worked – J M Dec 17 '18 at 10:36

Kordonme · Answer 2 · 2018-12-17T08:09:51.563

0

Exclude rows in join that does does not have a soldDate

SELECT * -- all the needed columns
FROM mytable
--other joins
LEFT OUTER JOIN myview ON -- conditions
        (myview.soldDate IS NOT NULL AND myview.soldDate between @fromdate and @todate

Your question doesn't explain why soldDate is NULL. It could be because of LEFT JOIN, but also by design.

If it's the first, just use a INNER JOIN. If it's by design, use my query above.

edited Dec 17 '18 at 08:09

answered Dec 17 '18 at 08:04

Kordonme

2,314
3
20
32

If no date is selected in the previous form, soldDate is saved as null in the table. – J M Dec 17 '18 at 10:41

MySQL between dates selecting Null values

2 Answers2