another join problem :(

[LJackson]

these inner/outer/left joins are causing me many problems

i am trying to creat a query which will:

1/ pull out ll the rows from a table (has 40000+ rows)
2/ then join the price table BUT only return 1 row from the prices table where the price is the cheapest

so this query

SELECT *
FROM `tbl_dvds` AS dvd
INNER JOIN (
tbl_prices AS price
) ON price.prodID = dvd.filmID

returns 100,000+ rows because it returns a new row for each price found for every product

so at the moment say the above query is returning:

prod1 -> ... -> 3.99
prod1 -> ... -> 5.99
prod1 -> ... -> 1.99
prod2 -> ... -> 6.99
prod3 -> ... -> 16.99
prod3 -> ... -> 16.99

... show more fields

i want it to return this
prod1 -> ... -> 1.99
prod2 -> ... -> 6.99
prod3 -> ... -> 16.99

is this possible?
many thanks

[LJackson]

tried this

SELECT *
FROM `tbl_dvds` AS dvd
INNER JOIN (

SELECT *
FROM tbl_prices
ORDER BY prodPrice ASC
LIMIT 0 , 1
) AS price ON price.prodID = dvd.filmID
LIMIT 0 , 30

which returned 1 row but says:
Showing rows 0 - 29 (275,592 total, Query took 0.2366 sec) in my php myadmin

[Old Pedant]

Man oh man, are you working too hard.

SELECT dvd.field1, dvd.field2, dvd.field3, MIN(price.prodPrice) AS lowestPrice
FROM `tbl_dvds` AS dvd, tbl_prices AS price
WHERE price.prodID = dvd.filmID
GROUP BY dvd.field1, dvd.field2, dvd.field3
ORDER BY any-fields-you-want

You must give the list of fields so that you can repeat the list in the GROUP BY.

Yes, of course you can use INNER JOIN syntax. The semantics are 100% the same as is the performance.

SELECT dvd.field1, dvd.field2, dvd.field3, MIN(price.prodPrice) AS lowestPrice
FROM `tbl_dvds` AS dvd INNER JOIN tbl_prices AS price
ON price.prodID = dvd.filmID
GROUP BY dvd.field1, dvd.field2, dvd.field3
ORDER BY any-fields-you-want

[LJackson]

wow i was over doing it! dunno where i would be without you mate!

appreciate it!!!!

[LJackson]

heres another one

i am trying to pull data from 3 tables
tbl_dvds
tbl_popularity
tbl_format

but the problem is that the query only returns items if the count of popularity is 1 or greater and doesnt return any results which has 0 count???

here is my query

SELECT dvd.filmID, dvd.filmName, dvd.filmThumbIMG, dvd.filmBinding, dvd.filmReleaseDate, filmAgeRating, filmTrailer, filmRRP, format.prodID, format.formatID, COUNT( pop.prodID ) AS popularity
                FROM tbl_dvds AS dvd, tbl_popularity AS pop, tbl_product_format AS format
                WHERE pop.prodID = dvd.filmID && format.prodID = dvd.filmID
                GROUP BY dvd.filmID, dvd.filmName, dvd.filmThumbIMG, dvd.filmBinding, dvd.filmReleaseDate, filmAgeRating, filmTrailer, filmRRP
                ORDER BY filmName ASC 

any ideas?
thanks mate

[Old Pedant]

SELECT dvd.filmID, dvd.filmName, dvd.filmThumbIMG, dvd.filmBinding, dvd.filmReleaseDate, 
       filmAgeRating, filmTrailer, filmRRP, format.prodID, format.formatID, 
       COUNT( pop.prodID ) AS popularity
FROM tbl_dvds AS dvd 
INNER JOIN tbl_product_format AS formatON format.prodID = dvd.filmID
LEFT JOIN  tbl_popularity AS pop ON pop.prodID = dvd.filmID
GROUP BY dvd.filmID, dvd.filmName, dvd.filmThumbIMG, dvd.filmBinding, dvd.filmReleaseDate, 
         filmAgeRating, filmTrailer, filmRRP, format.prodID, format.formatID
ORDER BY filmName ASC

I think it's time you studied up on SQL and learned about OUTER joins, no?

Note that you need to GROUP BY *all* non-aggregate fields. You had omitted format.prodID, and format.formatID from your GROUP BY.

[LJackson]

[code]
I think it's time you studied up on SQL and learned about OUTER joins, no?

totally agree with you there! they're such a major part of website development and i need to learn them inside and out asap.

thanks again for your help!!!!!