I need to do select data from a table based on some kind of priority like so:
select product, price from table1 where project = 1
-- pseudo: if no price found, do this:
select product, price from table1 where customer = 2
-- pseudo: if still no price found, do this:
select product, price from table1 where company = 3
That is, if I found 3 products with prices based on project = X
, I don't want to select on customer = Y
. I just want to return the resulting 3 rows and be done.
How are you supposed to do stuff like this in SQL? Use some kind of CASE-statement for the pseudo-if's? Do a union or some other smart thing?
Edit: I'm using MS SQL.
Thanks!
This question is related to
sql
sql-server
--Similar answer as above for the most part. Code included to test
DROP TABLE table1
GO
CREATE TABLE table1 (project int, customer int, company int, product int, price money)
GO
INSERT INTO table1 VALUES (1,0,50, 100, 40),(1,0,20, 200, 55),(1,10,30,300, 75),(2,10,30,300, 75)
GO
SELECT TOP 1 WITH TIES product
, price
, CASE WhereFound WHEN 1 THEN 'Project'
WHEN 2 THEN 'Customer'
WHEN 3 THEN 'Company'
ELSE 'No Match'
END AS Source
FROM
(
SELECT product, price, 1 as WhereFound FROM table1 where project = 11
UNION ALL
SELECT product, price, 2 FROM table1 where customer = 0
UNION ALL
SELECT product, price, 3 FROM table1 where company = 30
) AS tbl
ORDER BY WhereFound ASC
With SQL server you can just use a CTE instead of IF/THEN logic to make it easy to map from your existing queries and change the number of involved queries;
WITH cte AS (
SELECT product,price,1 a FROM table1 WHERE project=1 UNION ALL
SELECT product,price,2 a FROM table1 WHERE customer=2 UNION ALL
SELECT product,price,3 a FROM table1 WHERE company=3
)
SELECT TOP 1 WITH TIES product,price FROM cte ORDER BY a;
Alternately, you can combine it all into one SELECT
to simplify it for the optimizer;
SELECT TOP 1 WITH TIES product,price FROM table1
WHERE project=1 OR customer=2 OR company=3
ORDER BY CASE WHEN project=1 THEN 1
WHEN customer=2 THEN 2
WHEN company=3 THEN 3 END;
The CASE statement is the closest to an IF statement in SQL, and is supported on all versions of SQL Server:
SELECT CASE <variable>
WHEN <value> THEN <returnvalue>
WHEN <othervalue> THEN <returnthis>
ELSE <returndefaultcase>
END
FROM <table>
there is a case statement, but i think the below is more accurate/efficient/easier to read for what you want.
select
product
,coalesce(t4.price,t2.price, t3.price) as price
from table1 t1
left join table1 t2 on t1.product = t2.product and t2.customer =2
left join table1 t3 on t1.product = t3.product and t3.company =3
left join table1 t4 on t1.product = t4.product and t4.project =1
Instead of using EXISTS
and COUNT
just use @@ROWCOUNT
:
select product, price from table1 where project = 1
IF @@ROWCOUNT = 0
BEGIN
select product, price from table1 where customer = 2
IF @@ROWCOUNT = 0
select product, price from table1 where company = 3
END
Please check whether this helps:
select TOP 1
product,
price
from
table1
where
(project=1 OR Customer=2 OR company=3) AND
price IS NOT NULL
ORDER BY company
Source: Stackoverflow.com