What is wrong with sql

[xiaodao]

Hi

what is wrong with this sql

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

[_Aerospace_Eng_]

Change these

series.*

to this

*

Do it for the other one you have too.

[Old Pedant]

No reason to use an inner SELECT here.

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

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.

[bdl]

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:

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.

[Old Pedant]

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

SELECT series.* FROM series

and

SELECT * FROM series

None. Not one iota.

There *IS* a difference if you were using mutiple tables:

SELECT series.* FROM series LEFT JOIN rating ...

Indeed, that would get ONLY the fields from the series table. Wheras

SELECT * FROM series LEFT JOIN rating ...

would be the same as doing

SELECT series.*, rating.* FROM series LEFT JOIN rating ...

Granted that using * is bad, but there is truly ZERO difference between

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:

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.