CODEWITHSUNDEEP
sql-serverunionsunion-all
Smiles
Smiles

Reputation: 71

MSSQL Union All two queries with if statement

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.

Upvotes: 1

Views: 398

Answers (3)

Brian Pressler
Brian Pressler

Reputation: 6713

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.

Upvotes: 0

sridhar7
sridhar7

Reputation: 36

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

Upvotes: 1

OwlsSleeping
OwlsSleeping

Reputation: 1570

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.

Upvotes: 1

Related Questions