I created a fiddle for this, at this link: http://www.sqlfiddle.com/#!2/7e007
I could'nt find SQL compact / CE so it's in MySQL.
The tables looks like this
Records Clients
ID | NAME | AGE ID | NAME
------------------ ----------------
1 | John | 20 1 | John
2 | Steven | 30 2 | Daniel
3 | Abraham | 30 3 |
4 | Donald | 25 5 | Lisa
6 | | 35 6 | Michael
7 | | 42 7 |
I would like to select from both tables, and if the id is in both tables and both have names I would like the the name from "Clients" as the default. If the name in Records is blank, use the client name (if any) and if the Clients.Name is blank; use the records.Name.
From tables above, i would like this:
ID | NAME | AGE
------------------
1 | John | 20
2 | Daniel | 30
3 | Abraham | 30
4 | Donald | 25
5 | Lisa |
6 | Michael | 35
7 | | 42
How do i do this in SQL Compact?
EDIT: Thanks to great answers below i've managed to come up with this query, which ALMOST works:
SELECT t.id, t.name, t.age FROM
(
(
SELECT r.id,
CASE WHEN r.name = NULL THEN c.name ELSE r.name END as name,
r.age
FROM Records r
LEFT JOIN Clients c ON c.id = r.id
)
UNION
(
SELECT c.id, c.name, null as age FROM Clients c where c.id NOT IN (select id from Records)
)
) as t ORDER BY t.id
This gives me this output:
ID | NAME | AGE
------------------
1 | John | 20
2 | Daniel | 30
3 | Abraham | 30
4 | Donald | 25
5 | Lisa |
6 | | 35
7 | | 42
"Michael" (should be on #6) is missing in this case. Why?!
select r.id,
IF(c.name != '',c.name,r.name) as name,
r.age
FROM Records r
LEFT JOIN Clients c ON c.id = r.id
GROUP BY c.id
Use above query.
EDITED:
SELECT t.id, t.name, t.age FROM
(
(
SELECT r.id,
CASE WHEN c.name <> '' THEN c.name ELSE r.name END as name,
r.age
FROM Records r
LEFT JOIN Clients c ON c.id = r.id
)
UNION
(
SELECT c.id, c.name, null as age FROM Clients c where c.id NOT IN (select id from Records)
)
) as t ORDER BY t.id
Use this query.