troubleshooting Question

Problem with MySQL Multi sub query

Avatar of jaenglish
jaenglish asked on
DatabasesMySQL ServerSQL
8 Comments1 Solution324 ViewsLast Modified:
I have an issue with a MySQL query with sub queries where each subquery works independantly but not as a complete solution. I am trying to avoid having to pull a subset of data and using a PHP loop to then pull the additional data.
Scenario:
A table called 'residents' has a field called 'dislikes'. It is a comma-delimited text string, with each element being the ID of a row in another table. What I am trying to do with this query is select that string and converting it into a format that I can use in an 'IN' clause.
The loop in the SQL is governed by a counter with the max value being the length of the table of dislikes, and a 'GROUP BY' in use to restrict the replicated final ID.

I have attached a dump of the 3 tables in use to help understand the query.

The supplied query is supposed to work like this...get a the string 'dislikes' from the resident table where the resident id is 1. Get the ID of the field from the 'dislikes_types' table where the title is 'Vegetables'. Use that ID to get the IDs of the rows in the 'dislikes' table, and then use that data against the residents dislikes string to list the residents dislikes.

Hope that makes sense.

So each sub-query does work, but what is actually happening when they are all put together is that it only returns the last value.

Thanks.
SELECT md.title 
FROM menu_dislikes md
WHERE md.type_id = ( 
	SELECT mdt.type_id
	FROM menu_dislikes_types mdt
	WHERE mdt.title = 'Vegetables'
 ) AND md.dislike_id IN (
	SELECT SUBSTRING_INDEX(
		SUBSTRING_INDEX(( 
			SELECT dislikes 
			FROM menu_profile 
			WHERE resident_id = 1
		), ',', @r:=@r + 1 ), ',' , -1 ) zxz
	FROM ( SELECT @r:=0 ) x,
	( SELECT dislike_id xx FROM menu_dislikes ) z
	GROUP BY zxz
)
database-files.zip
ASKER CERTIFIED SOLUTION
pcelba

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros