Kiera Smith Kiera Smith - 4 months ago 17
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:

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?


I think you want this, though:

select  *
    from TableA a
        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.