Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Mar 2012
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Joins between 3 tables

    I am trying to join three tables to get a list of items to display on the screen, when I do the count query of all titles with a specific format, I get 1,312 records which is correct:

    Code:
    SELECT count(*) FROM mov_titles INNER JOIN mov_format ON mov_titles.mov_id=mov_format.mov_id WHERE mov_format.mov_format=1 OR mov_format.mov_format=7 OR mov_format.mov_format=10 OR mov_format.mov_format=14 OR mov_format.mov_format=17 OR mov_format.mov_format=19 OR mov_format.mov_format=23
    So I have one table that holds the titles and title ID; The second table holds the formats with title ID; the Third table holds the images with title ID.

    I want all the titles with the formats above but I don't have images for all of the titles. I have tried INNER and OUTER JOINs but can't seem to get the same number. I end up duplicating some of the titles with other formats. This query gives me 1,379 records

    Code:
    SELECT mov_titles.mov_original, mov_images.image_2ds FROM mov_format INNER JOIN mov_titles ON mov_titles.mov_id=mov_format.mov_id LEFT JOIN mov_images ON mov_images.mov_id=mov_format.mov_id WHERE mov_format.mov_format=1 OR mov_format.mov_format=7 OR mov_format.mov_format=10 OR mov_format.mov_format=14 OR mov_format.mov_format=17 OR mov_format.mov_format=19 OR mov_format.mov_format=23
    What am I missing?

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,982
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Code:
    LEFT JOIN mov_images ON mov_images.mov_id=mov_format.mov_id
    This is normal (as per your command LEFT). You have many images per one movie, hence you get

    mov1 image1
    mov1 image2

    hence your duplicates

    You dont get duplicates where you have no images or only one image:

    mov2 null
    mov3 image25

    How you would handle that duplicates to display them, you have to decide on application side.

    See forinstance this:http://www.agrozoo.net/jsp/PlantingCalendar.jsp?l2=en
    You have one row per one date displayed, but query result has (could have) many rows per one date. Rolling them all nicely into one row is application side job.
    Last edited by BubikolRamios; 04-05-2013 at 09:08 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •