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

[Tom]

Try this:

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));

SQL is very fussy about grouping.  Also, don't forget the terminating
':'.


-Tom


On Tue, 2003-04-01 at 23:28, Cade Thacker wrote:
> 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
-- 
Tom <tew at wiencko.com>

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