Find differences in mysql table and query

Question

I hope I can explain this correctly. Here it is:

I have a list of id's that I need to check in a mysql table against a subscription status to find out how many of those id's are current. I have the id and the subscriptionEnd. My query is not working and I do not have enough knowledge to know how this would be done. Please review and share with me what I could do. There are thousands of id's I need to match against.

SELECT c.id, c.subscriptionEnd
FROM subscriptions c
WHERE c.subscriptionEnd > '2013-01-27`
AND c.id = 12345
AND c.id = 12939
....

This list goes on and on and I see that this will not work. But the problem is I don't know how to get this to work. Very much appreciated with any solutions on how I can check and get the results of the id's that have a subscriptionEnd greater than the date given.

Thank you

I assume you mean OR between those ids...AND will never be true. — lc.
– lc., Commented Jan 28, 2013 at 19:02

lc. · Accepted Answer · 2013-01-28 19:01:52Z

1

Your best bet, since you have 1000's of values to check against, is to insert all of the ids you want to check into a temporary table - let's call the table checkIds with a single column id.

Then you can simply do:

SELECT c.id, c.subscriptionEnd
FROM subscriptions c
INNER JOIN checkIds ON c.id = checkIds.id
WHERE c.subscriptionEnd > '2013-01-27'

And finally drop the temporary table when you are done.

Other solutions include:

A giant unwieldy IN clause which will probably be a performance nightmare:

SELECT c.id, c.subscriptionEnd
FROM subscriptions c
WHERE c.subscriptionEnd > '2013-01-27'
AND c.id IN (12345, 12939, ...)

Finding a pattern in your list of IDs:

SELECT c.id, c.subscriptionEnd
FROM subscriptions c
WHERE c.subscriptionEnd > '2013-01-27'
AND (c.id = 12345
    OR c.id BETWEEN 12939 AND 12951
    OR ...)

edited Jan 28, 2013 at 19:01

answered Jan 28, 2013 at 18:55

lc.

117k21 gold badges163 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pertrai1 Over a year ago

Perfect with the temporary table. Thank you

Collectives™ on Stack Overflow

Find differences in mysql table and query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related