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 5 of 5
  1. #1
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts

    What is wrong with sql

    Hi

    what is wrong with this sql

    Code:
    SELECT series.* FROM series LEFT JOIN (select rating.* from rating WHERE rating.USER_USERID=51) ON series.SERIESID=rating.SERIES_SERIESID WHERE series.SERIESID=1
    the result should return records of Series with NULL rating if the rating table does not have the matching series_seriesid
    flying dagger

  • #2
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Change these
    Code:
    series.*
    to this
    Code:
    *
    Do it for the other one you have too.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    No reason to use an inner SELECT here.

    Just need to make sure that you code the ON condition correctly!

    Code:
    SELECT explicit, list, of, fields 
    FROM series LEFT JOIN rating 
    ON ( rating.USER_USERID=51 AND series.SERIESID=rating.SERIES_SERIESID )
    WHERE series.SERIESID=1
    And I *DISAGREE* with Aerospace. You should avoid doing SELECT * like the plague! Always only SELECT the actual fields you need. There's a LOT of overhead involved in using SELECT * in all too many circumstances.

  • #4
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Well, what _Aerospace_Eng_ was getting at, I believe, is the fact that this statement is at least redundant if not problematic in and of itself:
    Code:
    SELECT series.* FROM series
    You should simply change the series.* to just *. Same thing with ratings.*. Another problem comes into play when you have two tables with repeated field names using SELECT ALL ('*').

    That's not the fix to the statement, but you get the idea.


    Yes, the SELECT ALL statement should be avoided, absolutely.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Code:
    Well, what _Aerospace_Eng_ was getting at, I believe, is the fact that this statement is at least redundant if not problematic in and of itself:
    Code:
    
    SELECT series.* FROM series
    
    You should simply change the series.* to just *
    WHY?

    There is NO DIFFERENCE AT ALL between
    Code:
    SELECT series.* FROM series
    and
    Code:
    SELECT * FROM series
    None. Not one iota.

    There *IS* a difference if you were using mutiple tables:
    Code:
    SELECT series.* FROM series LEFT JOIN rating ...
    Indeed, that would get ONLY the fields from the series table. Wheras
    Code:
    SELECT * FROM series LEFT JOIN rating ...
    would be the same as doing
    Code:
    SELECT series.*, rating.* FROM series LEFT JOIN rating ...
    Granted that using * is bad, but there is truly ZERO difference between
    Code:
    SELECT series.* FROM series LEFT JOIN (
           select rating.* from rating WHERE rating.USER_USERID=51) 
           ON series.SERIESID=rating.SERIES_SERIESID 
    WHERE series.SERIESID=1
    and the same code using just * in place of series.* and rating.*

    The query is utterly bogus from the get-go because he LEFT JOINs to a sub-SELECT but then his ON clause refers to the ENTIRE rating table!

    If he really wanted to use a sub-SELECT, he should have coded:
    Code:
    SELECT series.* FROM series LEFT JOIN (
           select rating.* from rating WHERE rating.USER_USERID=51) AS XXX
           ON series.SERIESID=XXX.SERIES_SERIESID 
    WHERE series.SERIESID=1
    But of course it's much better to just do the LEFT JOIN as I showed it.


  •  

    Posting Permissions

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