CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
ixora
ixora

Reputation: 185

Join table on null where condition

I have tables Member and Transaction. Table Member has 2 columns MemberID and MemberName. Table Transaction has 3 columns, MemberID, TransactionDate, and MemberBalance.

The rows in the tables are as shown below:

Table Member:

MemberID           MemberName
=============================
1                  John
2                  Betty
3                  Lisa

Table Transaction:

MemberID        TransactionDate         MemberBalance
=====================================================
1               13-12-2012              200
2               12-12-2012              90
1               10-09-2012              300

I would like to query for MemberID, MemberName and MemberBalance where the TransactionDate is the latest (max) for each MemberID.

My query is like this:

SELECT 
    t.MemberID, m.MemberName , t.MemberBalance
FROM 
    Member AS m 
INNER JOIN 
    Transaction AS t ON m.MemberID = t.MemberID 
WHERE 
    t.TransactionDate IN (SELECT MAX(TransactionDate)
                          FROM Transaction 
                          GROUP BY MemberID)

This query returns:

MemberID           MemberName         MemberBalance
===================================================
1                  John               200
2                  Betty              90

My problem is, I want the query to return:

MemberID           MemberName         MemberBalance
===================================================
1                  John               200
2                  Betty              90
3                  Lisa               NULL

I want the member to be displayed even if its MemberID does not exist in the Transaction table.

How do I do this?

Thank you.

Upvotes: 1

Views: 120

Answers (5)

Narsimha
Narsimha

Reputation: 184

SELECT a.MemberId,a.MemberName,a.MemberBalance
FROM
(
SELECT m.MemberId,m.MemberName,t1.MemberBalance
      ,ROW_NUMBER() OVER(PARTITION BY m.MemberId ORDER BY t1.TransactionDate DESC) AS RN
FROM
@Member m OUTER APPLY (SELECT t.MemberId,t.MemberBalance,t.TransactionDate
                       FROM @Transaction t WHERE m.MemberId=t.MemberId) t1
)a
WHERE a.RN=1

Upvotes: 0

Igor Borisenko
Igor Borisenko

Reputation: 3866

You need to use LEFT JOIN. Also you had an error in your query because if two members had transactions at the same time you can get two rows for both the users.

Try this

SELECT t.MemberID, m.MemberName , t.MemberBalance
FROM Member AS m 
    LEFT JOIN Transaction AS t ON m.MemberID = t.MemberID AND t.TransactionDate=
    ( 
        SELECT MAX(TransactionDate)
        FROM Transaction T2
        WHERE T2.MemberID=t.MemberID
    )

Upvotes: 1

Anton
Anton

Reputation: 2483

To keep member in the result set, you need an outer join.

Also, please don't forget to add a condition on memberid for inner select query, as you might get issues when a maximum date for one user would match a non-maximum date of another (your where condition would pass twice for the second user as his transaction dates would appear on the select's results twice, one would be his actual maximum date and another - the max date of some user matching a non-max date)

Upvotes: 2

Taryn
Taryn

Reputation: 247720

You can also use something like this:

SELECT m.MemberID, m.MemberName, t1.MemberBalance
FROM Member AS m 
LEFT JOIN
(
  select max(transactionDate) transactionDate, 
    MemberID
  from Transactions
  group by MemberID
) AS t 
  ON m.MemberID = t.MemberID 
left join transactions t1
  on t.transactionDate = t1.transactionDate
  and t.memberid = t1.memberid

See SQL Fiddle with Demo

Upvotes: 5

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

member to be displayed even if its MemberID does not exist in Transaction table

You can preserve rows using LEFT JOIN on the Member table to Transaction table.

where the TransactionDate is the latest (max) for each MemberID.

From SQL Server 2005 onwards, the preferred and better performing method is to use ROW_NUMBER()

   SELECT MemberID, MemberName, MemberBalance
     FROM (
   SELECT m.MemberID, m.MemberName , t.MemberBalance,
          row_number() over (partition by m.MemberID order by t.TransactionDate desc) rn
     FROM Member AS m 
LEFT JOIN [Transaction] AS t ON m.MemberID = t.MemberID 
        ) X
    WHERE rn=1;

Upvotes: 2

Related Questions