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

[F. Grant Robertson]

Unless each type of record has specific fields that others don't, this would be a better method of doing it.  You could also make this more dynamic by having a small lookup table that related to the type of record (excercise, food, weight.. ) so, if later you decided that you wanted to log a different activity, you would simply add the definition for it to the lookup table. You could use the lookup table to generate a dropdown on the web form (assuming this is a web based app) for selecting the activity type. The net result, you could add and change activity types without changing the rendered html manually.

You would still do a join to get the record..

select a.*, b.activity_type from activity as a, activity_type as b where 'whatever your query' AND a.activity_type_id_fk=b.activity_type_id;

insert records with an activity_type_id_fk that matches the activity_type_id for the activity_type you want from the activity_type table. (that's a mouthful)

If you'd like a quick example of the schema for something like this, I'll be happy to post it.

-G


-----Original Message-----
From: ale-admin at ale.org [mailto:ale-admin at ale.org]On Behalf Of Robert
To: ale at ale.org
Coggins
Sent: Wednesday, April 02, 2003 9:56 AM
To: ale at ale.org
Subject: Re: [ale] [OT] Left Join SQL help...


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


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.459 / Virus Database: 258 - Release Date: 2/25/2003

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.459 / Virus Database: 258 - Release Date: 2/25/2003

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