Re: Doing multiple joins on one table

it doesnt work , it returns always the same max count() for all the rows



"Hugo Kornelis" <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:97bmf2l47irb9kbtki4k4i0f07sfl1pij5@xxxxxxxxxx
On Sat, 2 Sep 2006 22:26:02 +0200, Yaniv Danan wrote:

I have those Tables:

CREATE TABLE GameYellows(

id INT NOT NULL AUTO_INCREMENT,
(snip)

Hi Yaniv,

AUTO_INCREMENT is not a recognised keyword in SQL Server. If you're
using a different DBMS, you might try finding a group for that DBMS in
order to get better replies. All DBMS's have their specific quirks, so
what works goood in one might perform awfully or not work at all in
another.

As to your problem:

What i need is actually to get a table of statistics for each player in
the
players table.
(snip)
please help me solve this one, having hard time using join.

Try it like this:

SELECT p.id, y.id AS yellows, r.id AS reds,
so.id AS subsititutes_out, si.id AS subsititutes_in
FROM Players AS p
LEFT OUTER JOIN GameYellow AS y ON y.Player_ID = p.id
LEFT OUTER JOIN GameReds AS r ON r.Player_ID = p.id
LEFT OUTER JOIN GameSubsititues AS so ON so.OutPlayer_ID = p.id
LEFT OUTER JOIN GameSubsititues AS si ON si.OutPlayer_ID = p.id
ORDER BY p.id;

--
Hugo Kornelis, SQL Server MVP


.

Relevant Pages