List:General Discussion« Previous MessageNext Message »
From:Bruce Feist Date:July 9 2003 2:36pm
Subject:Re: left join help
View as plain text  
Rick Pasotto wrote:

>One of these days I will maybe understand...
>
A "left join" (t1 LEFT JOIN t2 ON <condition>) is defined as follows.
For each row in t1, find all matching rows in t2 and return the 
combination of t1 and t2 found.  If there are no t2s for a t1, leave the 
t2 values NULL in the result.

Try:

SELECT  *
  FROM (history h LEFT JOIN members m ON h.member_id = m.id) LEFT JOIN 
activity a ON h.activity = a.id
  WHERE h.date = <whatever.>;

Bruce Feist

>create table members (
>	id unsigned int autoincrement,
>	name
>)
>
>create table activity (
>	id unsigned int autoincrement,
>	description
>)
>
>create table history (
>	id unsigned in autoincrement,
>	date date,
>	member_id unsigned int,
>	activity unsigned int
>)
>
>What I need:
>	1) only records for a particular date
>	2) there should be at least one record for each activity
>	3) there may be multiples of the same activity on a given date
>	4) there may be multiples of the same member on a given date
>	5) not all members will be listed
>	6) the members.name result field may be NULL
>
>SELECT history.date, activity.description, members.name
>???
>WHERE history.date = 'YYYY-MM-DD'
>
>  
>



Thread
left join helpRick Pasotto9 Jul
  • Re: left join helpBruce Feist9 Jul