(sub) query from 3 tables - 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

General SQL Server Forums

New to SQL Server Programming

(sub) query from 3 tables

Author

Topic

B. Storm
Starting Member

3 Posts

Posted - 2010-08-02 : 09:38:02

I have 3 tables with the following fields:

tblCustomer: idCustomer, Filenr, Name, Address

tblStatusPerCustomer: idStatusPerCustomer, idCustomer, idStatusDescription, DateLastmodification

tblStatusDescription: idStatusDescription, StatusDescription

Relations:
tblCustomer.idCustomer > tblStatusPerCustomer.idCustomer
tblStatusPerCustomer.idStatusDescription > tblStatusDescription.idStatusDescription

Required return values:

tblCustomer.idCustomer, tblCustomer.Filenr, tblCustomer.Name, tblStatusPerCustomer.DateLastmodification, tblStatusDescription.StatusDescription

WHERE tblStatusPerCustomer.DateLastmodification = MAX(tblStatusPerCustomer.DateLastmodification)

Tested query:

SELECT DISTINCT TOP (100) PERCENT cl.Name, sc.StatusDescription , MAX(sc.DateLastmodification) AS MaxDate
FROM StatusPerCustomer AS spc
INNER JOIN (SELECT tblStatusDescription.StatusDescription, MAX(tblStatusPerCustomer.DateLastmodification) AS DateLastModification, tblStatusPerCustomer.idCustomer
FROM StatusPerCustomer LEFT OUTER JOIN tblStatusDescription ON StatusPerCustomer.Status = tblStatusDescription.idStatusDescription
GROUP BY tblStatusDescription.StatusDescription, StatusPerCustomer.idCustomer) AS sc ON sc.idCustomer = spc.idCustomer INNER JOIN tblCustomer AS cl ON cl.idCustomer = spc.idCustomer
GROUP BY cl.Filenr, cl.Name, sc.StatusDescription, sc.DateLastModification
ORDER BY cl.Name, MaxDate DESC, cl.Filenr, sc.StatusDescription

Resultset returns ALL previous Statusdescriptions instead of only the last Status per customer added to tblStatusPerCustomer

What am I doing wrong here?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-08-02 : 09:49:19

i think u have missed the join condition on date
add this condition AND spc.DateLastmodification = SC.DateLastmodification and check it once.

B. Storm
Starting Member

3 Posts

Posted - 2010-08-02 : 10:41:04

Thank you bkrl.

I tried your suggestion at different positions in the query.
This didn't help either.

Can you paste your suggestion into my query?

Thanks,
Berend

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-02 : 11:11:26

Try this

SELECT cl.Name, cl.idCustomer,cl.Filenr, sc.StatusDescription ,sp.DateLastmodification
FROM tblCustomer cl
INNER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY idCustomer ORDER BY DateLastmodification DESC) AS seq, * FROM tblStatusPerCustomer) sp
ON sp.idCustomer = cl.idCustomer AND sp.seq = 1
INNER JOIN tblStatusDescription sc ON sc.idStatusDescription = sp.idStatusDescription
ORDER BY cl.Name,sp.DateLastmodification,cl.Filenr, sc.StatusDescription

B. Storm
Starting Member

3 Posts

Posted - 2010-08-02 : 12:15:39

Thank you all for responding.

After combining several responses (also from other sites) I managed to solve my problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources