Use IF in a mySQL query

Question

Hi, I'm trying to make a SELECT where I need to select a specified table by a column wich define the type of subject:

table_buildings
id      name                location      source_type      source_id
1       NY Appartament      New York      0                NULL
2       Guggenheim  Museum  Manhattan     1                27
3       MoMA                New York      2                3

table_url // source type == 1
id      name               url
27      Guggenheim  Site   http://www.guggenheim.org/

table_books // source type == 2
id      name               pages           sample         author
3       World Museums      125-127         path/img.jpg   John Smith

I don't know if it's possible, but I have some problem to solve it without an IF statement inside my query because I can find the right table by the source_type column of table_buildings

does exist soma tecnique to use an IF inside a SELECT query?

Answer 1

I am afraid the only possible way is select from all tables unconditionally. Not a much overhead though.
Just left join as much tables as you need. ones with no corresponding information will just return nulls.

Answer 2

You could UNION three different SELECTs that JOIN to the three tables with WHERE clauses.

Alternatively, you could join against all three tables in one query and use a CASE operator to select a column from the appropiate joined table based on source_type.

Answer 3

Hi, you can use "CASE WHEN" in the SELECT.

SELECT
 table_buildings.id,
 table_buildings.name,
 table_buildings.location,
 CASE WHEN table_buildings.source_type = 0
  THEN ""
  ELSE CASE WHEN table_buildings.source_type = 1
   THEN table_url.name
   ELSE table_books.name
   END CASE
  END CASE AS source_name
FROM table_buildings
LEFT JOIN table_url
ON table_buildings.source_id = table_url.id
LEFT JOIN table_books
ON table_buildings.source_id = table_books.id

Answer 4

I'd just join both tables and use COALESCE for duplicate column names in the joined tables.

SELECT COALESCE(table_url.name, table_books.name) as name, url, pages, sample author FROM table_buildings
LEFT OUTER JOIN table_url ON table_buildings.source_type = 1 AND table_buildings.source_id = table_url.id
LEFT OUTER JOIN table_books ON table_buildings.source_type = 2 AND table_buildings.source_id = table_books .id