CODEWITHSUNDEEP
sqlpostgresql
rennajihi
rennajihi

Reputation: 472

Select from multiple table, eliminating duplicates values

I have these tables and values:

Person                 Account                
------------------     -----------------------              
ID | CREATED_BY        ID | TYPE | DATA                 
------------------     -----------------------              
1 |                    1  | T1   | USEFUL DATA                
2 |                    2  | T2   |                 
3 |                    3  | T3   |
4 |                    4  | T2   |



Person_account_link                                       
--------------------------                     
ID | PERSON_ID | ACCOUNT_ID                                   
--------------------------                     
1 |  1         |   1                                
2 |  1         |   2                                  
3 |  2         |   3                        
4 |  3         |   4

I want to select all persons with T1 account type and get the data column, for the others persons they should be in the result without any account information.

(I note that person 1 has two accounts : account_id_1 and account_id_2 but only one row must be displayed (priority for T1 type if exist otherwise null)

The result should be :

Table1             
----------------------------------------------------- 
PERSON_ID | ACCOUNT_ID | ACCOUNT_TYPE | ACCOUNT_DATA    
----------------------------------------------------- 
1         |  1         |    T1        |  USEFUL DATA       
2         |  NULL      |   NULL       |  NULL         
3         |  NULL      |   NULL       |  NULL         
4         |  NULL      |   NULL       |  NULL

Upvotes: 0

Views: 42

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In Postgres, I like to use the FILTER keyword. In addition, the Person table is not needed if you only want persons with an account. If you want all persons:

SELECT p.id,
       MAX(a.id) FILTER (a.type = 'T1') as account_id,
       MAX(a.type) FILTER (a.type = 'T1') as account_type,
       MAX(a.data) FILTER (a.type = 'T1') as account_data
FROM Person p LEFT JOIN
     Person_account_link pl
     ON pl.person_id = p.id LEFT JOIN
     account a
     ON pl.account_id = a.id
GROUP BY p.id;

Upvotes: 0

trincot
trincot

Reputation: 350272

You would need an outer join, starting with Person and then to the other two tables. I would also aggregate with group by and min to tackle the situation where a person would have two or more T1 accounts. In that case one of the data is taken (the min of them):

select    p.id person_id,
          min(a.id) account_id,
          min(a.type) account_type,
          min(a.data) account_data
from      Person p
left join Person_account_link pa on p.id = pa.person_id
left join Account a on pa.account_id = a.id and a.type = 'T1'
group by  p.id

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do conditional aggregation : 

SELECT p.id,
       MAX(CASE WHEN a.type = 'T1' THEN a.id END) AS ACCOUNT_ID,
       MAX(CASE WHEN a.type = 'T1' THEN 'T1' END) AS ACCOUNT_TYPE,
       MAX(CASE WHEN a.type = 'T1' THEN a.data END) AS ACCOUNT_DATA 
FROM person p LEFT JOIN
     Person_account_link pl
     ON p.id = pl.person_id LEFT JOIN
     account a
     ON pl.account_id = a.id
GROUP BY p.id;

Upvotes: 2

Related Questions