Selecting multiple values from same column in MySQL

doc_1982

I have a database which tracks a users usage history while watching a tv system. I want to show usage for certain "packages", like sports or movies etc. Right now my code looks something like

SELECT * FROM history WHERE channel IN (SELECT channel FROM history WHERE channel = 'sports1' OR channel = 'sports2' or channel = 'sports3') AND start > '2007-07-01' AND stop < '2007-07-06' and usernum = '12345'

However, when i run this code with the IN statement, it pretty much kills my server, often times timing it out. Is there a more efficient way to select multiple values inside one column, while also performing selects on other columns?

samelterrance

Hi there,

SELECT * FROM history WHERE channel IN (SELECT channel FROM history WHERE channel = 'sports1' OR channel = 'sports2' or channel = 'sports3') AND start > '2007-07-01' AND stop < '2007-07-06' and usernum = '12345'

You'd usually only use a select with an "in" if you're selecting from a different table, but here you're still selecting from the history channel.

Try

SELECT * FROM history WHERE
channel in ('sports1','sports2','sports3')
and start > '2007-07-01' AND stop < '2007-07-06' and usernum = '12345'

Cheers

doc_1982

That's ALOT quicker, thanks!

However, if i want to use it with LIKE statements, how do i incorporate this? For example right now, i have

WHERE channel IN (SELECT channel FROM history WHERE channel LIKE 'sports%')

samelterrance

Good stuff.

"Like" unfortunately won't work in an "IN" statement,
so you'd have to user:


SELECT * FROM history WHERE
channel like 'sports%'
and start > '2007-07-01' AND stop < '2007-07-06' and usernum = '12345'

or

SELECT * FROM history WHERE
channel like 'sports%' or channel like 'documentary%'
and start > '2007-07-01' AND stop < '2007-07-06' and usernum = '12345'

hope that helps.

doc_1982

Hmmm, looking at it now, it actually didn't work. It ignored the 'channel' criteria, but performed on the rest of the conditions(returning a data set involving all channels).

I had previously tried the second method you just suggested, but that ignored the rest of the conditions.

samelterrance

Sorry,

try this, with the brackets..

SELECT * FROM history WHERE
channel like ('sports%' or channel like 'documentary%')
and start > '2007-07-01' AND stop < '2007-07-06' and usernum = '12345'

doc_1982

No, i mean the original advice didn't work. The LIKE statement isn't all that important, as i can just use the full channel names instead.

koloughlin

Sam's advice should work I think

SELECT * FROM history WHERE
channel like 'sports%'
and start > '2007-07-01' AND stop < '2007-07-06' and usernum = '12345'

Be very careful if you include OR in the where clause. You'll need to wrap the OR piece with parenthesis, e.g.

SELECT * FROM history WHERE
(channel = 'sports1' or channel = 'sports2' or channel = 'sports3')
and start > '2007-07-01' AND stop < '2007-07-06' and usernum = '12345'

doc_1982

SELECT * FROM history WHERE
(channel = 'sports1' or channel = 'sports2' or channel = 'sports3')
and start > '2007-07-01' AND stop < '2007-07-06' and usernum = '12345'

This worked, thanks!