Help phrasing a JOIN Query ?

Discussion in 'MySQL' started by John Kjøller, Nov 23, 2015.

  1. #1
    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
     
    John Kjøller, Nov 23, 2015 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,803
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #2
    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):
     
    sarahk, Nov 23, 2015 IP