CODEWITHSUNDEEP
sqlsql-servert-sql
phil o.O
phil o.O

Reputation: 416

How to remove duplicates of inner query that was used to remove duplicates in SQL?

This is a SQL Server question, so here are the tables I am working with (note: column NAME is the name of the 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 I wrote currently finds all duplicate code set codes and which code set those code set codes belong too. The following query would return 

JACKETS, BLUE,  2
JACKETS, GREEN, 2

How would I wrap another query around the following query so that I only get JACKETS?

SELECT 
    bcs.NAME, bcsd.CODE, bcsd.DESCRIPTION, COUNT(*)
FROM 
    CODE_SET_DETAIL as bcsd
INNER JOIN 
    CODE_SET as bcs ON bcsd.CODE_SET_ID = bcs.CODE_SET_ID
GROUP BY 
    bcs.NAME, bcsd.CODE, bcsd.DESCRIPTION
HAVING 
    COUNT(*) > 1

So far, I've tried using WHERE EXISTS, yet the result set does not look correct.

Upvotes: 0

Views: 59

Answers (2)

NetMage
NetMage

Reputation: 26926

WITH X (Name, Code, Description, DetailCount) AS (
    SELECT bcs.NAME, bcsd.CODE, bcsd.DESCRIPTION, COUNT(*)
    FROM CODE_SET_DETAIL as bcsd
    INNER JOIN CODE_SET as bcs 
    ON bcsd.CODE_SET_ID = bcs.CODE_SET_ID
    GROUP BY bcs.NAME, bcsd.CODE, bcsd.DESCRIPTION
    HAVING COUNT(*) > 1
)
SELECT DISTINCT Name
FROM X

Upvotes: 1

Chris Maurer
Chris Maurer

Reputation: 2600

If you really want only the "jackets" value, why don't you add " bcs.name='jackets' " to your HAVING clause and skip the whole outer query, or am I missing something?

Upvotes: 0

Related Questions