[ale] [OT] Left Join SQL help...

[Cade Thacker]

OK, i have been googling and testing for 3 hours and I can not seem to
find the correct answer to this question. This is so freakin simple that I
embarassed to ask, but here goes. Very easy. 5 tables...

person
====
person_id
name
====

4 identical tables, exercise, food, weight, liquids.

exercise
====
person_id // matches person.person_id
date // the date ;)
exercise_notes // text of what exercise you did that day
==


The information I want is based on a person id and a date. I want to
return the exercise, food, liquids, and weight entry for that person, on
that date.

So given person_id 1 and date 2003-03-31, say they have only a exercise
entry, what I want back is:

person = cade
exercise = "Walked 1 mile"
food = NULL
weight = NULL
liquids = NULL

I know that I can do this with an OUTER (LEFT) join, but the correct
syntax is eluding me. I have tried

SELECT name, exercise_notes, food_notes, weight_notes, liquids_notes
FROM person
LEFT JOIN weight ON person.person_id = weight.person_id
LEFT JOIN liquids ON person.person_id = liquids.person_id
LEFT JOIN exercise ON person.person_id = exercise.person_id
LEFT JOIN food ON person.person_id = food.person_id

WHERE person.person_id = 1
AND (weight.date ='2003-03-30' OR weight.date = NULL)
AND (liquids.date ='2003-03-30' OR liquids.date = NULL)
AND (exercise.date ='2003-03-30' OR exercise.date = NULL)
AND (food.date ='2003-03-30' OR food.date = NULL)

#end of sql

Did I give enough info?  All of the help on google just returns how to
outer join 2 tables.

TIA....

--cade

On Linux vs Windows
==================
Remember, amateurs built the Ark, Professionals built the Titanic!
==================



_______________________________________________
Ale mailing list
Ale at ale.org
http://www.ale.org/mailman/listinfo/ale