1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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,494
    Likes Received:
    4,457
    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