Thursday, February 28, 2013

USE SUBQUERY IN INNER JOIN


create table tblUser
 (
  UserId int identity(1,1) not null,
  UserName nvarchar(500),
  Age int

 )

 INSERT INTO tblUser select 'Rahul',26
 INSERT INTO tblUser select 'Yunus',26


 create table tblTransaction

  (
   TransactionId int identity(1,1) not null,
   UserId int,
   Amount int ,
   Status bit
  )

  INSERT INTO tblTransaction SELECT 1,200,1
  INSERT INTO tblTransaction SELECT 1,2100,1
  INSERT INTO tblTransaction SELECT 1,900,1
  INSERT INTO tblTransaction SELECT 1,60,1



  INSERT INTO tblTransaction SELECT 2,200,1
  INSERT INTO tblTransaction SELECT 2,2100,1
  INSERT INTO tblTransaction SELECT 2,900,1
  INSERT INTO tblTransaction SELECT 2,60,1
  INSERT INTO tblTransaction SELECT 2,55,1




SELECT * FROM tblUser
SELECT * FROM tblTransaction

SELECT * FROM tblUser as U
INNER JOIN
(
SELECT * FROM tblTransaction
WHERE
       TransactionId IN
               (
                       SELECT MAX(TransactionId)
                       FROM
                               tblTransaction
                       GROUP BY UserId
               )
) AS [A]
ON
U.UserId=A.UserId