SQL Server Performance

Query Multiple Fields

Discussion in 'SQL Server 2005 General Developer Questions' started by vimalpercy, Jun 20, 2010.

  1. vimalpercy New Member

    Hello Experts
    I have 2 tables TblA and TblB
    TblA has records as shown
    ID Names
    1 SomeName
    2 Some Other Name
    TblB has Records as shown
    ID Names
    1 SomeName
    2 Some Other Name
    3 Other Name
    I want to get the 3rd record from TblB which does not exists in TblA.
    I tried this query but it retrieves all the 3 records.
    Select A.ID, A.Name from TblB A
    WHERE Exists
    (Select B.ID, B.Name from TblA B WHERE A.ID=B.ID AND A.Names<>B.Names)
    Thanks in advance for the help
    With Regards
  2. Adriaan New Member

    Well, you're looking for a key value in tblB that does NOT exist in tblA - hint, hint ...
    Your EXISTS subquery has the same effect as an INNER JOIN between tblA and tblB on the ID columns (1 matches 1, 2 matches 2) with a WHERE clause filtering on different names for the same ID value.
  3. vimalpercy New Member

    Hello Adriaan
    Do you mean like this?
    Select A.ID, A.Name from TblB A
    WHERE Not Exists
    (Select B.ID, B.Name from TblA B WHERE A.ID=B.ID AND A.Names=B.Names)
  4. Adriaan New Member

    Yes, you're going in the right direction.
    However, you should look at what exactly you want to match. There is a natural key (name) as well as a substitue key (ID), and you are now matching on both keys at the same time.
    Instead of the NOT EXISTS subquery, you could also use a "find unmatched" query like this:
    SELECT A.Name
    FROM tblA A
    LEFT JOIN tblB B ON A.Name = B.Name
    WHERE B.Name IS NULL
  5. vimalpercy New Member

    Thanks Adriaan for the help.
    You are the man.
    Have a nice evening!
    Thanks and Regards

Share This Page