Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an application with a function that uses a stored procedure (SQL Server 2008 R2) to pull in and arrange data from one table. Now, I’ve been asked to make it pull from two tables, but it’s not just a straight join. The tables have overlapping records (a common ID field between them), so my end result needs to have a field that indicates whether that row had a record from Table A only, from Table B only, or if it had records in both Tables A and B.

I am not a SQL guru (my queries are usually just selects with simple joins), so I’m having a hard time figuring this out. My current thought is to create three temp tables (one for A-only records, one for B-only records, and one for A+B records) then union them together for the output, but I’m sure there’s a better, more elegant/proper way to do this. Any thoughts on how this could be accomplished?


table setup

share|improve this question

1 Answer 1

up vote 3 down vote accepted

You can use a full outer join between your tables and check against null values to figure out where the data comes from.

select isnull(A.ID, B.ID) as ID,
       isnull(A.Name, B.Name) as Name,
       case
         when A.ID is null then 'B'
         when B.ID is null then 'A'
         else 'A+B'
       end as Tables   
from TableA as A
  full outer join TableB as B
    on A.ID = B.ID
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.