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?