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
