Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Joins

Author

Topic

bob s
Starting Member

7 Posts

Posted - 2002-07-04 : 07:27:31

Hi,

From the following three (simplified) tables, I'm trying to figure out how to return the most recent query for each contact from all customers

Customers: cus_id, summary
Contacts: cus_id, con_id, summary
Queries: cus_id, con_id, que_id, summary, created_date

I've created a view that returns all query rows for each contact but I just need the last one for example the desired result should look like...

Customer Contact Query
A A1 RFI
A A2 Support
B B1 First Contact
C C1 RFI

In other words Contact A1 may have submitted 30 queries, but "RFI" was their last, equally Customer A could have 30 Contacts and each would have a row if they had submitted at least 1 query.

Hope I haven't over simplified this! Any help greatly appreciated

Bob

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-04 : 14:25:26

Your going to need a groupby and having clause in there...

Could you post your view on here?

Also.. Just making sure, but you want the record with the most recent created date.. correct?

-----------------------
Take my advice, I dare ya

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-04 : 16:55:19

select *
from vw
where dte = (select max(dte) from vw v2 where v2.cust = vw.cust and v2.ctct = vw.ctct)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

bob s
Starting Member

7 Posts

Posted - 2002-07-05 : 03:30:26

Cheers Guys,

Since first post spec from client changed (doesn't it always), I'd figured out the Max(Created_Date) thing but thx anyway nr.

Below is original view giving v basic idea of required report...

select a.summary, b.summary, c.summary, c.created_date
from Customers a RIGHT OUTER JOIN Contacts b
on a.cus_id = b.cus_id
LEFT OUTER JOIN Queries c
on b.con_id = c.con_id

The view that gets results is...

select a.summary, b.summary, c.summary, c.created_date
from Customers a RIGHT OUTER JOIN Contacts b
on a.cus_id = b.cus_id
LEFT OUTER JOIN (select con_id, max(created_date) as created_date from queries group by con_id) d
on b.con_id = d.con_id
LEFT OUTER JOIN Queries c
on d.con_id = c.con_id and d.created_date = c.created_date

I had to have a single view for this rather than querying an existing one

M.E. is there anyway that HAVING would have helped in this situation?

Thanks for your help

Bob

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-05 : 07:21:09

Give this query a try. It should have the same results as nr's and may (or may not) better utilize indexes...


select * 
from vw v1
where not exists (
    select 1
    from vw v2
    where v1.dte < v2.dte and
          v1.cust = v2.cust and
          v1.ctct = v2.ctct)

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources