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

[George Carless]

Robert's suggestion is a reasonable one, but only if the tables will
always be 'identical' -- in which case, the suggestion is one that should
probably be implemented, with a couple of caveats.  First: if the tables
are likely to hold different data at some point (as I expect that they may
well) then having seperate tables is necessary.  Second: if they're not
going to hold different data, but an arbitrary number of 'activities' (or
whatever) might exist then the activity should be split into a separate
activities table, with a key to it from the primary table.

Ah, such fun.

Cheers,
--George

On Wed, 2 Apr 2003, Robert Coggins wrote:

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

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