CODEWITHSUNDEEP
sql-serversql-server-2008
Sravan Kumar
Sravan Kumar

Reputation: 1457

Using Joins to get all data from left table

I have two tables as shown below.

Id Name                            Id   Status
--------                           -------------------
1   A                              1        Approved
2   B                              6        Approved
3   C                              4        Pending
4   D                              1        Approved
5   E                              1        Pending
6   F                              3        Pending
                                   5        Rejected

Now this is how I want the output to be:

Id  Name  Status
-------------------
1    A    Pending
2    B    
3    C    Pending 
4    D    Pending
5    E
6    F

I have tried using left join but I am getting multiple rows.

select t1.ID,Name,Status from t1 left join t2 on t1.id=t2.id

and if I add where Status=pending i am getting only ids 1 and 3. Here are the queries i tried:

select distinct t1.id,name,status from t1 left join t2 on t1.id=t2.id  (this gives me duplicate records i.e id 1 is occurs twice with approved and pending)

and also 

select distinct t1.id,name,status from t1 left join t2 on t1.id=t2.id where t2.status='pending'  (gives me only 1,3 and 4)

Could any one help me, Thanks in advance.

Upvotes: 4

Views: 4930

Answers (1)

Martin Smith
Martin Smith

Reputation: 453067

To include all rows from T1 and only those from T2 with status = 'pending' move the condition into the ON clause SQL Fiddle

SELECT t1.ID,
       Name,
       Status
FROM   t1
       LEFT JOIN t2
         ON t1.id = t2.id
            AND t2.status = 'pending'

To only return one row per id in the event of dupes you could do (Fiddle)

WITH CTE AS
(
SELECT DISTINCT id, Status
FROM T2
WHERE Status = 'Approved' 
)
SELECT t1.ID,
       Name,
       Status
FROM   t1
       LEFT JOIN CTE
         ON t1.id = CTE.id

Upvotes: 5

Related Questions