Select id that has more than one value in an in clause

[Adamhumbug]

Hi,

A person has a person id and they also have a list of zones (301,401,501,601,701 etc)

I need a query that gives me the person id if their zone list contains more than one of (301,401,501)

op_id - person id

pr_azones - product ids

 

I know this is going to be an IN, but not sure on the more than one bit.

[Barand]

You really have to stop storing your data as comma separated lists.

I would expect that one solution could be along the same lines as that I gave you for the consecutive dates.

What does the data look like in the table?

[Adamhumbug]

I am in 100% agreement, i am afraid the stuff i am doing at the minute is not on my database and it is unlikely to get any better in the short term.

 

So i have a person table with:

id - fname - lname

1 - Adam - Smith

2 - John - Hughes

I have a badge table with:

badgeid - personid - zones

1 - 1 - 101,210,301,401,501,601

2 - 2 - 301,601,1101

and i need to get a list together of everyone that has more than one of the zones 301,401,501.

 

Hopefully this does not require something as wonderful as the function in the last "mess tidying series of SQL statements"

[Barand]

Your hopes are not in vain

select person_id
     , zones
from (
		select person_id
			 , zones
			 , find_in_set('301', zones) > 0 as has301
			 , find_in_set('401', zones) > 0 as has401
			 , find_in_set('501', zones) > 0 as has501
		from ahtest
     ) checks   
WHERE has301 + has401 + has501 > 1;

 

[Adamhumbug]

As always - thank you so much!