# How to make SELECT in this case?

**URL:** <https://forum.inductiveautomation.com/t/how-to-make-select-in-this-case/53378>\
**Category:** Ignition\
**Created:** [November 23, 2021, 9:46am UTC](https://forum.inductiveautomation.com/t/how-to-make-select-in-this-case/53378 "2021-11-23T09:46:18Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![grzegorz.kaczmarczyk](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/grzegorz.kaczmarczyk/32/24167_2.png) [@grzegorz.kaczmarczyk](https://forum.inductiveautomation.com/u/grzegorz.kaczmarczyk)\
**Post date:** [November 23, 2021, 9:46am UTC](https://forum.inductiveautomation.com/t/how-to-make-select-in-this-case/53378/1 "2021-11-23T09:46:18Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/5/e5b87fa8275c112f3bf6a889ea42c769dc6df6e8.png)

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [November 23, 2021, 10:44am UTC](https://forum.inductiveautomation.com/t/how-to-make-select-in-this-case/53378/2 "2021-11-23T10:44:07Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [November 23, 2021, 11:05am UTC](https://forum.inductiveautomation.com/t/how-to-make-select-in-this-case/53378/3 "2021-11-23T11:05:51Z")

</div>

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 |

```auto

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

```

---

<div class="post-metadata">

**Author:** ![grzegorz.kaczmarczyk](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/grzegorz.kaczmarczyk/32/24167_2.png) [@grzegorz.kaczmarczyk](https://forum.inductiveautomation.com/u/grzegorz.kaczmarczyk)\
**Post date:** [November 23, 2021, 11:23am UTC](https://forum.inductiveautomation.com/t/how-to-make-select-in-this-case/53378/4 "2021-11-23T11:23:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [November 23, 2021, 11:26am UTC](https://forum.inductiveautomation.com/t/how-to-make-select-in-this-case/53378/5 "2021-11-23T11:26:21Z")

</div>

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

> [@grzegorz.kaczmarczyk](#):
>
> Is there any way, to make the fruit number in your results distinct? Because e.g. there are two fruit\_number ‘1’

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 \<-- |

---

<div class="post-metadata">

**Author:** ![grzegorz.kaczmarczyk](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/grzegorz.kaczmarczyk/32/24167_2.png) [@grzegorz.kaczmarczyk](https://forum.inductiveautomation.com/u/grzegorz.kaczmarczyk)\
**Post date:** [November 23, 2021, 11:27am UTC](https://forum.inductiveautomation.com/t/how-to-make-select-in-this-case/53378/6 "2021-11-23T11:27:34Z")

</div>

- 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

---

<div class="post-metadata">

**Author:** ![grzegorz.kaczmarczyk](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/grzegorz.kaczmarczyk/32/24167_2.png) [@grzegorz.kaczmarczyk](https://forum.inductiveautomation.com/u/grzegorz.kaczmarczyk)\
**Post date:** [November 23, 2021, 11:28am UTC](https://forum.inductiveautomation.com/t/how-to-make-select-in-this-case/53378/7 "2021-11-23T11:28:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [November 23, 2021, 11:33am UTC](https://forum.inductiveautomation.com/t/how-to-make-select-in-this-case/53378/8 "2021-11-23T11:33:12Z")

</div>

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

```auto
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 |

---

<div class="post-metadata">

**Author:** ![grzegorz.kaczmarczyk](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/grzegorz.kaczmarczyk/32/24167_2.png) [@grzegorz.kaczmarczyk](https://forum.inductiveautomation.com/u/grzegorz.kaczmarczyk)\
**Post date:** [November 23, 2021, 12:16pm UTC](https://forum.inductiveautomation.com/t/how-to-make-select-in-this-case/53378/9 "2021-11-23T12:16:23Z")

</div>

It works!

Thanks a lot nminchin 🙂

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [November 23, 2021, 9:37pm UTC](https://forum.inductiveautomation.com/t/how-to-make-select-in-this-case/53378/10 "2021-11-23T21:37:15Z")

</div>

Give this a try instead:

```auto
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

```