CODEWITHSUNDEEP
sqlms-accessjoin
user6440081
user6440081

Reputation:

SQL Full Outer Join and combining two columns

I have the following two tables:

TableA:

UserID      PermissionTableA
UserA       PermissionA
UserB       PermissionA
UserC       PermissionB
UserD       PermissionB

TableB:

UserID      PermissionTableB    
UserA       PermissionC     
UserA       PermissionD     
UserB       PermissionC     
UserB       PermissionD

The two tables shall be joined via ID and the Permissions should be combined into one single column, like this:

Solution:

UserID      Permission      
UserA       PermissionC     
UserA       PermissionD     
UserA       PermissionA     
UserB       PermissionC     
UserB       PermissionD     
UserB       PermissionA

My current solution however, with full outer join, looks like this:

UserID      PermissionTableA    PermissionTableB
UserA       PermissionC         -
UserA       PermissionD         -
UserA       -                   PermissionA     
UserB       PermissionC         -
UserB       PermissionD         -
UserB       -                   PermissionA

Is it possible to combine both columns into one, similar to the solution table above with a SQL query (using MS Access).

Thanks in advance.

Upvotes: 0

Views: 765

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You are not looking for a join :-) A join combines records. What you are after is the union of two query results.

select UserID, PermissionTableA as Permission
from TableA
UNION 
select UserID, PermissionTableB
from TableB
where UserID in (select UserID from TableA)
order by UserID, Permission;

UPDATE: You may as well use your outer join with NZ. Sorry, it took me some time to see what the query was supposed to do.

select UserId, NZ(PermissionTableA, PermissionTableB) as Permission
from ...

Upvotes: 1

Related Questions