sql server - How to remove duplicates of inner query that was used to remove duplicates in SQL? -
this sql server question, here tables working (note: column name
name of code set):
table: code set
code_set_id | name -------------+----------- 1 | jackets 2 | pants 3 | shirts
table: code set detail
code | description | code_set_id ---------------+----------------+------------ blue | blue jacket | 1 blue | blue jacket | 1 green | green jacket | 1 green | green jacket | 1 purple | purple jacket | 1
the query wrote finds duplicate code set codes , code set code set codes belong too. following query return
jackets, blue, 2 jackets, green, 2
how wrap query around following query jackets
?
select bcs.name, bcsd.code, bcsd.description, count(*) code_set_detail bcsd inner join code_set bcs on bcsd.code_set_id = bcs.code_set_id group bcs.name, bcsd.code, bcsd.description having count(*) > 1
so far, i've tried using where exists
, yet result set not correct.
with x (name, code, description, detailcount) ( select bcs.name, bcsd.code, bcsd.description, count(*) code_set_detail bcsd inner join code_set bcs on bcsd.code_set_id = bcs.code_set_id group bcs.name, bcsd.code, bcsd.description having count(*) > 1 ) select distinct name x
Comments
Post a Comment