lundi 19 décembre 2016

MySQL JOIN, WHERE and IF issue

i have table of members

table members :
pid|  id    | name
1  |  id01  | jenny
2  |  id02  | kain
3  |  id03  | alex

and have another table members_opt

table members_opt
pid | members_id | category
1   | id01       | cat
2   | id01       | dog
3   | id02       | dog
4   | id03       | NULL

now i use below SQL query

SELECT * FROM members a JOIN
 (SELECT members_id, max(category) as category FROM members_opt GROUP BY members_id) b
ON a.id = b.members_id

But this SQL Query not catch "id03"'s data because "id03"'s members_opt.category is NULL

I want this result

result : 
id   | name  | category
id01 | jenny | cat
id02 | kain  | dog
id03 | alex  | NULL

(the result now showed double name, double id value.)

How can i use SQL query?

Aucun commentaire:

Enregistrer un commentaire