Forum Stats

  • 3,733,249 Users
  • 2,246,737 Discussions
  • 7,856,634 Comments

Discussions

SELECT with MINUS

634069
634069 Member Posts: 4
edited June 2008 in SQL & PL/SQL
Hi All,

I have two tables with the following records

ORG_TAB
org_id org_name
--------- --------------
1 One
2 Two
3 Three
4 Four

SUP_ORG_TAB
sup_id org_id
---------- ---------
101 1
101 4
102 2
102 3
103 1

Now, my problem is to create a select statement that would show me all the org_id/org_name on ORG_TAB that is not present in the group of sup_id (org_id)

RESULT
sup_id org_name
------------- ------------
101 Two
101 Three
102 One
102 Four
103 Two
103 Three
103 Four

Hopefully you can help me on this.

Cheers,
Roderick

Comments

  • NicloeiW
    NicloeiW Member Posts: 1,811
    edited June 2008
    SQL> 
    SQL> With ORG_TAB As
    2 (Select 1 org_Id ,'One' Name From dual Union All
    3 Select 2, 'Two' From dual Union All
    4 Select 3, 'Three' From dual Union All
    5 Select 4, 'Four' From dual
    6 ),
    7 SUP_ORG_TAB As
    8 (Select 101 sub_id ,1 org_id From dual Union All
    9 Select 101, 4 From dual Union All
    10 Select 102, 2 From dual Union All
    11 Select 102, 3 From dual Union All
    12 Select 103, 1 From dual
    13 )
    14

    --sorry wrong query, may be some can use the with statement
    Message was edited by:
    Nicloei W
  • 643513
    643513 Member Posts: 30
    Select s.sup_id, o.org_name
    from sup_org_tab s, org_tab o
    minus
    Select s.sup_id, o.org_name
    from sup_org_tab s, org_tab o
    where s.org_id = o.org_id
  • NicloeiW
    NicloeiW Member Posts: 1,811
    i dont think op needs this,

    he needs a various permutation and combinations of sub_if and org_name
  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    edited June 2008
    Hi Roderick,

    How about,
    WITH org_tab AS
    (
    SELECT 1 org_id, 'One' NAME
    FROM dual
    UNION ALL
    SELECT 2, 'Two'
    FROM dual
    UNION ALL
    SELECT 3, 'Three'
    FROM dual
    UNION ALL
    SELECT 4, 'Four'
    FROM dual)
    ,sup_org_tab AS
    (
    SELECT 101 sub_id, 1 org_id
    FROM dual
    UNION ALL
    SELECT 101, 4
    FROM dual
    UNION ALL
    SELECT 102, 2
    FROM dual
    UNION ALL
    SELECT 102, 3
    FROM dual
    UNION ALL
    SELECT 103, 1
    FROM dual)
    SELECT s.sub_id, o.NAME
    FROM org_tab o, (SELECT sub_id, min(org_id) org_id
    FROM sup_org_tab
    GROUP BY sub_id) s
    MINUS
    SELECT s.sub_id, o.NAME
    FROM org_tab o, sup_org_tab s
    WHERE o.org_id = s.org_id;
    Regards
    Peter

    @Nicloei W, thank you for the WITH statement, good thinking :-)
    Message was edited by:
    Peter Gjelstrup
  • 450441
    450441 Member Posts: 2,525
    i dont think op needs this,

    he needs a various permutation and combinations of
    sub_if and org_name
    I must admit, I read it the same - he wants a cartesian join less the ones that already exist. Which this query does - it produces the same output as Peter's too, while being less complicated.
  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    Which this query does - it produces the same output
    as Peter's too, while being less complicated.
    You're right no need to group. Didn't see that

    Regards
    Peter
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited June 2008
    Oracle supports "Partitioned Outer Join".
    However Oracle does not support "Partitioned Anti Join".

    Therefore I have used homemade "Partitioned Anti Join".
    With ORG_TAB As
    (Select 1 org_Id ,'One' org_name From dual Union All
    Select 2, 'Two' From dual Union All
    Select 3, 'Three' From dual Union All
    Select 4, 'Four' From dual),
    SUP_ORG_TAB As
    (Select 101 sub_id ,1 org_id From dual Union All
    Select 101, 4 From dual Union All
    Select 102, 2 From dual Union All
    Select 102, 3 From dual Union All
    Select 103, 1 From dual)
    select b.sub_id,a.org_name
    from ORG_TAB a
    Left outer join SUP_ORG_TAB b
    partition by (b.sub_id)
    on (a.org_id = b.org_id)
    where b.org_id is null
    order by b.sub_id,a.org_Id;
    SUB_ID  ORG_N
    ------ -----
    101 Two
    101 Three
    102 One
    102 Four
    103 Two
    103 Three
    103 Four
  • Ganesh Srivatsav
    Ganesh Srivatsav Member Posts: 2,284
    Make a cartisan product.
    SQL> WITH org_tab AS
      2       ( SELECT 1 org_id, 'One' NAME
      3          FROM DUAL  UNION ALL
      4        SELECT 2, 'Two'
      5          FROM DUAL  UNION ALL
      6        SELECT 3, 'Three'
      7          FROM DUAL UNION ALL
      8        SELECT 4, 'Four'
      9          FROM DUAL )
     10     , sup_org_tab AS
     11       ( SELECT 101 sub_id, 1 org_id
     12          FROM DUAL UNION ALL
     13        SELECT 101, 4
     14          FROM DUAL UNION ALL
     15        SELECT 102, 2
     16          FROM DUAL UNION ALL
     17        SELECT 102, 3
     18          FROM DUAL  UNION ALL
     19        SELECT 103, 1
     20          FROM DUAL )
     21  -----
     22  SELECT a.sub_id, b.NAME  FROM sup_org_tab a, org_tab b
     23  MINUS
     24  SELECT a.sub_id, b.NAME  FROM sup_org_tab a, org_tab b
     25               WHERE a.org_id = b.org_id;
    
        SUB_ID NAME
    ---------- -----
           101 Three
           101 Two
           102 Four
           102 One
           103 Four
           103 Three
           103 Two
    
    7 rows selected.
    
    SQL> 
    thanks for nicole's with clause
  • NicloeiW
    NicloeiW Member Posts: 1,811
    thanks, actually i myself posted same query, with cartisan product but i tot its not correct
  • 634069
    634069 Member Posts: 4
    Thanks everybody for the help.

    Cheers,
    Roderick
This discussion has been closed.