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

i have this query
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
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
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

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/showthread.php?p=818192#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.