CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008
Hitesh
Hitesh

Reputation: 1228

Get Common data from two different table

Hi I have 2 table With same column name (Id,FName, LName,DOB,Type,Contact, Add).

Now I want to get all data as a separate row which have same FName, LName and DOB from both table as order by Fname .

I try to use intersect and Union but not work for me.

  Select * from #t1 where FirstName in (select FirstName from #t2 ) and LastName in  (select LastName from #t2 ) and DateOfBirth in (select DateOfBirth from #t2 ) 
 UNION
Select * from #t2 where FirstName in (select FirstName from #t1 ) and LastName in  (select LastName from #t1 ) and DateOfBirth in (select DateOfBirth from #t1 )      order by FirstName ,LastName

Thanks

Upvotes: 1

Views: 9988

Answers (4)

Steve Pettifer
Steve Pettifer

Reputation: 2043

This would do it I think but I'm sure there's a more succinct way...

SELECT T1.*
FROM T1
INNER JOIN T2 
    ON T2.FirstName = T1.FirstName
    AND T2.LastName = T1.LastName
    AND T2.DateOfBirth = T1.DateOfBirth
UNION ALL
SELECT T2.*
FROM T2
INNER JOIN T1 
    ON T1.FirstName = T2.FirstName
    AND T1.LastName = T2.LastName
    AND T1.DateOfBirth = T2.DateOfBirth

Upvotes: 3

ɹɐqʞɐ zoɹǝɟ
ɹɐqʞɐ zoɹǝɟ

Reputation: 4370

this will work

Select a.*,b.* from #t1 a join #t2 b on (a.FName=b.FName and a.LName=b.LName and a.DOB=b.DOB)

Upvotes: 1

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11765

Try this

SELECT T1.Id,
       T1.FName, 
       T1.LName,
       T1.DOB,
       T1.Type,
       T1.Contact,
       T1.Add
FROM #t1 T1 INNER JOIN #t2 T2
   ON  T1.FName=T2.FName
   AND T1.LName=T2.LName
   AND T1.dob=T2.dob

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Another way to approach this is by aggregating after the union. Then check that the values appear the requisite number of times:

select firstname, lastname, dob
from ((select firstname, lastname, dob, 't1' as which
       from #t1
      ) union all
      (select firstname, lastname, dob, 't2' as which
       from #t2
      )
     ) t
group by firstname, lastname, dob
having count(distinct which) = 2;

Upvotes: 1

Related Questions