Output rows in one table for which no match exists in another

Question

I have two tables: scans and personel. In scans there is a column createdby where the username of the person who created a scan is there. I need to create a view where to see these scans but instead of the username, I want to see the full name of that person which I have in the table personel. This query mostly works:

select personel.firstname ||' '|| personel.lastname as CREATEDBY
from scans inner join personel on scans.createdby = personel.username;

The problem is that there are some user names in scans.createdby which aren't anymore in the personel table and their scans are not retrieved by the above query since the values in the createdby column does not match with any value in the username column.

So for that case, I would like to output the value from createdby for those scans.

So if:

scans.createdby = personel.username => show full name

but if:

scans.createdby doesn't match any personel.username => show username from scans.createdby instead of full name

See [this answer](http://stackoverflow.com/a/3308153/173497) to the SO question [mysql - How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?](http://stackoverflow.com/questions/3308122/how-do-i-decide-when-to-use-right-joins-left-joins-or-inner-joins-or-how-to-dete) — Kenny Evitt, Oct 30 '14 at 14:30

Z . · Accepted Answer · 2014-10-30T14:28:49.667

2

select ifnull(personel.firstname + ' ' + personel.lastname, scan.createdby) as CREATEDBY
from scans 
LEFT join personel on scans.createdby = personel.username;

edited Oct 30 '14 at 14:28

answered Oct 30 '14 at 14:23

Z .

12,657
1
31
56

well actually for the last version I get empty entries when there is no usernsme. Before revision was just fine :) Also had to use NVL function for oracle instead of ISNULL, but this is my bad that i didn't specified before. Thank you for the answer! – alex Alex Oct 30 '14 at 14:53

score 2 · Answer 2 · answered Oct 30 '14 at 14:23

2

You want a left join from scans to personel and you should use a coalesce operator (e.g. ISNULL or COALESCE in SQL Server) or function to create the CREATEDBY column in your query result.

answered Oct 30 '14 at 14:23

Kenny Evitt

9,291
5
65
93

score 2 · Answer 3 · edited Oct 30 '14 at 14:28

2

select case when personel.username is null then scans.createdby else personel.firstname ||' '|| personel.lastname end as CREATEDBY
from scans left outer join personel on scans.createdby = personel.username;

edited Oct 30 '14 at 14:28

Kenny Evitt

9,291
5
65
93

answered Oct 30 '14 at 14:26

Mike

577
4
9

Output rows in one table for which no match exists in another

3 Answers3