Search code examples
sqloracle

Select rows with same item code but different value in another column

Item

I have data from 2 tables. Item and Location are from table A and Description from Table B. Im trying to write an Oracle SQL where I am able to obtain those items and locs where the description in Loc 1 is different from all the other Locs for the same item but show me rows with Loc 1 and Loc 2 only.

I tried getting the full list and then a export to excel and then finding duplicates etc but that list is so long that the export fn does not work and so does copy-paste into excel as well.

The end game here is that I need to update the Loc 1 description with what is there in the Loc 2 rows.

Therefore here is what I aim to achieve with this SQL.

enter image description here

Solution

  • You can use conditional aggregation with analytic functions:

    SELECT item,
       location,
       description
FROM   (
  SELECT item,
         location,
         description,
         COUNT(DISTINCT CASE location WHEN 'Loc 1' THEN description ELSE NULL END) OVER (
           PARTITION BY item
         ) AS num_loc1,
         COUNT(DISTINCT CASE location WHEN 'Loc 1' THEN NULL ELSE description END) OVER (
           PARTITION BY item
         ) AS num_other_loc,
         COUNT(DISTINCT description) OVER (PARTITION BY item) AS num_descr
  FROM   a
         INNER JOIN b
         ON a.item_id = b.item_id
)
WHERE  num_descr > 1
AND    num_loc1 = 1
AND    num_other_loc + num_loc1 = num_descr
AND    location IN ('Loc 1', 'Loc 2')

    or, depending on how you want to implement the logic of "where the description in Loc 1 is different from all the other Locs for the same item", you can change the final filter to:

    WHERE  num_descr > 1
AND    num_loc1 = 1
AND    num_other_loc + num_loc1 = num_descr
AND    location IN ('Loc 1', 'Loc 2')

    Which, for the sample data:

    CREATE TABLE a (item_id, item, location) AS
SELECT  1, 'Item A', 'Loc 1' FROM DUAL UNION ALL
SELECT  2, 'Item A', 'Loc 2' FROM DUAL UNION ALL
SELECT  3, 'Item A', 'Loc 3' FROM DUAL UNION ALL
SELECT  4, 'Item A', 'Loc 4' FROM DUAL UNION ALL
SELECT  5, 'Item B', 'Loc 1' FROM DUAL UNION ALL
SELECT  6, 'Item B', 'Loc 2' FROM DUAL UNION ALL
SELECT  7, 'Item B', 'Loc 3' FROM DUAL UNION ALL
SELECT  8, 'Item B', 'Loc 4' FROM DUAL UNION ALL
SELECT  9, 'Item C', 'Loc 1' FROM DUAL UNION ALL
SELECT 10, 'Item C', 'Loc 2' FROM DUAL UNION ALL
SELECT 11, 'Item C', 'Loc 3' FROM DUAL UNION ALL
SELECT 12, 'Item C', 'Loc 4' FROM DUAL;

CREATE TABLE b (item_id, description) AS
SELECT 1, 'Tube' FROM DUAL UNION ALL
SELECT 2, 'Pipe' FROM DUAL UNION ALL
SELECT 3, 'Pipe' FROM DUAL UNION ALL
SELECT 4, 'Pipe' FROM DUAL UNION ALL
SELECT 5, 'Mallet' FROM DUAL UNION ALL
SELECT 6, 'Mallet' FROM DUAL UNION ALL
SELECT 7, 'Mallet' FROM DUAL UNION ALL
SELECT 8, 'Mallet' FROM DUAL UNION ALL
SELECT 9, 'Wrench' FROM DUAL UNION ALL
SELECT 10, 'Spanner' FROM DUAL UNION ALL
SELECT 11, 'Spanner' FROM DUAL UNION ALL
SELECT 12, 'Spanner' FROM DUAL;

    Both output:

    ITEM LOCATION DESCRIPTION
    Item A Loc 1 Tube
    Item A Loc 2 Pipe
    Item C Loc 1 Wrench
    Item C Loc 2 Spanner

    fiddle