Conditional SQL?

Grofit

Im just putting together some queries however im sure i read somewhere that SQL supported some sort of conditional commands...

Basically what im trying to do is bring back a query that will always have:

ID
Association Type
Association Value

Now the association type relates to a table, so there could be 3 different tables, and association value will be a different column in each different table...

So is there a way in SQL to tell it what to select based on criteria... ie...

SELECT base_table.id, (if association_type == 1)assoc_table1.value1 AS association_value(else)assoc_table2.value2 AS association_value(/if)
WHERE base_table.id < 100

Thats psudo codey like stuff not a real SQL statement im just trying to show what i want to do, although i would ideally rather a switch() style statement rather than ifs, but im not fussy if it doesnt support it...

Its just if i cant select different tables based off certain criteria i would have to do a 2nd query for each row returned 🙁

tomhath

If I understand what you are trying to do, either an IF or a CASE function should work:

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Grofit

Brilliant!

thats exactly what i wanted, although im not sure if im using it right.. here is an example of what i thought i would need to do:

SELECT employer.*, 
CASE employer.job_type_id
WHEN 1 THEN managers.name AS job_name 
WHEN 2 THEN admins.name AS job_name 
END
FROM employer
CASE employer.job_type_id
WHEN 1 THEN JOIN managers ON managers.id = employer.job_id 
WHEN 2 THEN JOIN admins ON admins.id = employer.job_id  

END
WHERE employer.id = 10

It keeps telling my that my SQL is wrong, im using the latest SQL server version to my knowledge... i will need to be able to join and select certain tables based on the base tables critera... as shown above...

Any advice would be great!

Grofit

after reading up a bit more ive found out that i should but the "as X" outside the case, however i cant find any successfull examples of how to pick a table to join on based on a case statement... anyone know if it is even possible?