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.
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.
SELECT md.title FROM menu_dislikes mdWHERE 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)
”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
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.