Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002

    Unanswered: Sub-Select Workaround

    Hi guys,
    I'm having some trouble crafting a select query without using a sub select. Here's what I want:

    SELECT from organizations WHERE organizationID NOT IN (SELECT organizationID FROM lanAdmin_organization_relations WHERE lanAdminID=7);

    I want to list all of the organizations that the lanAdmin with lanAdminID=7 is NOT a part of, based on the lanAdmin_organization_relations table.

    Any ideas on what sort of joins I can use?


  2. #2
    Join Date
    Feb 2002
    Baltimore, MD
    An outer join should do what you need:

    FROM       organizations o RIGHT JOIN lanadmin_organization_relations lor
                                      ON o.organizationID = lor.organization_id
    WHERE      lor.lanAdminID = 7
               AND o.organizationID IS NULL
    I don't know your data model, so this may not be exactly correct, but should be enough to get you started.

    Hope it helps.
    Rob Wilkerson

  3. #3
    Join Date
    May 2002
    MySQL currently only supports sub selects of the form INSERT ... SELECT ... and REPLACE ... SELECT .... You can however use the function IN() in other contexts.

    In many cases you can rewrite the query without a sub-select:

    SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

    This can be re-written as:

    SELECT table1.* FROM table1,table2 WHERE;

    The queries:

    SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
    SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where;

    Can be rewritten as:

    SELECT table1.* FROM table1 LEFT JOIN table2 ON where IS NULL


Posting Permissions

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