Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Question Unanswered: SQL query problem

    hi, i need some help with an sql query. it should be a simple sql if i could used subqueries but mysql doesn't support subqueries.

    i have 3 tables.

    Outlet table: outletname, slot, outletseq(PK)
    Phone table: phoneseq (PK), outletseq(FK)
    Data table: dataseq (PK), outletseq(FK)

    an outletname may be repeated with different slots. eg

    outlet1, slotA, 1
    outlet1, slotB, 2
    outlet1, slotC, 3
    outlet2, slotA, 4
    outlet2, slotB, 5
    .
    .
    .

    i need my sql to retrieve the outletnames that do not exist in the other 2 tables as foreign keys.

    if outlet1, slotB appears in either phone table or data tables, outlet1 should NOT be returned even if the other 2 slots of outlet1 do not exist in the other tables.

    might help your understanding to look at this subquery i tried to use.

    Select DISTINCT(o.outletname) from outlet o where o.outletname NOT IN (select o.outletname from outlet o, phonepanel p, datapanel d where p.outletseq=o.outletseq or o.outletseq=d.outletseq);

    thanx
    Last edited by jerricateh; 10-03-03 at 12:16.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    You could try using MINUS:

    Code:
    
    Select DISTINCT(outletname) from outlet 
    MINUS
    Select DISTINCT(outletname) from phonepanel
    MINUS
    Select DISTINCT(outletname) from datapanel;
    
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2003
    Posts
    4
    Originally posted by LKBrwn_DBA
    You could try using MINUS:

    Code:
    
    Select DISTINCT(outletname) from outlet 
    MINUS
    Select DISTINCT(outletname) from phonepanel
    MINUS
    Select DISTINCT(outletname) from datapanel;
    
    outletname does not exist in datapanel table or phonepanel table. but i altered your suggestion and tried it out anyway and it seems mysql does not understand MINUS

    thanx anyway

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •