SQL Query

Posted on 2011-04-25
Last Modified: 2012-05-11
Hey Guys,

I have two tables - tblMain and tblOT.  Both tables have LAST, FIRST, and DOB fields.  I'd like to find all of the records in tblOT that have a matching record in tblMain using LAST, FIRST, and DOB as the identifying fields.  

I know this will match on LAST:

Select * from tblOT T1 where
T1.[LAST] in (select T2.Last from tblMain T2)

But I have no idea how to match on all three fields.  

Thanks in advance for any help you can provide.  

Question by:ttist25
    LVL 3

    Expert Comment

    Try this :

    select T1.* from tblOT T1, tblMain T2
    where T1.LAST = T2.LAST and
                T1.FIRST = T2.FIRST and
                 T1.DOB = T2.DOB
    LVL 22

    Accepted Solution


    Select T1.*, T2.* from tblMain T1
    Join tblOT T2 ON T1.LAST  = T2.Last AND T1.first  = T2.first AND T1.DOB = T2.DOB

    LVL 39

    Expert Comment

    by:Pratima Pharande
    Select LAST, FIRST,DOB from tblOT where
    Exists  (select  LAST, FIRST,DOB from tblMain )
    LVL 40

    Expert Comment

    You can try the INNER JOIN as mentioned above (or) an EXISTS clause.
    SELECT * 
      FROM tblOT T1 
                     FROM tblMain T2 
                    WHERE T1.[LAST] = T2.[LAST] 
                          AND T1.[FIRST] = T2.[FIRST] 
                          AND T1.DOB = T2.DOB)

    Open in new window

    LVL 1

    Author Closing Comment

    This worked perfectly.  Sorry for the delay in responding.  Things got crazy here.  

    Thanks again!

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now