	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

10/27/2009 at 01:10PM PDT, ID: 24848677

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.1

Problem with MySQL Multi sub query

Asked by jaenglish in SQL Query Syntax, Data Manipulation, MySQL Server

Tags: 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.

View the Solution FREE for 30 Days

         
           
           
             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
)
           
         

Attachments:

database-files.zip (2 KB) (File Type Details)

SQL dump of tables used in the query

20090824-EE-VQP-74 - Hierarchy / EE_QW_3_20080625

1. pcelba22,870
2. mark_wills9,000
3. rrjegan178,200
4. mrjoltcola7,314
5. ushastry7,000
6. dportas6,050
7. sujith806,000
8. ozo5,500
9. BillBach5,400
10. mwvisa15,000
11. capricorn14,500
12. angelIII4,250
12. snoyes_jw4,250
12. earthman24,250
15. markgeer4,200
15. rockiroads4,200
17. sdstuber4,168
18. JestersGrind4,000
18. Zberteoc4,000
18. Sharath_...4,000
18. saurabh7264,000
22. LSMCons...3,700
23. schwertner3,500
23. momi_sa...3,500
25. ralmada3,200

1. angelIII28,450
2. pcelba22,870
3. mark_wills13,244
4. BillBach11,400
5. sujith8011,100
6. chapmandew10,500
7. dqmq10,230
8. sdstuber10,118
9. mwvisa19,900
10. dportas9,400
11. saurabh7269,250
12. ushastry9,000
13. grant3008,500
14. rrjegan178,200
15. BrandonG...7,800
16. mrjoltcola7,314
17. LSMCons...7,004
18. Sharath_...7,000
19. capricorn16,600
19. rockiroads6,600
21. momi_sa...6,500
22. ozo6,100
23. earthman25,600
24. Kdo4,700
25. jimpen4,625

Problem with MySQL Multi sub query

Asked by jaenglish in SQL Query Syntax, Data Manipulation, MySQL Server

Tags: mysql, multi-sub query

About this solution