×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

left join with unique rows from joined table?

left join with unique rows from joined table?

left join with unique rows from joined table?

(OP)
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

RE: left join with unique rows from joined table?

CODE

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


smile

r937.com | rudy.ca

RE: left join with unique rows from joined table?

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

RE: left join with unique rows from joined table?

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 .


RE: left join with unique rows from joined table?

bcdixit, i love your ANSI SQL!! smile


well, except for the SEL keyword, which should say SELECT, yes?

r937.com | rudy.ca

RE: left join with unique rows from joined table?

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

RE: left join with unique rows from joined table?

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

SQL-2003 Validator output:

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



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

RE: left join with unique rows from joined table?

QUALIFY is probably part of the teradata dialect

and Fluffy is not missing in my LEFT OUTER JOIN

happy shades

r937.com | rudy.ca

RE: left join with unique rows from joined table?

(OP)
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. smile

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:

CODE

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

RE: left join with unique rows from joined table?

Quote:

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? winky smile

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 smile


Quote:

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


Quote:

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close