List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 15 1999 7:34pm
Subject:Re: Left Join query question
View as plain text  
Mark wrote:
> 
> Hi,
> 
> Would someone lend a hand with a join that has me confused.
> 
> Well actually, almost every join has me confused.
> 
> >From the manual:
> 
> SELECT table1.* from table1
> LEFT JOIN table2 ON table1.id=table2.id
> where table2.id is NULL
> 
> selects from table 1 that which is not in table 2.
> 
> That's pretty neat.
> 
> It's a start on what I need, but I can't figure it out.
> 
> Can I select the item_names  that  player_name  does not have,
> but just fpr a particular player from the game table ?
> 
> game                    status                  player_names      item_names
> -------                 --------                -----------       -----------
> player_id               player_id               player_id         item_id
> group                   item_id                 player_name       item_name
> game
> location
> 
> (Players start with 500 items - no entries in the status table.
> As they lose items, entries go into the status table..
> As they gain items, entries come back out of the status table.)
> 
> Any ideas??  Help would be much appreciated
> 
> Thanks, Mark

Hi Mark

Just use something like this:
SELECT
	game.game
	,player_names.player_name
	,item_names.item_name
FROM
	game
	,player_names
	,item_names
	LEFT JOIN status 
	ON status.player_id = game.player_id
	AND status.item_id = item_names.item_id
WHERE
	game.game = xyz
	AND game.player_id = player_names.player_id
	AND status.item_id IS NULL

Tschau
Christian

PS: Sorry for the late answer, I was on vacation.

Thread
Left Join query questionMark29 Jun
  • Re: Left Join query questionChristian Mack15 Jul