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!

Answer 1

You can try making 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