need help to modify my query, to set field names as 'something'

[LJackson]

i have this query

PHP Code:

SELECT * , count( * ) AS howmany
        FROM tbl_popularity AS pop
        LEFT JOIN (
        tbl_dvds AS dvd
        ) ON pop.prodID = dvd.filmID
        WHERE dvd.filmBinding = '$cSect' && pop.prodID <>0 && pop.dateStamp > DATE_SUB( curdate( ) , INTERVAL 30
        DAY )
        GROUP BY `prodID`
        ORDER BY howmany DESC
        LIMIT 0 , 6 


and on the join i need to select certain fields like i have in this query

PHP Code:

SELECT filmID as id, filmName as name, filmThumbIMG as img, filmThumbHeight as height, filmThumbWidth as width, filmRRP as rrp, filmBinding as binding 
        FROM tbl_dvds 


i tried this

PHP Code:

SELECT * , count( * ) AS howmany
        FROM tbl_popularity AS pop
        LEFT JOIN (
        SELECT filmID as id, filmName as name, filmThumbIMG as img, filmThumbHeight as height, filmThumbWidth as width, filmRRP as rrp, filmBinding as binding FROM tbl_dvds AS dvd
        ) ON pop.prodID = dvd.filmID
        WHERE dvd.filmBinding = 'DVD' && pop.prodID <>0 && pop.dateStamp > DATE_SUB( curdate( ) , INTERVAL 30
        DAY )
        GROUP BY `prodID`
        ORDER BY howmany DESC
        LIMIT 0 , 6 


but it says Every derived table must have its own alias?

can i do that using the left join or do i need to use union? and will union enable me to product the same outputs as my first query?

thanks
Luke

[Old Pedant]

It's just saying you need "... AS anyname " after each of your inner SELECTs.

You have your AS in the wrong place: As written, you are aliasing tbl_dvds to dvd. You need to alias the entire SELECT.

*PROBABLY* this:

SELECT * , count( * ) AS howmany
FROM tbl_popularity AS pop
LEFT JOIN (
        SELECT filmID as id, filmName as name, filmThumbIMG as img, filmThumbHeight as height, 
               filmThumbWidth as width, filmRRP as rrp, filmBinding as binding 
        FROM tbl_dvds ) AS dvd
ON pop.prodID = dvd.filmID AND dvd.filmBinding = 'DVD' 
WHERE pop.prodID <> 0 
AND pop.dateStamp > DATE_SUB( curdate( ) , INTERVAL 30  DAY )
GROUP BY `prodID`
ORDER BY howmany DESC
LIMIT 0 , 6

Notice that I moved your dvd.filmBinding = 'DVD' to the ON clause. If you put it in the WHERE clause, you just converted your LEFT JOIN into an INNER JOIN.

Didn't I show you that before? If not, look here:
http://www.codingforums.com/showthre...192#post818192

Also, I don't trust/like your GROUP BY. Normally, you should GROUP BY *every single field* in your SELECT except the aggregate fields (aggregates are the functions such as COUNT, AVG, SUM, etc. that work on a group of records). If you don't do that, you can't guarantee that your aggregate values actually go with the non-aggregate fields. Almost alone among DBs, MySQL allows you to omit fields from the GROUP BY, but then it gives oddball results.

n.b.: Rather than even having that one condition in the JOIN, why not put it in the inner SELECT, thus:

SELECT * , count( * ) AS howmany
FROM tbl_popularity AS pop
LEFT JOIN (
        SELECT filmID as id, filmName as name, filmThumbIMG as img, filmThumbHeight as height, 
               filmThumbWidth as width, filmRRP as rrp, filmBinding as binding 
        FROM tbl_dvds 
        WHERE filmBinding = 'DVD' ) AS dvd
ON pop.prodID = dvd.filmID  
WHERE pop.prodID <> 0 
AND pop.dateStamp > DATE_SUB( curdate( ) , INTERVAL 30  DAY )
GROUP BY `prodID`
ORDER BY howmany DESC
LIMIT 0 , 6

That's more likely to be more efficient.