Web Analytics Made Easy -
StatCounter What is wrong with sql - CodingForum

Announcement

Collapse
No announcement yet.

What is wrong with sql

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new 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
    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!||||

    Comment


    • #3
      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.
      Be yourself. No one else is as qualified.

      Comment


      • #4
        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.

        Comment


        • #5
          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) [B][COLOR="Red"]AS XXX[/COLOR][/B]
                 ON series.SERIESID=[B][COLOR="Red"]XXX.[/COLOR][/B]SERIES_SERIESID 
          WHERE series.SERIESID=1
          But of course it's much better to just do the LEFT JOIN as I showed it.
          Be yourself. No one else is as qualified.

          Comment

          Working...
          X