left join with unique rows from joined table?

[clone45]

Hello!

Say I have the following tables:

Pets
=====
id - integer (pk)
name - varchar
type - varchar

notes
=====
id - integer (pk)
pet_id - integer (fk)
note_text - text
date - date/time

I'd like to query for pets of type "dog", and I'd like the query to also return the most recent note associated with that pet, if any.

Here's an example:

Pets (id|name|type)
====
1|Fluffy|cat
2|Sammy|dog
3|Ralph|dog
4|Cheeky|dog

notes (id|pet_id|text|date)
=====
1|2|'sammy is healthy'|'2001-08-02'
2|2|'came to the office'|'2003-01-01'
3|4|'saw at lunch'|'2005-01-01'

I want a query that basically says, "Select all dogs and the most recent notes associated with each dog if any."

The results I'm looking for are:

name|notes|date
================
Sammy|'came to the office'|'2003-01-01'
Ralph|NULL|NULL
Cheeky|'saw at lunch'|'2005-01-01'

Any ideas on what this query would look like? I need something that would work for both sql server and mysql.

Thanks!
- Bret

 

[r937]

select p.name
     , n.note_test
     , n.date
  from pets as p
left outer
  join notes as n
    on n.pet_id = p.id
   and n.date =
       ( select max(date)
           from notes
          where pet_id = p.id )

p.s. your question was very well written




r937.com | rudy.ca

 

[bcdixit]

r937 is correct. co-related sub query is what it is called.

 

[bcdixit]

clone,
the ouptut that you have given doesnot include all Dogs . Fluffy is missing from the output.
well anyway i guess here is another way of doing it..any little more complex but I dont know if this works on SQL server or MySQL.

sel z.name, m.notes,m.date_id from bi_stg_8.t1 z left outer join
(sel pet_id,notes,date_id, rank() over (partition by pet_id order by date_id desc) x from bi_stg_8.t2
qualify x = 1 ) m
on z.id=m.pet_id

the subquery is picking up the most recent note for the pet .

 

[r937]

bcdixit, i love your ANSI SQL!!


[sub]well, except for the SEL keyword, which should say SELECT, yes?[/sub]

r937.com | rudy.ca

 

[bcdixit]

i work on teradata primarily.. 'select' can be written as 'sel' (teradata extension)....thanks for pointing that out r937. i should have changed sel to select in the forum

 

[JarlH]

Sorry for being such a nag, but it seems likes it's still not ANSI compliant SQL syntax.

SQL-2003 Validator output:[tt]

select z.name, m.notes,m.date_id from bi_stg_8.t1 z left outer join
(select pet_id,notes,date_id, rank() over (partition by pet_id order by date_id desc) x from bi_stg_8.t2
qualify x = 1 ) m
^
syntax error: qualify x
correction: HAVING x

on z.id=m.pet_id[/tt]


I'm not familiar with the OLAP functionality and I have no clue at all what the qualify clause means. (Can't find any reference to the QUALIFY keyword in the SQL spec.)

 

[r937]

QUALIFY is probably part of the teradata dialect

and Fluffy is not missing in my LEFT OUTER JOIN

B-)

r937.com | rudy.ca

 

[clone45]

Hello!

Thank you for the responses. I have some follow up question. First, though, I wanted to clarify that fluffy is a cat, not a dog.

Quite honestly, I can't make heads or tails of the ANSI compliant version. What's bi_stg_8.ti? Well, anyhow, I've been focused on this proposed solution:

select p.name
     , n.note_test
     , n.date
  from pets as p
left outer
  join notes as n
    on n.pet_id = p.id
   and n.date =
       ( select max(date)
           from notes
          where pet_id = p.id )

If I left two notes for Ralph the dog on 11-11-2008, wouldn't my results contain two rows for Ralph? Unfortunately, the date column doesn't contain the time. If it did, the proposed solution would probably work fine.

My coworker initially suggested that adding a Top(1), or limit 1 might help, but I'm not sure where to put it.

Thanks again,
- Bret

 

[r937]

My coworker initially suggested that adding a Top(1), or limit 1 might help, but I'm not sure where to put it.

want to know where to put it? ;-)


clearly, the time has come that you learn the difference between the real world and ANSI SQL

neither TOP nor LIMIT is ANSI SQL

however, they are both faster

I'd like the query to also return the most recent note associated with that pet, if any.

that specification is not incompatible with returning two rows, if two rows were both made on the same date, and that date is the latest

unfortunately the date column doesn't contain the time

in that case, please specify how you want to decide which one of those comments on Ralph is to be the one that is selected


r937.com | rudy.ca