A Common Query Question!

Hi Guys,

I have a query that gives me multiple rows for every customer, where as my requirement is to have one row for each customer. Below are the details:

Customers Table

Cust_ID int (PK)

Cust_Name varchar(255)

Subscription_Status int

Cust_Subscription_Audit Table

ID Identity(PK)

Cust_ID int

Old_Subscription_Status int

New_Subscription_Status int

Audit_Date DateTime

Logins Table (one customer may have multiple logins )

ID Identity (PK)

Cust_ID int

login name varchar(256)

email_ID varchar(256)

F_Name varchar(50)

L_name varchar(50)

role int

Last_Modified DateTime

INDEXES

Logins Table

PK__Clustered Ascending

No Index on Audit Table

Customers Table

PK__Clustered_ Ascending

Below is the query that is being used to extract the data

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID,MAX(CSA.Audit_Date)

FROM Customers C

INNERJOIN Cust_Subscription_Audit CSA ON C.Cust_ID= CSA.Cust_ID

INNERJOIN Logins L ON L.Cust_ID= C.Cust_ID

WHERE C.Subscription_Status >=1 AND L.Role= 1

GROUPBY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

This gives me multiple rows for a customer. What I am looking for is

1) One row for each customer.(Query 1)

2) If possible can pick the row of my choice Like log in that was added first or last based on login or Modified date in login table.(Query 2)

3) If possible can pick the row by row number i.e. if there are multiple logins for a customer then the 2nd login. (Query 3)

Your help will be appreciated.

Thanks

-Leo

Can you post some DDL, including constraints and indexes, sample data and expected result, please?

AMB

|||

Hi ,

Thanks for your prompt reply. Here is the Sample Data

Customers Table

Cust_ID Cust_Name SubsStatus

1 abc corp. 7

2 Adventure Works Inc 1

3 MicroCorn Inc 2

4 Fabrikam 7

5 Goleeeee Inc 5

Logins Table

ID Cust ID Login Email f_Name l_Name Role Date_Modified

1 1 rob1 rob1@.hotmail.com Rob Haany 1 2006-02-28 13:24
2 2 scot1 scot1@.yahoo.com Scot claudia 1 2006-05-24 12:30
3 3 scot2 scot2@.hotmail.com Scot Roy 1 2006-05-24 12:30
4 4 rob NULL RON HARTON 1 2006-08-22 15:26
5 4 qsir qsir@.hotmail.com Q Sir 1 2006-09-17 12:23
6 5 nzaar nzaar@.hotmail.com nad zaar 3 2006-09-17 12:23
7 5 jluk jluk@.hotmail.com Ron Richard 1 2006-09-17 12:23
8 2 drase draze@.hotmail.com Dino Mosoli 1 2006-08-22 15:26
9 2 dlins David Lin 3 2006-12-28 13:24
10 4 dmay NULL David May 1 2006-05-24 12:30

Role 1 = admin

Role 3 = Primary Contact

ID Cust_ID Old_Subscription_Status New_Subscription_Status Audit_Date

1 1 3 7 2006-09-24 12:30

2 2 0 1 2006-03-24 02:30

3 3 1 2 2006-08-24 10:30

4 4 1 7 2006-11-24 12:30

5 5 3 5 2006-09-24 12:30

6 4 3 1 2006-10-24 12:30

7 3 0 1 2006-06-24 12:30

8 1 0 1 2006-02-24 12:30

9 1 1 3 2006-07-24 12:30

10 5 1 3 2006-07-24 12:30

The required result is

Cust_ID Cust_Name F_name L_Name Email Audit Date

1 abc Corp Rob Haany rob1@.hotmail.com 2006-09-24 12:30

2 Adventure Works Inc Scot claudia scot1@.yahoo.com 2006-03-24 02:18

3 MicroCorn Inc Scot Roy scot2@.hotmail.com 2006-08-24 10:28

4 Fabrikam Q Sir qsir@.hotmail.com 2006-11-24 12:30

5 Goleeee Inc Ron Richard jluk@.hotmail.com 2006-09-24 12:30

Right now what is happening is threre are more than one records for Cust_ID = 2. The basic question is (Query 1) is how can we restrict the result set to only one row per Cust_ID. So my other questions (Query 2, Query3) were how can we choose one of these records based on some criteria e.g. since more than one records are there for Cust_ID = 2, and I wana choose the one that is with earliest Date_Modified or 2nd earliest Modified date.

Thanks,

-Leo.

|||

hi Leo,

If you are using SQL server 2005 Try this:

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)

FROM Customers C

INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID

CROSS APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID ) L

WHERE C.Subscription_Status >=1 AND L.Role= 1

GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

|||

Thank you very much for this nice hint. I tweeked the query little bit and it woked for me here is the modified query.

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)

FROM Customers C

INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID

OUTER APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID And [Role] = 1 ) L

WHERE C.Subscription_Status >=1

GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

|||do you need OUTER APPLU here? OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function (pretty much like OUTER JOIN in case of normal table). So if there is no match in table 'Logins' the value of L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID will be NULL.