Try:
select t1.player,t1.game,t1.points,t1.ppg,t2.team as team_name
from sc_scoreleaders as t1
left join sc_seasonstanding as t2 ON t1.team=t2.id
You don't need to join sc_scoreleaders 2 times...
Better:
select t1.player,t1.game,t1.points,t1.ppg,
ifnull(t2.team,"No Team") as team_name
from sc_scoreleaders as t1
left join sc_seasonstanding as t2 ON t1.team=t2.id
On Mon, 2002-03-11 at 23:15, Johnny Withers wrote:
>
> So, it's been a while since I've actaully needed to do this
> and I can not for the life of me figure this out, maybe it's
> because I've been working for nine hours w/o getting up.
>
> Here it is:
> I have two tables, one table holds Team information, the
> other table holds top scorers. However, sometimes the top
> scorers are a tie between players on different teams, so I need
> a record returned with no team associated with it:
>
> ie:
>
> Top Scorers:
> Name Team Points
> John Doe Team A 423
> Jane Doe Team B 233
> Tie between 3 players (no team) 100
>
> This is the left join I'm using:
> select t1.player,t1.game,t1.points,t1.ppg,t2.team as team_name
> from sc_scoreleaders as t1,sc_seasonstanding as t2
> left outer join sc_scoreleaders on t1.id=t2.team;
>
> And I get these results:
> *************************** 1. row ***************************
> player: Johnny Withers
> game: 2
> points: 200
> ppg: 100
> team_name: Mindbender's Fake Team
> *************************** 2. row ***************************
> player: No Team Asso
> game: 1
> points: 1
> ppg: 1
> team_name: Mindbender's Fake Team
>
>
> Table desc and data follows:
>
> mysql> desc sc_seasonstanding;
> +------------+----------------------+------+-----+---------+------------
> ----+
> | Field | Type | Null | Key | Default | Extra
> |
> +------------+----------------------+------+-----+---------+------------
> ----+
> | id | int(10) unsigned | | PRI | NULL |
> auto_increment |
> | team | varchar(255) | | | |
> |
> | win | tinyint(1) unsigned | YES | | 0 |
> |
> | loss | tinyint(1) unsigned | YES | | 0 |
> |
> | pts_scored | smallint(3) unsigned | YES | | 0 |
> |
> | pts_versus | smallint(3) unsigned | YES | | 0 |
> |
> | pts_diff | smallint(3) unsigned | YES | | 0 |
> |
> +------------+----------------------+------+-----+---------+------------
> ----+
> 7 rows in set (0.01 sec)
>
> mysql> desc sc_scoreleaders;
> +--------+----------------------+------+-----+---------+----------------
> +
> | Field | Type | Null | Key | Default | Extra
> |
> +--------+----------------------+------+-----+---------+----------------
> +
> | id | int(10) unsigned | | PRI | NULL | auto_increment
> |
> | team | int(10) unsigned | YES | MUL | 0 |
> |
> | player | varchar(255) | YES | | NULL |
> |
> | game | varchar(255) | YES | | 0 |
> |
> | points | smallint(6) unsigned | YES | | 0 |
> |
> | ppg | smallint(6) unsigned | YES | | 0 |
> |
> +--------+----------------------+------+-----+---------+----------------
> +
>
>
> data:
>
> mysql> select * from sc_seasonstanding\G
> *************************** 1. row ***************************
> id: 2
> team: Mindbender's Fake Team
> win: 32
> loss: 0
> pts_scored: 4052
> pts_versus: 12
> pts_diff: 4050
> 1 row in set (0.00 sec)
>
> mysql> select * from sc_scoreleaders\G
> *************************** 1. row ***************************
> id: 1
> team: 2
> player: Johnny Withers
> game: 2
> points: 200
> ppg: 100
> *************************** 2. row ***************************
> id: 2
> team: 0
> player: No Team Asso
> game: 1
> points: 1
> ppg: 1
> 2 rows in set (0.00 sec)
>
>
>
> Someone help me out please...
> Server version: 3.23.44-Max
>
> (mysql,query,table,sql,etc)
>
> ---------------------
> Johnny Withers
> [EMAIL PROTECTED]
> p. 601.853.0211
> c. 601.209.4985
>
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
--
dsoares
(sql)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
