Join problem

[Lasse Edsvik]

Hello

I have 2 tables

Players

Player .......
A
B
C
D

Scores

Player Year Points
A 2002 3
B 2002 5
A 2003 4
B 2003 1


I would like to list all players and order by points desc where year=2002,
but i cant get it to list all players

I would like to have the following result


Player Year Points
B 2002 5
A 2002 3
C NULL NULL
D NULL NULL


how's that done?

TIA
/Lasse

[Harag]

SELECT P.Player, S.Year, S.Points
FROM Players P LEFT OUTER JOIN
Scores S ON P.Player = S.Player
WHERE S.Year = 2002
ORDER BY P.Player


Something along those lines, its always best to have some DDL and
example data so people who help can copy & paste into QA and test it.
Also letting us know what DB your using will help as well, Access,
MSSQL etc.

HTH

Al

On Wed, 7 Jan 2004 10:01:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
wrote:

>Hello
>
>I have 2 tables
>
>Players
>
>Player .......
>A
>B
>C
>D
>
>Scores
>
>Player Year Points
>A 2002 3
>B 2002 5
>A 2003 4
>B 2003 1
>
>
>I would like to list all players and order by points desc where year=2002,
>but i cant get it to list all players
>
>I would like to have the following result
>
>
>Player Year Points
>B 2002 5
>A 2002 3
>C NULL NULL
>D NULL NULL
>
>
>how's that done?
>
>TIA
>/Lasse
>

[Harag]

Hi again :)

I knocked up the following which gives the results you want: this is
for MSSQL 2k.

declare @Players TABLE(Player varchar(10))
declare @Scores TABLE(Player varchar(10), [Year] varchar(4), Points
int)

insert into @players values('A')
insert into @players values('B')
insert into @players values('C')
insert into @players values('D')

insert into @Scores values('A', '2002', 3)
insert into @Scores values('B', '2002', 6)
insert into @Scores values('A', '2003', 4)
insert into @Scores values('B', '2003', 5)

--SELECT * FROM @Players
--SELECT * FROM @Scores

SELECT P.Player, S.[Year], S.Points
FROM @Players P LEFT OUTER JOIN
@Scores S ON P.Player = S.Player AND S.[Year] = '2002'
ORDER BY P.Player


HTH

Al.

On Wed, 7 Jan 2004 10:01:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
wrote:

>Hello
>
>I have 2 tables
>
>Players
>
>Player .......
>A
>B
>C
>D
>
>Scores
>
>Player Year Points
>A 2002 3
>B 2002 5
>A 2003 4
>B 2003 1
>
>
>I would like to list all players and order by points desc where year=2002,
>but i cant get it to list all players
>
>I would like to have the following result
>
>
>Player Year Points
>B 2002 5
>A 2002 3
>C NULL NULL
>D NULL NULL
>
>
>how's that done?
>
>TIA
>/Lasse
>

[Lasse Edsvik]

Harag,

Access........ and im going nuts, it makes no sense :/

it only shows A and B with that query, same with RIGHT OUTER JOIN......
would be neat if things worked as in sql server :/

any ideas?

/Lasse


"Harag" <harag@REMOVETHESECAPITALSsofthome.net> wrote in message
news:hdsnvv4bm8uqk9288n8dmpe6ulvqvanegk@4ax.com...

>
>
> SELECT P.Player, S.Year, S.Points
> FROM Players P LEFT OUTER JOIN
> Scores S ON P.Player = S.Player
> WHERE S.Year = 2002
> ORDER BY P.Player
>
>
> Something along those lines, its always best to have some DDL and
> example data so people who help can copy & paste into QA and test it.
> Also letting us know what DB your using will help as well, Access,
> MSSQL etc.
>
> HTH
>
> Al
>
> On Wed, 7 Jan 2004 10:01:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
> wrote:
>

> >Hello
> >
> >I have 2 tables
> >
> >Players
> >
> >Player .......
> >A
> >B
> >C
> >D
> >
> >Scores
> >
> >Player Year Points
> >A 2002 3
> >B 2002 5
> >A 2003 4
> >B 2003 1
> >
> >
> >I would like to list all players and order by points desc where

year=2002,

> >but i cant get it to list all players
> >
> >I would like to have the following result
> >
> >
> >Player Year Points
> >B 2002 5
> >A 2002 3
> >C NULL NULL
> >D NULL NULL
> >
> >
> >how's that done?
> >
> >TIA
> >/Lasse
> >

>

[Jeff Cochran]

On Wed, 7 Jan 2004 10:01:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
wrote:

>Hello
>
>I have 2 tables
>
>Players
>
>Player .......
>A
>B
>C
>D
>
>Scores
>
>Player Year Points
>A 2002 3
>B 2002 5
>A 2003 4
>B 2003 1
>
>
>I would like to list all players and order by points desc where year=2002,
>but i cant get it to list all players
>
>I would like to have the following result
>
>
>Player Year Points
>B 2002 5
>A 2002 3
>C NULL NULL
>D NULL NULL
>
>
>how's that done?

From your table layout, it's all in a single table, unless "Player"
isactually an ID number and you have other fields in the Players table
you need displayed. At any rate, you can use a SELECT and WHERE with
table identifiers to do this, something like:

SELECT Players.Player, Scores.Year, Scores.Points WHERE Scores.Year =
"2002" ORDER BY Scores.Points DESC

Jeff

[Harag]

see my other reply I noticed the mistake after I posted it...

Al.

On Wed, 7 Jan 2004 13:12:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
wrote:

>Harag,
>
>Access........ and im going nuts, it makes no sense :/
>
>it only shows A and B with that query, same with RIGHT OUTER JOIN......
>would be neat if things worked as in sql server :/
>
>any ideas?
>
>/Lasse
>
>
>"Harag" <harag@REMOVETHESECAPITALSsofthome.net> wrote in message
>news:hdsnvv4bm8uqk9288n8dmpe6ulvqvanegk@4ax.com.. .

>>
>>
>> SELECT P.Player, S.Year, S.Points
>> FROM Players P LEFT OUTER JOIN
>> Scores S ON P.Player = S.Player
>> WHERE S.Year = 2002
>> ORDER BY P.Player
>>
>>
>> Something along those lines, its always best to have some DDL and
>> example data so people who help can copy & paste into QA and test it.
>> Also letting us know what DB your using will help as well, Access,
>> MSSQL etc.
>>
>> HTH
>>
>> Al
>>
>> On Wed, 7 Jan 2004 10:01:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
>> wrote:
>>

>> >Hello
>> >
>> >I have 2 tables
>> >
>> >Players
>> >
>> >Player .......
>> >A
>> >B
>> >C
>> >D
>> >
>> >Scores
>> >
>> >Player Year Points
>> >A 2002 3
>> >B 2002 5
>> >A 2003 4
>> >B 2003 1
>> >
>> >
>> >I would like to list all players and order by points desc where

>year=2002,

>> >but i cant get it to list all players
>> >
>> >I would like to have the following result
>> >
>> >
>> >Player Year Points
>> >B 2002 5
>> >A 2002 3
>> >C NULL NULL
>> >D NULL NULL
>> >
>> >
>> >how's that done?
>> >
>> >TIA
>> >/Lasse
>> >

>>

>

[Jeff Cochran]

On Wed, 07 Jan 2004 11:51:13 +0000, Harag
<harag@REMOVETHESECAPITALSsofthome.net> wrote:

>SELECT P.Player, S.Year, S.Points
> FROM Players P LEFT OUTER JOIN
> Scores S ON P.Player = S.Player
> WHERE S.Year = 2002
> ORDER BY P.Player

Given the WHERE criteria, which I *assume* is unique because a player
couldn't get two different score totals for a single year, I don't
think you even need a join. :)

Jeff

>Something along those lines, its always best to have some DDL and
>example data so people who help can copy & paste into QA and test it.
>Also letting us know what DB your using will help as well, Access,
>MSSQL etc.
>
>HTH
>
>Al
>
>On Wed, 7 Jan 2004 10:01:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
>wrote:
>

>>Hello
>>
>>I have 2 tables
>>
>>Players
>>
>>Player .......
>>A
>>B
>>C
>>D
>>
>>Scores
>>
>>Player Year Points
>>A 2002 3
>>B 2002 5
>>A 2003 4
>>B 2003 1
>>
>>
>>I would like to list all players and order by points desc where year=2002,
>>but i cant get it to list all players
>>
>>I would like to have the following result
>>
>>
>>Player Year Points
>>B 2002 5
>>A 2002 3
>>C NULL NULL
>>D NULL NULL
>>
>>
>>how's that done?
>>
>>TIA
>>/Lasse
>>

[Harag]

he needs to Join both tables together with a LEFT OUTER JOIN, he wants
all the players (A, B, C, D) in the result even if they didn't score
any points in which case they should show up null. The LEFT OUTER JOIN
means that you want ALL the rows from the left table no matter what
the criteria is for the ON statement

>>Player Year Points
>>B 2002 5
>>A 2002 3
>>C NULL NULL
>>D NULL NULL

I'm asuming that the Player table will have more info in the table
than just an ID. like Player First & Last names, DOB, etc


Al


On Wed, 07 Jan 2004 12:22:08 GMT, [email]jcochran.nospam@naplesgov.com[/email] (Jeff
Cochran) wrote:

>On Wed, 7 Jan 2004 10:01:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
>wrote:
>

>>Hello
>>
>>I have 2 tables
>>
>>Players
>>
>>Player .......
>>A
>>B
>>C
>>D
>>
>>Scores
>>
>>Player Year Points
>>A 2002 3
>>B 2002 5
>>A 2003 4
>>B 2003 1
>>
>>
>>I would like to list all players and order by points desc where year=2002,
>>but i cant get it to list all players
>>
>>I would like to have the following result
>>
>>
>>Player Year Points
>>B 2002 5
>>A 2002 3
>>C NULL NULL
>>D NULL NULL
>>
>>
>>how's that done?

>
>From your table layout, it's all in a single table, unless "Player"
>isactually an ID number and you have other fields in the Players table
>you need displayed. At any rate, you can use a SELECT and WHERE with
>table identifiers to do this, something like:
>
>SELECT Players.Player, Scores.Year, Scores.Points WHERE Scores.Year =
>"2002" ORDER BY Scores.Points DESC
>
>Jeff

[Lasse Edsvik]

Harag,

yes, tried it..... and got "Join expression not supported" :((

man i hate access.......

/Lasse


"Harag" <harag@REMOVETHESECAPITALSsofthome.net> wrote in message
news:rfunvvkpetqjs8jpoe3r3q4e6ijmmt0btj@4ax.com...

> see my other reply I noticed the mistake after I posted it...
>
> Al.
>
> On Wed, 7 Jan 2004 13:12:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
> wrote:
>

> >Harag,
> >
> >Access........ and im going nuts, it makes no sense :/
> >
> >it only shows A and B with that query, same with RIGHT OUTER JOIN......
> >would be neat if things worked as in sql server :/
> >
> >any ideas?
> >
> >/Lasse
> >
> >
> >"Harag" <harag@REMOVETHESECAPITALSsofthome.net> wrote in message
> >news:hdsnvv4bm8uqk9288n8dmpe6ulvqvanegk@4ax.com.. .

> >>
> >>
> >> SELECT P.Player, S.Year, S.Points
> >> FROM Players P LEFT OUTER JOIN
> >> Scores S ON P.Player = S.Player
> >> WHERE S.Year = 2002
> >> ORDER BY P.Player
> >>
> >>
> >> Something along those lines, its always best to have some DDL and
> >> example data so people who help can copy & paste into QA and test it.
> >> Also letting us know what DB your using will help as well, Access,
> >> MSSQL etc.
> >>
> >> HTH
> >>
> >> Al
> >>
> >> On Wed, 7 Jan 2004 10:01:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
> >> wrote:
> >>
> >> >Hello
> >> >
> >> >I have 2 tables
> >> >
> >> >Players
> >> >
> >> >Player .......
> >> >A
> >> >B
> >> >C
> >> >D
> >> >
> >> >Scores
> >> >
> >> >Player Year Points
> >> >A 2002 3
> >> >B 2002 5
> >> >A 2003 4
> >> >B 2003 1
> >> >
> >> >
> >> >I would like to list all players and order by points desc where

> >year=2002,

> >> >but i cant get it to list all players
> >> >
> >> >I would like to have the following result
> >> >
> >> >
> >> >Player Year Points
> >> >B 2002 5
> >> >A 2002 3
> >> >C NULL NULL
> >> >D NULL NULL
> >> >
> >> >
> >> >how's that done?
> >> >
> >> >TIA
> >> >/Lasse
> >> >
> >>

> >

>

[Harag]

I can't really help that much as I've never used Access I started with
MySQL then went onto MSSQL. Maybe a Guru might help.

Have you tried just "LEFT JOIN" - drop the word outer. does that help?


Al.


On Wed, 7 Jan 2004 13:34:49 +0100, "Lasse Edsvik" <lasse@nospam.com>
wrote:

>Harag,
>
>yes, tried it..... and got "Join expression not supported" :((
>
>man i hate access.......
>
>/Lasse
>
>
>"Harag" <harag@REMOVETHESECAPITALSsofthome.net> wrote in message
>news:rfunvvkpetqjs8jpoe3r3q4e6ijmmt0btj@4ax.com.. .

>> see my other reply I noticed the mistake after I posted it...
>>
>> Al.
>>
>> On Wed, 7 Jan 2004 13:12:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
>> wrote:
>>

>> >Harag,
>> >
>> >Access........ and im going nuts, it makes no sense :/
>> >
>> >it only shows A and B with that query, same with RIGHT OUTER JOIN......
>> >would be neat if things worked as in sql server :/
>> >
>> >any ideas?
>> >
>> >/Lasse
>> >
>> >
>> >"Harag" <harag@REMOVETHESECAPITALSsofthome.net> wrote in message
>> >news:hdsnvv4bm8uqk9288n8dmpe6ulvqvanegk@4ax.com.. .
>> >>
>> >>
>> >> SELECT P.Player, S.Year, S.Points
>> >> FROM Players P LEFT OUTER JOIN
>> >> Scores S ON P.Player = S.Player
>> >> WHERE S.Year = 2002
>> >> ORDER BY P.Player
>> >>
>> >>
>> >> Something along those lines, its always best to have some DDL and
>> >> example data so people who help can copy & paste into QA and test it.
>> >> Also letting us know what DB your using will help as well, Access,
>> >> MSSQL etc.
>> >>
>> >> HTH
>> >>
>> >> Al
>> >>
>> >> On Wed, 7 Jan 2004 10:01:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
>> >> wrote:
>> >>
>> >> >Hello
>> >> >
>> >> >I have 2 tables
>> >> >
>> >> >Players
>> >> >
>> >> >Player .......
>> >> >A
>> >> >B
>> >> >C
>> >> >D
>> >> >
>> >> >Scores
>> >> >
>> >> >Player Year Points
>> >> >A 2002 3
>> >> >B 2002 5
>> >> >A 2003 4
>> >> >B 2003 1
>> >> >
>> >> >
>> >> >I would like to list all players and order by points desc where
>> >year=2002,
>> >> >but i cant get it to list all players
>> >> >
>> >> >I would like to have the following result
>> >> >
>> >> >
>> >> >Player Year Points
>> >> >B 2002 5
>> >> >A 2002 3
>> >> >C NULL NULL
>> >> >D NULL NULL
>> >> >
>> >> >
>> >> >how's that done?
>> >> >
>> >> >TIA
>> >> >/Lasse
>> >> >
>> >>
>> >

>>

>

[Bob Barrows]

Lasse Edsvik wrote:

> Hello

Assuming Access (Please do not leave out this information!! It is crucial to
the solution!), you will need to use a subquery:

SELECT p.player, s.Year, s.Points
FROM Players p LEFT JOIN
(Select Player,[Year],Points FROM Scores
WHERE [Year]=2002) s
ON p.Player = s.Player
ORDER BY Points DESC,p.Player

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Lasse Edsvik]

Harag,

this is unreal, this "works"

SELECT players.player, year, points

FROM players LEFT JOIN scores ON scores.player= players.player

WHERE year=2002 or year is null

ORDER BY points DESC;



but if you change year to 2005 nothing shows........ :((

/Lasse





"Harag" <harag@REMOVETHESECAPITALSsofthome.net> wrote in message
news:c42ovv4rak2g22nos74kreofuk9l4ukfru@4ax.com...

>
>
> I can't really help that much as I've never used Access I started with
> MySQL then went onto MSSQL. Maybe a Guru might help.
>
> Have you tried just "LEFT JOIN" - drop the word outer. does that help?
>
>
> Al.
>
>
> On Wed, 7 Jan 2004 13:34:49 +0100, "Lasse Edsvik" <lasse@nospam.com>
> wrote:
>

> >Harag,
> >
> >yes, tried it..... and got "Join expression not supported" :((
> >
> >man i hate access.......
> >
> >/Lasse
> >
> >
> >"Harag" <harag@REMOVETHESECAPITALSsofthome.net> wrote in message
> >news:rfunvvkpetqjs8jpoe3r3q4e6ijmmt0btj@4ax.com.. .

> >> see my other reply I noticed the mistake after I posted it...
> >>
> >> Al.
> >>
> >> On Wed, 7 Jan 2004 13:12:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
> >> wrote:
> >>
> >> >Harag,
> >> >
> >> >Access........ and im going nuts, it makes no sense :/
> >> >
> >> >it only shows A and B with that query, same with RIGHT OUTER

JOIN......

> >> >would be neat if things worked as in sql server :/
> >> >
> >> >any ideas?
> >> >
> >> >/Lasse
> >> >
> >> >
> >> >"Harag" <harag@REMOVETHESECAPITALSsofthome.net> wrote in message
> >> >news:hdsnvv4bm8uqk9288n8dmpe6ulvqvanegk@4ax.com.. .
> >> >>
> >> >>
> >> >> SELECT P.Player, S.Year, S.Points
> >> >> FROM Players P LEFT OUTER JOIN
> >> >> Scores S ON P.Player = S.Player
> >> >> WHERE S.Year = 2002
> >> >> ORDER BY P.Player
> >> >>
> >> >>
> >> >> Something along those lines, its always best to have some DDL and
> >> >> example data so people who help can copy & paste into QA and test

it.

> >> >> Also letting us know what DB your using will help as well, Access,
> >> >> MSSQL etc.
> >> >>
> >> >> HTH
> >> >>
> >> >> Al
> >> >>
> >> >> On Wed, 7 Jan 2004 10:01:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
> >> >> wrote:
> >> >>
> >> >> >Hello
> >> >> >
> >> >> >I have 2 tables
> >> >> >
> >> >> >Players
> >> >> >
> >> >> >Player .......
> >> >> >A
> >> >> >B
> >> >> >C
> >> >> >D
> >> >> >
> >> >> >Scores
> >> >> >
> >> >> >Player Year Points
> >> >> >A 2002 3
> >> >> >B 2002 5
> >> >> >A 2003 4
> >> >> >B 2003 1
> >> >> >
> >> >> >
> >> >> >I would like to list all players and order by points desc where
> >> >year=2002,
> >> >> >but i cant get it to list all players
> >> >> >
> >> >> >I would like to have the following result
> >> >> >
> >> >> >
> >> >> >Player Year Points
> >> >> >B 2002 5
> >> >> >A 2002 3
> >> >> >C NULL NULL
> >> >> >D NULL NULL
> >> >> >
> >> >> >
> >> >> >how's that done?
> >> >> >
> >> >> >TIA
> >> >> >/Lasse
> >> >> >
> >> >>
> >> >
> >>

> >

>

[Harag]

Good ole Bob comes up with the answer :)

I'm glad I don't use access,

Al.


On Wed, 7 Jan 2004 08:53:11 -0500, "Bob Barrows"
<reb01501@NOyahoo.SPAMcom> wrote:

>Lasse Edsvik wrote:

>> Hello

>Assuming Access (Please do not leave out this information!! It is crucial to
>the solution!), you will need to use a subquery:
>
>SELECT p.player, s.Year, s.Points
>FROM Players p LEFT JOIN
>(Select Player,[Year],Points FROM Scores
>WHERE [Year]=2002) s
>ON p.Player = s.Player
>ORDER BY Points DESC,p.Player
>
>HTH,
>Bob Barrows

[Lasse Edsvik]

Found a neat solution :)

SELECT players.player, year, points
FROM players LEFT JOIN scores ON (scores.player= players.player AND
year=2002)
ORDER BY points DESC,players.player;

finally! was running out of hair on my head

/Lasse



"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:uE0m5vP1DHA.4032@tk2msftngp13.phx.gbl...

> Hello
>
> I have 2 tables
>
> Players
>
> Player .......
> A
> B
> C
> D
>
> Scores
>
> Player Year Points
> A 2002 3
> B 2002 5
> A 2003 4
> B 2003 1
>
>
> I would like to list all players and order by points desc where year=2002,
> but i cant get it to list all players
>
> I would like to have the following result
>
>
> Player Year Points
> B 2002 5
> A 2002 3
> C NULL NULL
> D NULL NULL
>
>
> how's that done?
>
> TIA
> /Lasse
>
>

[Bob Barrows]

Lasse Edsvik wrote:

> Found a neat solution :)
>
> SELECT players.player, year, points
> FROM players LEFT JOIN scores ON (scores.player= players.player AND
> year=2002)
> ORDER BY points DESC,players.player;
>
> finally! was running out of hair on my head
>

:-)

I had tried something similar to that:

SELECT players.player, year, points
FROM players LEFT JOIN scores ON scores.player= players.player AND
year=2002
ORDER BY points DESC,players.player;

and got a "syntax error in join expression". The parentheses make it
correct! I'm happy: I've learned something today.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Lasse Edsvik]

Bob,

same here....... damn parentheses... *grrr*

/Lasse

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23nGIWBT1DHA.1752@tk2msftngp13.phx.gbl...

> Lasse Edsvik wrote:

> > Found a neat solution :)
> >
> > SELECT players.player, year, points
> > FROM players LEFT JOIN scores ON (scores.player= players.player AND
> > year=2002)
> > ORDER BY points DESC,players.player;
> >
> > finally! was running out of hair on my head
> >

> :-)
>
> I had tried something similar to that:
>
> SELECT players.player, year, points
> FROM players LEFT JOIN scores ON scores.player= players.player AND
> year=2002
> ORDER BY points DESC,players.player;
>
> and got a "syntax error in join expression". The parentheses make it
> correct! I'm happy: I've learned something today.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Jeff Cochran]

On Wed, 07 Jan 2004 12:29:49 +0000, Harag
<harag@REMOVETHESECAPITALSsofthome.net> wrote:

>
>he needs to Join both tables together with a LEFT OUTER JOIN, he wants
>all the players (A, B, C, D) in the result even if they didn't score
>any points in which case they should show up null. The LEFT OUTER JOIN
>means that you want ALL the rows from the left table no matter what
>the criteria is for the ON statement

Missed that in the original post, you're correct.

Jeff

>
>

>>>Player Year Points
>>>B 2002 5
>>>A 2002 3
>>>C NULL NULL
>>>D NULL NULL

>
>
>I'm asuming that the Player table will have more info in the table
>than just an ID. like Player First & Last names, DOB, etc
>
>
>Al
>
>
>On Wed, 07 Jan 2004 12:22:08 GMT, [email]jcochran.nospam@naplesgov.com[/email] (Jeff
>Cochran) wrote:
>

>>On Wed, 7 Jan 2004 10:01:26 +0100, "Lasse Edsvik" <lasse@nospam.com>
>>wrote:
>>

>>>Hello
>>>
>>>I have 2 tables
>>>
>>>Players
>>>
>>>Player .......
>>>A
>>>B
>>>C
>>>D
>>>
>>>Scores
>>>
>>>Player Year Points
>>>A 2002 3
>>>B 2002 5
>>>A 2003 4
>>>B 2003 1
>>>
>>>
>>>I would like to list all players and order by points desc where year=2002,
>>>but i cant get it to list all players
>>>
>>>I would like to have the following result
>>>
>>>
>>>Player Year Points
>>>B 2002 5
>>>A 2002 3
>>>C NULL NULL
>>>D NULL NULL
>>>
>>>
>>>how's that done?

>>
>>From your table layout, it's all in a single table, unless "Player"
>>isactually an ID number and you have other fields in the Players table
>>you need displayed. At any rate, you can use a SELECT and WHERE with
>>table identifiers to do this, something like:
>>
>>SELECT Players.Player, Scores.Year, Scores.Points WHERE Scores.Year =
>>"2002" ORDER BY Scores.Points DESC
>>
>>Jeff

[Harag]

LOL yea I did that earlier but it was for SQL 2k which it works fine
on... then I learned he was using Access..

Al.

On Wed, 7 Jan 2004 10:10:04 -0500, "Bob Barrows"
<reb01501@NOyahoo.SPAMcom> wrote:

>Lasse Edsvik wrote:

>> Found a neat solution :)
>>
>> SELECT players.player, year, points
>> FROM players LEFT JOIN scores ON (scores.player= players.player AND
>> year=2002)
>> ORDER BY points DESC,players.player;
>>
>> finally! was running out of hair on my head
>>

>:-)
>
>I had tried something similar to that:
>
>SELECT players.player, year, points
>FROM players LEFT JOIN scores ON scores.player= players.player AND
>year=2002
>ORDER BY points DESC,players.player;
>
>and got a "syntax error in join expression". The parentheses make it
>correct! I'm happy: I've learned something today.
>
>Bob Barrows

[Harag]

Glad your problem is finally solved.

Al.

On Wed, 7 Jan 2004 16:43:04 +0100, "Lasse Edsvik" <lasse@nospam.com>
wrote:

>Bob,
>
>same here....... damn parentheses... *grrr*
>
>/Lasse
>
>"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
>news:%23nGIWBT1DHA.1752@tk2msftngp13.phx.gbl...

>> Lasse Edsvik wrote:

>> > Found a neat solution :)
>> >
>> > SELECT players.player, year, points
>> > FROM players LEFT JOIN scores ON (scores.player= players.player AND
>> > year=2002)
>> > ORDER BY points DESC,players.player;
>> >
>> > finally! was running out of hair on my head
>> >

>> :-)
>>
>> I had tried something similar to that:
>>
>> SELECT players.player, year, points
>> FROM players LEFT JOIN scores ON scores.player= players.player AND
>> year=2002
>> ORDER BY points DESC,players.player;
>>
>> and got a "syntax error in join expression". The parentheses make it
>> correct! I'm happy: I've learned something today.
>>
>> Bob Barrows
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get a
>> quicker response by posting to the newsgroup.
>>
>>

>

[Lasse Edsvik]

thanks Bob and rest of the guys.

finally got it to work....... man i miss sql server lol

/Lasse


"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:uE0m5vP1DHA.4032@tk2msftngp13.phx.gbl...

> Hello
>
> I have 2 tables
>
> Players
>
> Player .......
> A
> B
> C
> D
>
> Scores
>
> Player Year Points
> A 2002 3
> B 2002 5
> A 2003 4
> B 2003 1
>
>
> I would like to list all players and order by points desc where year=2002,
> but i cant get it to list all players
>
> I would like to have the following result
>
>
> Player Year Points
> B 2002 5
> A 2002 3
> C NULL NULL
> D NULL NULL
>
>
> how's that done?
>
> TIA
> /Lasse
>
>