Mysql Join with filters 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.
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.