Jump to content

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


Go to solution Solved by Barand,

Recommended Posts

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.

Link to post
Share on other sites

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?

Link to post
Share on other sites

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"

Link to post
Share on other sites
  • Solution

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;

 

  • Great Answer 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.