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

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!

Answer 1

You can make the following sql query

IF ((SELECT COUNT(*) FROM table1 WHERE project = 1) > 0) 
    SELECT product, price FROM table1 WHERE project = 1
ELSE IF ((SELECT COUNT(*) FROM table1 WHERE project = 2) > 0) 
    SELECT product, price FROM table1 WHERE project = 2
ELSE IF ((SELECT COUNT(*) FROM table1 WHERE project = 3) > 0)
    SELECT product, price FROM table1 WHERE project = 3

Answer 2

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> 

Answer 3

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