SQL Server Inner Join Tables But Select From One Table Only

xxhby3vn 于 2023-06-21 发布在其他

关注(0)|答案(6)|浏览(135)

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?

sql-server

来源：https://stackoverflow.com/questions/31325670/inner-join-tables-but-select-from-one-table-only

6条答案

按热度按时间

jtjikinw1#

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

赞(0）回复(0）举报 2023-06-21

0s0u357o2#

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.

赞(0）回复(0）举报 2023-06-21

nvbavucw3#

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

赞(0）回复(0）举报 2023-06-21

uxh89sit4#

You could also do a left join and then just return rows that do not match:

SELECT TableA.*
FROM TableA
    LEFT JOIN TableB
        ON TableA.Comp = TableB.Comp2
WHERE TableB.Comp IS NULL

You can restrict the columns return like this, but it is typically better to not user the *, but to name all of the columns.

赞(0）回复(0）举报 2023-06-21

9avjhtql5#

For me works like this. TABLE ARE FOR TESTS

select ov_item.*, ov.data_fecha from ov_item
inner join ov
on ov_item.id_ov = ov.id
where id_vendedor = 50 or id_tecnico = 50

赞(0）回复(0）举报 2023-06-21

vpfxa7rd6#

SELECT A.* FROM RMS_User A LEFT JOIN RMS_Department D ON A.DepartmentId = D.Id WHERE D.Identifier = 'ADMIN'

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Inner Join Tables But Select From One Table Only

6条答案

相关问题

热门标签

最新问答