...

View Full Version : another join problem :(



LJackson
08-29-2011, 11:25 PM
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
08-29-2011, 11:29 PM
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
08-30-2011, 02:00 AM
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
08-30-2011, 09:30 AM
wow i was over doing it! dunno where i would be without you mate!

appreciate it!!!!

LJackson
08-30-2011, 07:22 PM
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
08-30-2011, 08:08 PM
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
08-30-2011, 08:28 PM
[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!!!!!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum