Re: DBI question with placeholders and IN

i once wanted to to this:

SELECT
 events.b_g
FROM
 events
WHERE
 events.b_g NOT IN ( SELECT otf.b_g FROM otf )
AND
 events.b_g NOT IN ( SELECT requests.b_g FROM requests)
[download]

and this...

...
WHERE
 events.b_g IN ( SELECT otf.b_g FROM otf )
OR
 events.b_g IN ( SELECT requests.b_g FROM requests )
...
[download]

instead i had to do this:

SELECT
 events.b_g,
 events.vector,
 SUM(tries) AS 'attempts',
 COUNT(DISTINCT events.observer) AS 'reporters'
FROM
 events

LEFT JOIN otf      ON events.b_g = otf.b_g
LEFT JOIN requests ON events.b_g = requests.b_g
WHERE
 requests.b_g IS NULL
AND
      otf.b_g IS NULL

GROUP BY events.b_g, events.vector
ORDER BY events.b_g, events.vector, 'attempts'
[download]

and...

...
WHERE
 requests.b_g = events.b_g
OR
      otf.b_g = events.b_g
...
[download]

this might or might not help.

Comment on Re: DBI question with placeholders and IN Select or Download Code

Replies are listed 'Best First'.
Try IN with sub-select instead of placeholders by blssu (Pilgrim) on Oct 02, 2002 at 14:03 UTC
If you were using Oracle, then the code you wanted to run should have worked as-is. You could also use UNION in a sub-select like this: `SELECT events.b_g FROM events WHERE events.b_g NOT IN ((SELECT otf.b_g FROM otf) UNION (SELECT requests.b_g FROM requests))` [download] That shouldn't be any faster, but the logic is cleaner IMHO. I agree with you that there are alternatives to using IN with literals (or placeholders). All of those alternatives require the values you want to test to be in the database though. If you ever find code using lots of similar IN queries, sometimes it's worth creating a temporary table to cache values for your IN clauses. It works like this: `# insert all of the values you want to test with IN INSERT INTO temp (id, value) VALUES (?, ?); # now replace the placeholder IN with a sub-select SELECT ... WHERE column IN (SELECT value FROM temp WHERE id=?); # after you're completely done, remove the values -- you # might just rollback instead of deleting if the inserts # were never commited. DELETE FROM temp where id=?;` [download] I use code very similar to this when a user logs into our application. Lots of multi-valued security and preference checks can be flattened out into a temporary table. The IN statements become much simpler and more robust (you don't have to worry about the query blowing up if you have too many placeholders).	[reply] [d/l] [select]

Replies are listed 'Best First'.

Try IN with sub-select instead of placeholders
by blssu (Pilgrim) on Oct 02, 2002 at 14:03 UTC

If you were using Oracle, then the code you wanted to run should have worked as-is. You could also use UNION in a sub-select like this:

SELECT events.b_g FROM events WHERE
 events.b_g NOT IN ((SELECT otf.b_g FROM otf) UNION
                    (SELECT requests.b_g FROM requests))
[download]

I agree with you that there are alternatives to using IN with literals (or placeholders). All of those alternatives require the values you want to test to be in the database though.

If you ever find code using lots of similar IN queries, sometimes it's worth creating a temporary table to cache values for your IN clauses. It works like this:

# insert all of the values you want to test with IN
INSERT INTO temp (id, value) VALUES (?, ?);

# now replace the placeholder IN with a sub-select
SELECT ...
 WHERE column IN (SELECT value FROM temp WHERE id=?);

# after you're completely done, remove the values -- you
# might just rollback instead of deleting if the inserts
# were never commited.
DELETE FROM temp where id=?;
[download]

[reply]
[d/l]
[select]