How to make SELECT in this case?

Ignition
1

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.

SQL
SQL776×668 8.75 KB

2

I’m a bit confused.

  • expirationDate in your resultset, is that a boolean to say if the fruit has an expiration data associated with it? If it has an expiry date defined or the fruit it missing from the table, then return 0, otherwise return 1 if the expiration date is NULL
  • how do you choose which batch of fruit to return into the resultset if there are multiple batches of the same fruit, potentially with a mix of both having and not having expiry dates?
  • why does your row for fruit 3 and 5 have a 0? (the questions before this might answer that)
3

This will get you almost there, but includes batch_number as well. It’s a little trickier to group by fruits, but I also need to know your criteria for grouping regarding how to choose which batch_number to return.

batch_number fruit_number expirationDate
NULL 4 0
1 1 1
2 1 0
1 2 0
2 2 1
1 3 0
2 3 0
1 5 0 

SELECT
	e.batch_number,
	f.fruit_number,
	CASE
		WHEN e.expiration_date is NULL AND e.fruit_number IS NOT NULL THEN 1
		ELSE 0
		END AS expirationDate
FROM
    expiration as e
    FULL OUTER JOIN fruits as f ON f.fruit_number = e.fruit_number
WHERE
	f.fruit_number IS NOT NULL
ORDER BY
	e.fruit_number
1 Like
4

Hi!

Wow, this seems very close to what I want to achieve. Is there any way, to make the fruit number in your results distinct? Because e.g. there are two fruit_number ‘1’. Also we want to have fruit_number ‘4’ in the results, which will have ‘0’ in expirationDate, which means there is no NULL for that fruit number.

5

Oops, it was supposed to include fruit 4, my bad. I edited my post.

Yes, and these rows have a 0 and a 1 respectively, so how do you choose which one to pick?

batch_number fruit_number expirationDate
1 1 1 <--
2 1 0 <--
6

  • Yes, boolean to say that there is a NULL for the fruit number. If there is no fruit number (e.g. 4), it means it doesn’t have NULL.

  • We want to show all fruit numbers and column with ‘0’ and ‘1’, which will show which fruits have NULL in expiration_date

  • 3 and 5 have 0, because they don’t have NULL in expiration_date

7

We want to choose the one with expirationDate equal to ‘1’

1 Like
8

This will produce it, but it’s ugly with the nested SELECT… I might get back to you with something cleaner.

SELECT
	fruit_number, 
	MAX(expirationDate) as expirationDate
FROM
	(SELECT
		f.fruit_number,
		CASE
			WHEN e.expiration_date is NULL AND e.fruit_number IS NOT NULL THEN 1
			ELSE 0
			END AS expirationDate
	FROM
		expiration as e
		FULL OUTER JOIN fruits as f ON f.fruit_number = e.fruit_number
	WHERE
		f.fruit_number IS NOT NULL
	) t
GROUP BY
	fruit_number
ORDER BY
	fruit_number

Result:

fruit_number expirationDate
1 1
2 1
3 0
4 0
5 0
2 Likes
9

It works!

Thanks a lot nminchin :slight_smile:

1 Like
10

Give this a try instead:

SELECT  f.fruit_number, 
        f.fruit_name,
        expirationDate = MAX(CASE WHEN e.expiration_date IS NULL AND e.fruit_number IS NOT NULL THEN 1 ELSE 0 END)
FROM    dbo.fruits AS f
        LEFT JOIN dbo.expiration AS e
            ON e.fruit_number = f.fruit_number
GROUP BY f.fruit_number, f.fruit_name
ORDER BY f.fruit_number