select multiple column with multiple condition

Question

i have some trouble in selecting two columns and each column has different condition

first table (main)

id_main |Title
---------------
1       |Title1
2       |Title2

Second table (person)

id_psn | id_main | Name | status
-------------------------------
1      | 1       | Tedy | 1
2      | 1       | john | 2
3      | 2       | lisa | 1
4      | 2       | khan | 2

id_main for second table is a foreign key form table first primary key. i want a select query to produce 2 columns(person1 and person2) person1 contain name where status is 1, and person2 is 2. it should produce table like this

id_main | title  | person1 | person2
------------------------------------
1       | title1 | Tedy    | john
2       | titl2  | Lisa    | khan

ive tried a query like this

 SELECT  main.id_main ,main.title,
(SELECT  person.name  where person.status = 1) as 'person1',
(SELECT  bap.nama  where person.status= 2) as 'person2'
FROM main 
JOIN person on main.id_main = person.id_main

and doesnt work like what i want. it produce like this

id_main | title  | Person1 | person2
-----------------------------------
1       | title1 | Tedy    | NULL
1       | title1 | NULL    | john
2       | title2 | Lisa    | NULL
2       | title2 | NULL    | khan

hope anyone can solve this

Can you show what you have tried?

Bleach
– Bleach

2018-07-16 02:10:45 +00:00
Commented Jul 16, 2018 at 2:10 — Bleach
– Bleach, Commented Jul 16, 2018 at 2:10

Teja · Accepted Answer · 2018-07-16 02:12:51Z

1

SELECT a.id_main,
       b.Title AS title,
       MAX( CASE WHEN a.status = 1 THEN a.Name ELSE NULL END ) AS person1,              
       MAX( CASE WHEN a.status = 2 THEN a.Name ELSE NULL END ) AS person2
  FROM person a
INNER JOIN
       main b
    ON a.id_main = b.id_main
GROUP BY a.id_main,b.Title;

answered Jul 16, 2018 at 2:12

Teja

13.7k38 gold badges103 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

select multiple column with multiple condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related