Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts

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

    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 <>&& pop.dateStamp DATE_SUBcurdate( ) , INTERVAL 30
            DAY 
    )
            
    GROUP BY `prodID`
            
    ORDER BY howmany DESC
            LIMIT 0 

    and on the join i need to select certain fields like i have in this query
    PHP Code:
    SELECT filmID as idfilmName as namefilmThumbIMG as imgfilmThumbHeight as heightfilmThumbWidth as widthfilmRRP as rrpfilmBinding as binding 
            FROM tbl_dvds 
    i tried this
    PHP Code:
    SELECT * , count( * ) AS howmany
            FROM tbl_popularity 
    AS pop
            LEFT JOIN 
    (
            
    SELECT filmID as idfilmName as namefilmThumbIMG as imgfilmThumbHeight as heightfilmThumbWidth as widthfilmRRP as rrpfilmBinding as binding FROM tbl_dvds AS dvd
            
    ON pop.prodID dvd.filmID
            WHERE dvd
    .filmBinding 'DVD' && pop.prodID <>&& pop.dateStamp DATE_SUBcurdate( ) , INTERVAL 30
            DAY 
    )
            
    GROUP BY `prodID`
            
    ORDER BY howmany DESC
            LIMIT 0 

    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

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,191
    Thanks
    80
    Thanked 4,562 Times in 4,526 Posts
    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:
    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 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:
    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 
            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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •