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

[jemarasco at bellsouth.net]

SELECT name, exercise_notes, food_notes, weight_notes, liquids_notes
FROM person p
LEFT OUTER JOIN weight w ON p.person_id = w.person_id and w.date = '2003-03-30'
LEFT OUTER JOIN liquids l ON p.person_id = l.person_id and w.date = '2003-03-30'
LEFT OUTER JOIN exercise e ON p.person_id = e.person_id and w.date = '2003-03-30'
LEFT OUTER JOIN food f ON p.person_id = f.person_id and w.date = '2003-03-30'
WHERE p.person_id = 1

Adding note types involves database structure and query changes.

Arguably more flexible is:

person
====
person_id (primary key)
name
====

notes
====
person_id (primary key)
note_type (primary key)
date (primary key)
note
====

note_type
====
note_type (primary key)
note_type_desc
====

SELECT name, en.note 'exercise_notes', fn.note 'food_notes', wn.note 'weight_notes', ln.note 'liquids_notes'
FROM person p
LEFT OUTER JOIN notes en ON p.person_id = en.person_id AND en.date = '2003-03-30' AND en.note_type = 1
LEFT OUTER JOIN notes fn ON p.person_id = fn.person_id AND fn.date = '2003-03-30' AND fn.note_type = 2
LEFT OUTER JOIN notes wn ON p.person_id = wn.person_id AND wn.date = '2003-03-30' AND wn.note_type = 3
LEFT OUTER JOIN notes ln ON p.person_id = ln.person_id AND ln.date = '2003-03-30' AND ln.note_type = 4
WHERE p.person_id = 1

Adding note types involves database data and query changes.

Even more flexible is:

SELECT name, note, note_type_desc
FROM person p
LEFT OUTER JOIN notes n ON p.person_id = n.person_id
LEFT OUTER JOIN note_type nt ON n.note_type = nt.note_type
WHERE p.person_id = 1 and n.date = '2003-03-30'
FOR XML AUTO

Adding note types involves only database data changes.

Hope this is right...

> 
> From: Cade Thacker <linux at cade.org>
> Date: 2003/04/01 Tue PM 11:28:38 EST
> To: ale at ale.org
> 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