What is wrong with sql

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

Change these
series.*
to this
*
Do it for the other one you have too.

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.

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.

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) 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.