CODEWITHSUNDEEP
sqlsql-servert-sql
bicycle_guy
bicycle_guy

Reputation: 299

Get records having the same value in 2 columns but a different value in a 3rd column

I am having trouble writing a query that will return all records where 2 columns have the same value but a different value in a 3rd column. I am looking for the records where the Item_Type and Location_ID are the same, but the Sub_Location_ID is different.

The table looks like this:

+---------+-----------+-------------+-----------------+
| Item_ID | Item_Type | Location_ID | Sub_Location_ID |
+---------+-----------+-------------+-----------------+
|       1 |     00001 |          20 |              78 |
|       2 |     00001 |         110 |             124 |
|       3 |     00001 |         110 |             124 |
|       4 |     00002 |           3 |              18 |
|       5 |     00002 |           3 |              25 |
+---------+-----------+-------------+-----------------+

The result I am trying to get would look like this:

+---------+-----------+-------------+-----------------+
| Item_ID | Item_Type | Location_ID | Sub_Location_ID |
+---------+-----------+-------------+-----------------+
|       4 |     00002 |           3 |              18 |
|       5 |     00002 |           3 |              25 |
+---------+-----------+-------------+-----------------+

I have been trying to use the following query:

SELECT *
FROM Table1
WHERE Item_Type IN (
     SELECT Item_Type
     FROM Table1
     GROUP BY Item_Type
     HAVING COUNT (DISTINCT Sub_Location_ID) > 1
)

But it returns all records with the same Item_Type and a different Sub_Location_ID, not all records with the same Item_Type AND Location_ID but a different Sub_Location_ID.

Upvotes: 2

Views: 181

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think you can use exists:

select t1.*
from table1 t1
where exists (select 1
              from table1 tt1
              where tt1.Item_Type = t1.Item_Type and
                    tt1.Location_ID = t1.Location_ID and
                    tt1.Sub_Location_ID <> t1.Sub_Location_ID
             );

Upvotes: 1

Jason A. Long
Jason A. Long

Reputation: 4442

This should do the trick...

-- some test data...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
BEGIN DROP TABLE #TestData; END;

CREATE TABLE #TestData (
    Item_ID INT NOT NULL PRIMARY KEY,
    Item_Type CHAR(5) NOT NULL,
    Location_ID INT NOT NULL,
    Sub_Location_ID INT NOT NULL 
    );
INSERT #TestData (Item_ID, Item_Type, Location_ID, Sub_Location_ID) VALUES
    (1, '00001',  20,  78),
    (2, '00001', 110, 124),
    (3, '00001', 110, 124),
    (4, '00002',   3,  18),
    (5, '00002',   3,  25);

-- adding a covering index will eliminate the sort operation...
CREATE NONCLUSTERED INDEX ix_indexname ON #TestData (Item_Type, Location_ID, Sub_Location_ID, Item_ID);

-- the actual solution...
WITH
    cte_count_group AS (
        SELECT 
            td.Item_ID,
            td.Item_Type,
            td.Location_ID,
            td.Sub_Location_ID,
            cnt_grp_2 = COUNT(1) OVER (PARTITION BY td.Item_Type, td.Location_ID),
            cnt_grp_3 = COUNT(1) OVER (PARTITION BY td.Item_Type, td.Location_ID, td.Sub_Location_ID)
        FROM
            #TestData td
        )
SELECT 
    cg.Item_ID,
    cg.Item_Type,
    cg.Location_ID,
    cg.Sub_Location_ID
FROM
    cte_count_group cg
WHERE 
    cg.cnt_grp_2 > 1
    AND cg.cnt_grp_3 < cg.cnt_grp_2;

Upvotes: 3

Serg
Serg

Reputation: 22811

Sql server has no vector IN so you can emulate it with a little trick. Assuming '#' is illegal char for Item_Type

SELECT *
FROM Table1
WHERE Item_Type+'#'+Cast(Location_ID as varchar(20)) IN (
     SELECT Item_Type+'#'+Cast(Location_ID as varchar(20))
     FROM Table1
     GROUP BY Item_Type, Location_ID
     HAVING COUNT (DISTINCT Sub_Location_ID) > 1
);

The downsize is the expression in WHERE is non-sargable

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use exists :

select t.*
from table t
where exists (select 1 
              from table t1 
              where t.Item_Type = t1.Item_Type and
                    t.Location_ID = t1.Location_ID and
                    t.Sub_Location_ID <> t1.Sub_Location_ID
             );

Upvotes: 1

Related Questions