Sami Sami - 22 days ago 7
SQL Question

SQL query to select out of 3 options and from other table, based on column value

I need an SQL query for a somewhat complex SELECT. Assuming the following tables:


Table 1: Table 2:
name | email | callingcardid id | name | email
-------|------------|-------------- ---|--------|----------------
first | e@mail.com | null 1 | second | second@mail.com
second | | 1
third | | null


Output should be:


name | email
-------|-----------
first | e@mail.com
second | second@mail.com
third | -


The query should be something like

SELECT table1.name, COALESCE(NULLIF(table1.email, ''),
CASE WHEN table1.callingcardid != NULL
THEN (SELECT table2.email WHERE id = table1.callingcardid) ELSE '-' END) "email"


The syntax is obviously wrong, since I can't get it to work. There are a couple of other columns with joins from other tables, but otherwise it works. The
CASE
clause is the problem maker.

Database is Microsoft SQL Server 2008 (or 2012).

Answer

Try using COALESCE, something like this;

SELECT
    t1.name
    ,COALESCE(t1.email, t2.email,'-') email
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t1.callingcardid = t2.id