Mysql Join with filters

Question

I have two tables: MAINCOLOR and SUBCOLOR with the data as:

MAINCOLOR :
MAINID COLOR
1 RED
2 BLUE
3 GREEN
SUBCOLOR:
SUBID MAINID SUBCOLOR STATUS
1 1 PINK 0
2 1 ORANGE 1
3 2 VIOLET 1

I need a result set as:

MAINID COLOR SUBCOLOR
1 RED ORANGE
2 BLUE VIOLET
3 GREEN

Resultset should be a join of MAINCOLOR and SUBCOLOR using MAINID. Need child data having status as 1.

Answer 1

You can get the result by using a LEFT JOIN between the maincolor and the subcolor.

The key is placing the filter of status=1 on the JOIN condition. Your code will be:

select m.mainid,
 m.color,
 s.subcolor
from maincolor m
left join subcolor s
 on m.mainid = s.mainid
 and s.status = 1

See SQL Fiddle with Demo.

The LEFT JOIN will return all rows from the maincolor table and any matching rows in the subcolor table. Since you what to apply a WHERE to the data in the subcolor table, then you will want to move that filter to the JOIN condition to get the result.

• Praveen Prasannan: Hurray. It worked. I was trying with a where clause, not resulting the required result. Thanks bluefeet.