Jason Kimbel Jason Kimbel - 1 month ago 5
SQL Question

Query help consolidating two tables

I have two tables (same structure) from two different databases that I'd like to consolidate using a single query if possible.

I'm trying to retrieve all distinct serial numbers and their item name, and two category identifiers. The serial number is stored in 4 fields though. The other problem is the name and category field wont always be the same between the two tables (even though they should be - but that's another issue all together). So, I want the query to return distinct SNs and the name and cat fields from the first table.

So I started with:

SELECT
LEFT(NUMBR_1,4) + '-' + LEFT(NUMBR_2,4) + '-' + LEFT(NUMBR_3,3) + '-' + LEFT(NUMBR_4,5) AS SN
,DESCR
,TYP
,ATNUM
FROM DB1.dbo.table1
UNION
SELECT
LEFT(NUMBR_1,4) + '-' + LEFT(NUMBR_2,4) + '-' + LEFT(NUMBR_3,3) + '-' + LEFT(NUMBR_4,5) AS SN
,DESCR
,TYP
,ATNUM
FROM DB2.dbo.table2


From there I'd manually complete the consolidation in Excel and feed that data into the necessary report. I was hoping to get the final result using just SQL, but doing so is outside of my skill set.

I wrapped the above query in another select to get distinct or group by SN - which gets me the final consolidated list of SN. However, because those values themselves weren't something I could use to then query the other fields from the first table (at least that I could figure out), I wasn't sure how to proceed. Any help would be appreciated. Thanks.

Answer
SELECT 
    LEFT(NUMBR_1,4) + '-' + LEFT(NUMBR_2,4) + '-' + LEFT(NUMBR_3,3) + '-' + LEFT(NUMBR_4,5) AS SN,
    ,coalesce(t1.DESCR, t2.DESCR) DESCR,
    ,coalesce(t1.TYP, t2.TYP) TYP
    ,coalesce(t1.ATNUM, t2.ATNUM) ATNUM
FROM DB1.dbo.table1 t1
FULL JOIN DB2.dbo.table2 t2 ON
     t1.NUMBR_1 = t2.NUMBR_1 AND t1.NUMBR_2 = t2.NUMBR_2 AND t1.NUMBR_3 = t2.NUMBR_3 AND t1.NUMBR_4 = t2.NUMBR_4