I am not very experienced in MySQL - so there is a good chance I may be asking for a simple and basic advice - in that case, I apologize: I am building a "search" for specific classical music-pieces. I have my main table with aprox 8000 classical pieces. And then I have a separate table with what is called "movements". ("movements" are basically like chapters in a book. Different parts of the piece, with separate names) I call the pieces for "Works". And here is my problem. The Works are all unique and each have their own id. The movements also have unique id's - but they ofcourse relate to the "Works" database - by having a "Works_id" field. Which means that there will be anywhere between 1 and 20 "movements" with unique id's - BUT with the same work_id. I am trying to phrase the query so that when I type a searchphrase in my search field - the query should check if the search phrase is present in any of the "names" fields in both "Works" and "Movements" tables. In the "Works" table I have "first_name", "last_name" and "Full title". That part I got. But then I also need to check the names of the "movements". If the search phrase does not exist in the "Works" tables field - then perhaps I am searching for the name of one of the movements. I dont need to display the movements. I need the "work". - But it is possible that I am sarching for the name of one of the movements to this work. Here is my best bid, searhing for "dom": SELECT `works`.`full_title`, `works`.`id` FROM `works` JOIN `movements` ON `movements`.`works_id` = `works`.`id` WHERE `works`.`full_title` LIKE '%dom%' OR `movements`.`name` LIKE '%dom%' Code (markup): But this results in many duplicate "works"."id" s. (because of the movements, I guess) How do I avoid the duplicate works.ids ? Can you help? Thanks John
You've done really well given your level of understanding. You just need to "group" the result like this SELECT `works`.`full_title`, `works`.`id` FROM `works` JOIN `movements` ON `movements`.`works_id` = `works`.`id` WHERE `works`.`full_title` LIKE '%dom%' OR `movements`.`name` LIKE '%dom%' GROUP BY `works`.`id` ORDER BY `works`.`full_title` Code (markup):