Can you have if-then-else logic in SQL

Question

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

User · Answer

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;

An SQLfiddle to test with.

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;

Another SQLfiddle.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The CASE statement is the closest to an IF statement in SQL  and is supported on all versions of SQL Server   SELECT CASE  lt variable gt              WHEN  lt value gt       THEN  lt returnvalue gt              WHEN  lt othervalue gt  THEN  lt returnthis gt              ELSE  lt returndefaultcase gt          END    FROM  lt table gt

User · Answer

You can make the following sql query  IF   SELECT COUNT    FROM table1 WHERE project   1   gt  0       SELECT product  price FROM table1 WHERE project   1 ELSE IF   SELECT COUNT    FROM table1 WHERE project   2   gt  0       SELECT product  price FROM table1 WHERE project   2 ELSE IF   SELECT COUNT    FROM table1 WHERE project   3   gt  0      SELECT product  price FROM table1 WHERE project   3

User · Answer

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

[sql] Can you have if-then-else logic in SQL?

Examples related to sql

Examples related to sql-server