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 :-) — You Old Fool
– You Old Fool, Commented Dec 17, 2018 at 8: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: stackoverflow.com/questions/6294778/… — You Old Fool
– You Old Fool, Commented Dec 17, 2018 at 8:16
I am sorry with the confusion, I tried with inner join, then I get the same output — J M
– J M, Commented Dec 17, 2018 at 10:35

D-Shih · Accepted Answer · 2018-12-17 08:04:57Z

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, 2018 at 8:04

answered Dec 17, 2018 at 8:03

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

3 Comments

Salman Arshad Over a year ago

What would be the join condition for the left join?

Kordonme Over a year ago

From the result set shown, we can't be sure that soldDate is NULL because of JOIN or by design.

J M Over a year ago

I added the condition in where and it worked, but changing into INNER JOIN not worked

Kordonme · Accepted Answer · 2018-12-17 08:09:51Z

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, 2018 at 8:09

answered Dec 17, 2018 at 8:04

Kordonme

2,2843 gold badges20 silver badges32 bronze badges

1 Comment

J M Over a year ago

If no date is selected in the previous form, soldDate is saved as null in the table.

Collectives™ on Stack Overflow

MySQL between dates selecting Null values

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related