yaniv danan

I have those Tables:

CREATE TABLE GameYellows(

id INT NOT NULL AUTO_INCREMENT,

AddedDate DATETIME NOT NULL,

Player_ID INT NOT NULL,

)

CREATE TABLE GameReds(

id INT NOT NULL AUTO_INCREMENT,

AddedDate DATETIME NOT NULL,

Player_ID INT NOT NULL,

)

CREATE TABLE GameSubstitutes(

id INT NOT NULL AUTO_INCREMENT,

OutPlayer_ID INT NOT NULL,

InPlayer_ID INT NOT NULL,

)

CREATE TABLE Players(

id INT NOT NULL AUTO_INCREMENT,

FullName VARCHAR(250) NOT NULL,

)

What i need is actually to get a table of statistics for each player in the
players table.

meaning i need to return a table like this:

id | yellows | reds | substitutes_out | substitutes_in

---------------------------------------------------------------------

1 | 2 | 1 | 5 | 2

2 | 4 | 0 | 0 | 3

3 | 1 | 2 | 0 | 7

please help me solve this one, having hard time using join.

thanks, Yaniv.

hugo kornelis

(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:


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

arnie rowland

Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to prevent folks from
wasting time on already answered questions, please:

Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup), and
indicate that you've already posted elsewhere.

If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.


--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

yaniv danan

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

hugo kornelis

Hi Yaniv,

There's neither a max nor a count() in my solution, so I have no idea
what "it" refers to, not how or why it "doesn't work".

To get better help, it's advisable to post table structure in the form
of CREATE TABLE statements (omitting irrelevant columns, but including
all constraints, properties, and indexes), sample data in the form of
INSERT statements (not housands of rows, but just a few rows of data,
carefully chosen to illustrate the problem), and expected results based
on the sample data provided.

Also, check out http://www.aspfaq.com/5006.

--
Hugo Kornelis, SQL Server MVP