Kiera Smith Kiera Smith - 1 month ago 6
SQL Question

Inner Join Tables But Select From One Table Only

I have two tables which I join so that I may compare a field and extract records from one table where the field being compared is not in both tables:

Table A
---------
Comp Val
111 327
112 234
113 265
114 865


Table B
-----------
Comp2 Val2
111 7676
112 5678


So what im doing is to join both tables on Comp-Comp2, then I wish to select all values from Table A for which a corrssponding Comp does not exist in Table B. In this case, the query should result in:

Result
---------
Comp Val
113 265
114 865


Here is the query:

select * into Result from TableA
inner join TableB
on (TableB.Comp2 = TableA.Comp)
where TableB.Comp2 <> TableA.Comp


Problem is, it pulls values from both tables. Is there a way to select values from TableA alone without specifying the fields explicitly?

Answer

I think you want this, though:

select  *
    from TableA a
    where
        not exists (select b.Comp2 from TableB b where a.Comp1 = b.Comp2)

That will find all records in A that don't exist in B.

Comments