SQL Server SQL returns entries with multiple reference in an other table

arknldoa 于 2023-05-05 发布在其他

关注(0)|答案(1)|浏览(149)

I'm trying to retrieve each entry from the table "Component" which appears to be in more than one "Computer". When I perform :

select Component.Piece, NO_COMPUTER from Component cmp
inner join Computer cpt
on Component.Piece = cpt.Piece
where Piece like 'i7 9210'

I get a result of two lines which is correct (like my 'i7 9210' is actually in two computers)

However now when I try to do that for each entry using :

select Component.Piece, NO_COMPUTER from Component cmp
inner join Computer cpt
on Component.Piece = cpt.Piece
GROUP BY Component.Piece, NO_COMPUTER
HAVING COUNT(DISTINCT cpt.NO_COMPUTER) > 1`

I have nothing in return

I tried to remove the DISTINCT but I can't figure out what's wrong

sql-server

来源：https://stackoverflow.com/questions/76181949/sql-returns-entries-with-multiple-reference-in-an-other-table

1条答案

按热度按时间

jk9hmnmh1#

Presumably, this is the query you wanted to write:

select t.piece, t.no_computer 
from component t
inner join computer r on r.piece = t.piece 
group by t.piece, t.no_computer
having count(*) > 1

This joins the two tables, groups by component (assuming that piece and no_computer can be used as a primary key), and filters on tuples that have more than one match.

However, I find that this kind of filtering logic would be better expressed with a correlated subquery:

select t.* 
from component t
where (select count(*) from computer r where r.piece = t.piece) > 1

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server SQL returns entries with multiple reference in an other table

1条答案

相关问题

热门标签

最新问答