How to create a single MSSQL SELECT query in this case:

1. We've got 2 tables: fruits and expiration
2. The goal is to receive table where specific fruit number has information about having NULL in expirationDate column. Those fruit numbers that don't have NULL would have zeros in that column. Number 4 doesn't exist in expiration table, so it would also have 0 in results, because it doesn't have NULL.

Tables

CodePudding user response：

You can't easily get the expiration date on the format you want. However, it does not really matter(?). I assume you want 1 or 0 in your new table because you want to use an if statement to check if the fruit is bad or not. You can solve this easily:

if(expirationDate == null){/*something*/} 

or, you might even be able to do

if(expirationDate) //this is all fruit that is not bad
else{/*your code to deal with expirated fruit here*/}

Note: I don't know what programming languages you are using. But in most of them: null and 0 are FALSE.

if(null) // false
if(0) //false
if(undefined) //false, in javascript
//everything that is not a false value, is true.
if("oiaehgtaoiwgneawg") //true
if(-1) //true

To answer your SQL query question:

You already have everything you need in the expiration table

SELECT fruit_number, expiration_date
FROM expiration;

I hope this helps