Inner Join Tables But Select From One Table Only

Question

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 1

Just prefix the * with the desired table name, like this:

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

Answer 2

SELECT a.* 
FROM tblA a,tblB b 
WHERE a.comp <> b.comp