SQL HELP 1

[markbabcock]

I have a SQL Question I was wondering if anyone can help me with.

I have two tables that have the following structure:

table1
+--------+-----------------------+
|ID1 (pk)| subject |
+--------+-----------------------+
| 1 | math |
+--------+-----------------------+
| 2 |history |
+--------+-----------------------+
| 3 |biology |
+--------+-----------------------+


table2

+---------+-----------+---------+-----------------------+
|ID2 (PK) | ID1 (FK) |SendTO | Datetime |
+---------+-----------+---------+-----------------------+
| 1 | 1 | mark | 10/25/2003 14:23:00 |
+---------+-----------+---------+-----------------------+
| 2 | 1 | robin | 10/26/2003 12:03:00 |
+---------+-----------+---------+-----------------------+
| 3 | 2 | mark | 10/25/2003 14:44:00 |
+---------+-----------+---------+-----------------------+
| 4 | 3 | robin | 10/26/2003 08:34:00 |
+---------+-----------+---------+-----------------------+

table1.ID1 is PK for that table
table2.ID1 is FK
table2.ID2 is PK

I want to get the subject and who it was sent to from table two. However, I need to only retreive one person per FK in table2 based on the most recent entry.
for example: if I query mark I should only see that he has histrory. if I query robin I want to see math and biology.

Can anyone please help me with this? Is my table structure off? I have checked the rules of normalization and it seems to be in 3NF.

I have been trying every query i can think of and am stuck. I appreciate the help

 

[r937]

> I want to get the subject and who it was
> sent to from table two. However, I need to
> only retreive one person per FK in table2
> based on the most recent entry.

[tt]select AA.subject
, BB.SendTo
from table1 AA
inner
join table2 BB
on AA.ID1 = BB.ID1
where BB.Datetime
= ( select max(Datetime
from table2
where SendTo = BB.SendTo ) [/tt]

> for example: if I query mark I should only see
> that he has histrory. if I query robin I want
> to see math and biology.

those two examples are inconsistent with each other and/or your sample data

rudy

http://r937.com/

 

[markbabcock]

You say the data is inconsistant.

I will try to clarify.

table a holds a generic message (subject) and I need to see the user in table2 who has the message.
does that help any?

 

[r937]

nope, that's a different query

"i need to see the user who has X"

is different from

"if i query robin i want to see Y and Z"


mark has two subjects, robin has two subjects, yet for mark you want only 1 returned, for robin 2 -- that's what's inconsistent

 

[markbabcock]

lets start with table2

+---------+-----------+---------+-----------------------+
|ID2 (PK) | ID1 (FK) |SendTO | Datetime |
+---------+-----------+---------+-----------------------+
| 1 | 1 | mark | 10/25/2003 14:23:00 |
+---------+-----------+---------+-----------------------+
| 2 | 1 | robin | 10/26/2003 12:03:00 |
+---------+-----------+---------+-----------------------+
| 3 | 2 | mark | 10/25/2003 14:44:00 |
+---------+-----------+---------+-----------------------+
| 4 | 3 | robin | 10/26/2003 08:34:00 |
+---------+-----------+---------+-----------------------+


robin and mark both appear 2 times each in this table

if i run a query on mark

then i will get back 2 records if I run a query on robin i will get back 2 records

and if i run a basic equijoin query like this

select a.subject,b.sendto from table1 as a, table2 as b where a.id1 = b.id1 and a.sentto = 'mark'

i would get back

+----------------------+---------------+
| subject | sendto |
+----------------------+---------------+
| math | mark |
+----------------------+---------------+
| history | mark |
+----------------------+---------------+

and if i run

select a.subject,b.sendto from table1 as a, table2 as b where a.id1 = b.id1 and a.sentto = 'robin'


+----------------------+---------------+
| subject | sendto |
+----------------------+---------------+
| math | robin |
+----------------------+---------------+
| biology | robin |
+----------------------+---------------+


what I wnat to do is run a query that will return soemthing like this:

if i run it on mark

+-------------------+--------------+
| subject | sendto |
+-------------------+--------------|
| history | mark |
+-------------------+--------------+

and if i run it on robin

+-------------------+--------------+
| subject | sendto |
+-------------------+--------------|
| math | robin |
+-------------------+--------------+
| biology | robin |
+-------------------+--------------+

because robin has a higher datetime stamp in table2 so she would get id1.

and if i run this query
select id1,max(datetimestamp) from table2 group by id1
i get

+--------+--------------------+
| id1 | datetimestamp |
+--------+--------------------+
| 1 | 10/26/2003 12:03 |
+--------+--------------------+
| 2 | 10/25/2003 14:44 |
+--------+--------------------+
| 3 | 10/26/2003 08:34|
+--------+--------------------+

 

[r937]

ah, i get it

try this:

[tt]select AA.subject
, BB.SendTo
from table1 AA
inner
join table2 BB
on AA.ID1 = BB.ID1
where BB.Datetime
= ( select max(Datetime)
from table2
where ID1 = AA.ID1 ) [/code]

 

[markbabcock]

R937 Thanks for all your help that seems to be the result set I was looking for. Now to look at what the says.. inner join with a sub query.. Yippie.. my head already hurts..but thanks.