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

[Robert Coggins]

I am not sure of the use of the ERD you have here.  This question is purely
for my own curiosity and education.  But here ti goes.  Would it be so wrong
to do this...

Instead of having four tables for exercise, food, weight, and liquids would
it not be easier to just have one table called maybe activities with
person_id, date, exercise_notes, AND say maybe an ACTIVITY attribute that
could be a value of either exercise, food, weight, and liquids? Well, tell
me what you all think.  I am trying to learn...

-Rob


----- Original Message -----
From: "Cade Thacker" <linux at cade.org>
To: ale at ale.org
To: <ale at ale.org>
Sent: Tuesday, April 01, 2003 11:28 PM
Subject: [ale] [OT] Left Join SQL help...


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

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