MSSQL Union All two queries with if statement

Question

I have a query the following works as expected

 If((Select count(*) from table1 where product = 'carrot')< 5)
Begin
Select Top (5 - (Select count(*) from table1 where product = 'carrot'))
id, product From table2
WHere id NOT IN
(Select id from table1) AND product = 'carrot'
Order by newid()
END

What i want to do is Union or Union all say another product potatoes

If((Select count(*) from table1 where product = 'potato')< 5)
Begin
Select Top (5 - (Select count(*) from table1 where product = 'potato'))
id, product From table2
WHere id NOT IN
(Select id from table1) AND product = 'potato'
Order by newid()
END

I keep getting a syntax error, when i add UNION between IF or after END. Is this possible or another way is better....

What i am doing is trying to select a random sample of carrots, first i want to check if i have the 5 carrots in table1. if i do don't run sample. If i do not have 5 total carrots run the sampler and return 5 carrots. I then filter out if they already exist in table 1 by the id. Then it subtracts the count from the new sample for a total of five.

It works well, now i want to run for other products eg lettuce, potatoes etc... But i want an UNION or UNION All. hope makes sense.

This is not valid syntax: `Select Top (5 - (Select count(*) from #Table1 where product = 'carrots')< 5)`. I assume you don't want that <5 in there? — Brian Pressler, Aug 23 '19 at 21:12
Also if you select IDs from table2 where the ID is not in any of the ID's in table2... it will never return any records. What are you trying to accomplish? — Brian Pressler, Aug 23 '19 at 21:21

score 1 · Answer 1 · answered Aug 23 '19 at 21:10

I'd be interested to see whether this way works-

Select Top (5 - (Select count(*) from table1 where product = 'carrots')< 5)
id
, product 
From table2
WHere id NOT IN (Select id from table2)
    AND (Select count(*) from table1 where product = 'carrots')< 5)
UNION ALL
Select Top (5 - (Select count(*) from table1 where product = 'potatoes')< 5)
id
, product 
From table2
WHere id NOT IN (Select id from table2) 
    AND (Select count(*) from table1 where product = 'potatoes')< 5)

Your style is interesting, feels procedural rather than set-based.

If a product has more than 5 rows in table1 it will throw an error: `A TOP N or FETCH rowcount value may not be negative.` — Brian Pressler, Sep 03 '19 at 22:53

score 1 · Answer 2 · edited Aug 24 '19 at 04:57

1

You can try it this way

If(((Select count(*) from table1 where product = 'carrot'< 5) and (Select count(*) from table1 where product ='potato' <5))
) 
Begin

Select Top (5 - (Select count(*) from table1 where product = 'carrot')) id, product 
From table2 
WHere id NOT IN (Select id from table1) AND product = 'carrot' Order by newid()

Union all


Select Top (5 - (Select count(*) from table1 where product = 'potato')) id, product From table2 
WHere id NOT IN (Select id from table1) AND product = 'potato' Order by newid()

END

edited Aug 24 '19 at 04:57

Prashant Pimpale

10,349
9
44
84

answered Aug 24 '19 at 03:02

sridhar7

26
5

This will only return rows if there are less than 5 records for every product. If one product has more than 5 and another has less... the select statements will not run at all. – Brian Pressler Sep 03 '19 at 22:56

score 0 · Answer 3 · answered Aug 26 '19 at 16:33

IF statements in SQL do not behave as sub-queries or row-sets in SQL, as you've found out. They are for branching the flow of control only.

Here is a more set based approach you could take:

SELECT ProdSamples.*
FROM
    (
    SELECT Table2.*, ROW_NUMBER() OVER (PARTITION BY table2.Product ORDER BY NEWID()) RowNum
    FROM Table2
    LEFT JOIN Table1
    ON Table1.id = Table2.id
    WHERE Table1.id IS NULL
    ) ProdSamples
JOIN
    (
    SELECT Product, COUNT(*) ProdCount
    FROM Table1
    GROUP BY Product
    ) ProdCounts
ON ProdSamples.Product = ProdCounts.Product
    AND ProdSamples.RowNum <= (5 - ProdCounts.ProdCount)

The first sub-query ProdSamples returns all the products from Table2 that do not have an id in Table1. The RowNum field ranks them in random order partitioned by Product.

The second sub-query ProdCounts is the count of records for each product in Table1. Then it joins these sub-queries together and only returns the records from ProdSamples where the RowNum is lower or equal to the number of samples you want to return.

MSSQL Union All two queries with if statement

3 Answers3