CODEWITHSUNDEEP
mysqlsqlselect
Adiansyah
Adiansyah

Reputation: 353

select multiple column with multiple condition

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

Upvotes: 1

Views: 1049

Answers (1)

Teja
Teja

Reputation: 13534

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;

Upvotes: 1

Related Questions