...

View Full Version : Joins between 3 tables



chellert
04-05-2013, 07:50 PM
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:



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



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?

BubikolRamios
04-05-2013, 08:59 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum