Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: Joins between 3 tables
04-05-2013, 07:50 PM #1
- Join Date
- Mar 2012
- Ontario, Canada
- 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:
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
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
04-05-2013, 08:59 PM #2
- Join Date
- Dec 2005
- Thanked 78 Times in 78 Posts
This is normal (as per your command LEFT). You have many images per one movie, hence you getCode:LEFT JOIN mov_images ON mov_images.mov_id=mov_format.mov_id
hence your duplicates
You dont get duplicates where you have no images or only one image:
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.