Select to create multi-field row showing items missing from list-CodePudding

This sounds similar to other questions asked, but I have tried (unsuccessfully) to adapt them... Oracle database, if that matters. We have sales folks who are assigned any combination of NORTH, EAST, SOUTH or WEST regions (1, 2, 3 or all 4). I would like to create a table that shows what's missing. So, table sales_regions holds:

SELECT user, region, current    
FROM sales_regions    
WHERE region IN ('NORTH','EAST','SOUTH','WEST');


USER      REGION   CURRENT
---- -
U1        EAST     Y    
U1        SOUTH    Y    
U2        NORTH    Y    
U3        NORTH    Y    
U3        EAST     Y    
U3        SOUTH    Y    
U3        WEST     Y

I am trying to come up with a query that would produce a list that, when combined with the existing one, would give every salesperson every region:

USER      REGION   CURRENT    
U1        WEST     N    
U1        NORTH    N    
U2        EAST     N    
U2        SOUTH    N    
U2        WEST     N

An old post, containing only 1 field, had the solution:

SELECT v.fruit FROM (    
    SELECT 'apple' AS fruit    
    UNION ALL    
    SELECT 'banana'    
    UNION ALL    
    SELECT 'carrot') v    
LEFT JOIN friuttable ft ON ft.fruit = v.fruit    
    WHERE ft.fruit IS NULL

This seems so close - but I can't get the multi-field query to work...

CodePudding user response：

You can create a result set that holds every combination of distinct sales person and region:

SELECT t1.user, t2.region
FROM (SELECT DISTINCT user FROM sales_region) t1
    ,(SELECT DISTINCT region FROM sales_region) t2;

You can then use set operator MINUS to get the difference between this table and your table:

SELECT t1.user, t2.region
FROM (SELECT DISTINCT user FROM sales_region) t1
    ,(SELECT DISTINCT region FROM sales_region) t2
MINUS
SELECT user, region 
FROM sales_region;

That should spit out a table that has only the user/region combinations that aren't already present in your sales_region table.

CodePudding user response：

Try this:

    WITH sales_regions AS (
  SELECT 'U1' AS username, 'EAST' AS region, 'Y' AS CURRENT_ from dual UNION ALL 
  SELECT 'U1' AS username, 'SOUTH' AS region, 'Y' AS CURRENT_ from dual UNION ALL 
  SELECT 'U2' AS username, 'NORTH' AS region, 'Y' AS CURRENT_ from dual UNION ALL 
  SELECT 'U3' AS username, 'EAST' AS region, 'Y' AS CURRENT_ from dual UNION ALL 
  SELECT 'U3' AS username, 'SOUTH' AS region, 'Y' AS CURRENT_ from dual UNION ALL 
  SELECT 'U3' AS username, 'NORTH' AS region, 'Y' AS CURRENT_ from dual UNION ALL 
  SELECT 'U3' AS username, 'WEST' AS region, 'Y' AS CURRENT_ from dual ),
  users AS (
    SELECT 'U1' AS username FROM dual UNION ALL
    SELECT 'U2' AS username FROM dual UNION ALL
    SELECT 'U3' AS username FROM dual),
  regions AS (
    SELECT 'EAST' AS region FROM dual UNION ALL
    SELECT 'SOUTH' AS region FROM dual UNION ALL
    SELECT 'NORTH' AS region FROM dual UNION ALL
    SELECT 'WEST' AS region FROM dual
  )
  SELECT u.username, r.region, 'N' AS current_
  FROM users u JOIN regions r ON 1=1
  LEFT JOIN sales_regions sr ON u.username = sr.username AND r.region = sr.region
  WHERE sr.username IS NULL

Or the MINUS operator if the records are unique or they are not, but you want them to be:

    WITH sales_regions AS (
  SELECT 'U1' AS username, 'EAST' AS region, 'Y' AS CURRENT_ from dual UNION ALL 
  SELECT 'U1' AS username, 'SOUTH' AS region, 'Y' AS CURRENT_ from dual UNION ALL 
  SELECT 'U2' AS username, 'NORTH' AS region, 'Y' AS CURRENT_ from dual UNION ALL 
  SELECT 'U3' AS username, 'EAST' AS region, 'Y' AS CURRENT_ from dual UNION ALL 
  SELECT 'U3' AS username, 'SOUTH' AS region, 'Y' AS CURRENT_ from dual UNION ALL 
  SELECT 'U3' AS username, 'NORTH' AS region, 'Y' AS CURRENT_ from dual UNION ALL 
  SELECT 'U3' AS username, 'WEST' AS region, 'Y' AS CURRENT_ from dual ),
  users AS (
    SELECT 'U1' AS username FROM dual UNION ALL
    SELECT 'U2' AS username FROM dual UNION ALL
    SELECT 'U3' AS username FROM dual),
  regions AS (
    SELECT 'EAST' AS region FROM dual UNION ALL
    SELECT 'SOUTH' AS region FROM dual UNION ALL
    SELECT 'NORTH' AS region FROM dual UNION ALL
    SELECT 'WEST' AS region FROM dual
  )
  SELECT username, region, 'N' AS current_
  FROM (
    SELECT u.username, r.region
    FROM users u JOIN regions r ON 1=1
    MINUS
    SELECT sr.username, sr.region
    FROM sales_regions sr ) t

Hope this helps!

PS: Please avoid using keywords as object or column names!