Results 1 to 4 of 4

Thread: SQL query

  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Question Unanswered: SQL query

    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)

    bascially, outletname and slot can uniquely identify a row in the outlet table but is replaced outletseq, which is also uniuq and is used to link to other table. an outletname may be repeated with different slots. eg

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


    Note that outletname does not exist in the other 2 tables.

    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

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    The following query will give you what you want:

    SELECT outletname, IF(count(p.phoneseq)=0 AND count(d.dataseq)=0, 1, 0) AS chooser FROM outlet o
    LEFT JOIN phonepanel p ON p.outletseq = o.outletseq
    LEFT JOIN datapanel d ON d.outletseq = o.outletseq
    GROUP BY outletname HAVING chooser = 1;

  3. #3
    Join Date
    Oct 2003
    Posts
    4
    Originally posted by aus
    The following query will give you what you want:

    SELECT outletname, IF(count(p.phoneseq)=0 AND count(d.dataseq)=0, 1, 0) AS chooser FROM outlet o
    LEFT JOIN phonepanel p ON p.outletseq = o.outletseq
    LEFT JOIN datapanel d ON d.outletseq = o.outletseq
    GROUP BY outletname HAVING chooser = 1;
    Thanx. It worked. but what's 0,1,0?

    and i just realised that i needed to ensure that the outletname itself does not exist in another table called workarea. Is this how i do it? i'm not used to outer and inner joins. outletname would be a FK in workarea table and may only appear once.

    SELECT o.outletname, IF(count(p.outletseq)=0 AND count(d.outletseq)=0, 1, 0) AS chooser, IF(count(w.outletname)=0,1,0) AS colname FROM outlet o
    LEFT JOIN phonepanel p ON p.outletseq = o.outletseq
    LEFT JOIN datapanel d ON d.outletseq = o.outletseq
    LEFT JOIN workarea w ON w.outletname = o.outletname
    GROUP BY outletname HAVING chooser = 1;

    Appreciate your help.

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    No problem,

    That would work if you added
    AND colname = 1
    to your HAVING clause

    The each part of "0,1,0" means
    0 : part of the If(...) = 0 statement
    1: the return value if the statement in the first argument is true
    0: the return value if the statement is false

Posting Permissions

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