Result taken from 2 tables
-
hi, i'm beginner in SQL Server. let's say i've have these following table(s)
tblA
companyid | staffname
-------------------------------
ABC123 | William
ABC127 | Hanna
ABC123 | Robert
ABC106 | Zach
tblB
companyid | staffname
-------------------------------
ABC123 | Jennifer
ABC129 | Gonzalez
ABC123 | Karen
ABC128 | Erdogan
how to query from both table where companyid='ABC123'? expected result shown as follows
companyid | staffname
-------------------------------
ABC123 | William
ABC123 | Robert
ABC123 | Jennifer
ABC123 | Karen
tq.
-
select companyid,staffname
from
(select companyid,staffname,1 as sequence
from tblA
where companyid='ABC123'
union
select companyid,stafname,2 as sequence
from tblB
where companyid='ABC123'
) AS mynewtable
order by companyid,stafname,sequence
-
And just to point out some details. If you do not care for the ordering of the data amongst the two tables you do not need to add (or use) the column 'sequence'.
A gotcha is that UNION _removes_ duplicates from the resultset whilst UNION ALL would keep duplicates. This is important if you have enormous amounts of data (UNION ALL eliminates the extra steps UNION must do in evaluating the resultset).
Happy SQLing!
Hanslindgren
-
tq very much.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply