Sub-Select Workaround [Archive]

View Full Version : Sub-Select Workaround

wtraylor

02-22-02, 18:32

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

SELECT organizations.name 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?

Thanks!
--Walker

rwilkerson

02-27-02, 15:11

An outer join should do what you need:

SELECT o.name
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.

SEKA

05-23-02, 06:46

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 table1.id=table2.id;

The queries:

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

Can be rewritten as:

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

thanks