Link to home
Start Free TrialLog in
Avatar of jaenglish
jaenglish

asked on

Problem with MySQL Multi sub query

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
)

Open in new window

database-files.zip
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Instead of complex IN() clause building you coud test the dislikes string for substring occurence:

INSTR(',' + REPLACE(dislikes, ' ', '') + ',' , ',' + REPLACE(CAST(md.dislike_id AS char), ' ', '') + ',') > 0
And the whole command could look like this:

(Sorry, I don't know MySQL syntax in all details, I also suppose just one record having mp.resident_id = 1 in menu_profile table.)
SELECT md.title 
FROM menu_dislikes md
INNER JOIN menu_dislikes_types mdt ON mdt.type_id = md.type_id AND mdt.title = 'Vegetables'
INNER JOIN menu_profile mp ON mp.resident_id = 1
WHERE INSTR(',' + REPLACE(mp.dislikes, ' ', '') + ',' , ',' + REPLACE(CAST(md.dislike_id AS char), ' ', '') + ',') > 0

Open in new window

Avatar of jaenglish
jaenglish

ASKER

Unfortunately that returns the same 1 row as I get with my query. It should return 7 rows.

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Probably is MySQL engine issue as discussed, but I'm unable to really test or upgrade at this time. pcelba was extremely helpful and accommodating.
It is not good if it still does not work. If you ask again and provide data, statements, and MySQL version then somebody else would test it on exactly same MySQL version which could help to decide about the upgrade or work around.
Well the project in question has been tabled for the moment, so it's not fair to you or anyone else to leave an abandoned question for several months while I sort out office politics. I'm satisfied enough that this is not an issue with the query to give you credit for the solution.
OK, lets leave it as it is. But please could you provide your MySQL version? Thanks.