sql server mseq: Doing multiple joins on one table -- sql server mseq

You're in the

sql server mseq

group:

Doing multiple joins on one table

Yaniv Danan
9/2/2006 10:26:02 PM

sql server mseq:

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.

Re: Doing multiple joins on one table

Arnie Rowland
9/3/2006 2:20:48 PM

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

[quoted text, click to view]

Re: Doing multiple joins on one table

Hugo Kornelis
9/3/2006 9:33:27 PM

[quoted text, click to view]
(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:

[quoted text, click to view]

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;

--

Re: Doing multiple joins on one table

Yaniv Danan
9/4/2006 12:00:00 AM

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

[quoted text, click to view]

Re: Doing multiple joins on one table

Hugo Kornelis
9/5/2006 12:45:49 AM

[quoted text, click to view]

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 www.aspfaq.com/5006.

--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.

View Other Months
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn