php - mysql left join and return joined and unjoind -

i have 2 tables , want join b , eace join unjoind results

table pixel

+----+------------+------------+-------------------+--------+------+------------+ | id | account_id | project_id | uuid              | name   | type | date       | +----+------------+------------+-------------------+--------+------+------------+ | 10 |          2 |          3 | e03aa~f86a1~7c661 | test 1 |    0 | 1420553362 | | 11 |          2 |          3 | a3e3b~b4182~da556 | test 2 |    1 | 1420553933 | |  9 |          1 |          1 | 57eae~e633c~b929f | test 3 |    1 | 1420041387 | +----+------------+------------+-------------------+--------+------+------------+ 

table pixel_tags

+----+------------+-------------------+--------------+--------------+------------+ | id | project_id | pixel             | tag          | name         | date       | +----+------------+-------------------+--------------+--------------+------------+ |  6 |          0 | 57eae~e633c~b929f | facebook-cpc | facebook-cpc | 1420041606 | |  7 |          0 | 57eae~e633c~b929f | google-cpc   | google-cpc   | 1420041621 | |  8 |          0 | a3e3b~b4182~da556 | utm_google   | test         | 1420554059 | +----+------------+-------------------+--------------+--------------+------------+ 

this query

select     `pixel`.*,     (case  when `pixel_tags`.`name` <>'' `pixel_tags`.`name` else `pixel`.`name` end ) `p_name`,     `pixel_tags`.`tag` `pixel` left join `pixel_tags` on `pixel`.`uuid`=`pixel_tags`.`pixel` `pixel`.`account_id`='1' 

the result if there tags pixel show in name, need return both, example if 1 pixel has 2 tags, wanna 3 rows, 1 name , 2 tags thank you.

dump

-- phpmyadmin sql dump -- version 4.0.10.7 -- http://www.phpmyadmin.net -- -- host: localhost -- generation time: may 14, 2015 @ 01:19 pm -- server version: 5.5.42-cll -- php version: 5.4.23  set sql_mode = "no_auto_value_on_zero"; set time_zone = "+00:00";  -- -- database: `mslm_db` --  -- --------------------------------------------------------  -- -- table structure table `pixel` --  create table if not exists `pixel` (   `id` int(11) not null auto_increment,   `account_id` int(11) not null,   `project_id` int(11) not null,   `uuid` text not null,   `name` text not null,   `type` int(11) not null,   `date` bigint(20) not null,   primary key (`id`) ) engine=myisam  default charset=utf8 auto_increment=22 ;  -- -- dumping data table `pixel` --  insert `pixel` (`id`, `account_id`, `project_id`, `uuid`, `name`, `type`, `date`) values (10, 2, 3, '75d79~535f8~96fa1~f1b5e~c21e7~e03aa~f86a1~7c661', '×—×ž×•×¦×™× ×–×” ××—', 0, 1420553362), (11, 2, 3, '195a6~83005~4c660~62ef3~8c79a~a3e3b~b4182~da556', '×—×ž×•×¦×™× ×–×” ×חות', 1, 1420553933), (9, 1, 1, 'd0950~15d68~354c8~5fbae~daf05~57eae~e633c~b929f', 'טורקיז', 1, 1420041387), (12, 4, 5, 'e92e8~deda0~11571~86fea~13af9~b2266~f8efd~fb9d3', 'חביתה', 0, 1420554873), (13, 4, 5, '38ffd~3a1f4~a3cde~7a90e~af099~cd11d~28752~67d77', 'חלומי', 1, 1420555402), (14, 4, 5, '9525d~a8682~1932e~85d96~b5830~03bf8~9c77d~7ebe2', 'סביח', 1, 1420555681), (15, 5, 6, '3784e~151da~7bfde~c12f6~a6c01~435e3~36e4e~ed4ab', 'ביקיני בוטו×', 1, 1420556203), (16, 1, 1, '1211b~9c86c~83024~9039c~43f8f~b639d~547eb~8cfac', 'שולחן', 0, 1421322108), (17, 1, 1, 'a8df0~23617~904f6~94880~99192~4781e~d8712~221a7', 'כס×', 1, 1421322943), (18, 1, 1, '0a492~ea76b~01948~061ab~a74a7~34f58~42dac~366de', 'חזותה', 0, 1421945914), (19, 1, 20, '2e3fe~200c7~fc8e1~17323~a9a1d~6f278~cbecf~cdd6e', '×’× - מעיל', 0, 1422351583), (20, 1, 20, '85cf8~71d5a~71c24~d9fc1~3a041~a1ac7~ab6ce~e1b1d', 'שפן של מורדי', 0, 1431527532), (21, 1, 0, 'af627~4e88e~13138~49be4~49bb7~dab92~df35e~14c97', '', 0, 1431589094);  -- --------------------------------------------------------  -- -- table structure table `pixel_tags` --  create table if not exists `pixel_tags` (   `id` int(11) not null auto_increment,   `project_id` int(11) not null,   `pixel` text not null,   `tag` text not null,   `name` text not null,   `date` bigint(20) not null,   primary key (`id`) ) engine=myisam  default charset=utf8 auto_increment=11 ;  -- -- dumping data table `pixel_tags` --  insert `pixel_tags` (`id`, `project_id`, `pixel`, `tag`, `name`, `date`) values (6, 0, 'd0950~15d68~354c8~5fbae~daf05~57eae~e633c~b929f', 'facebook-cpc', 'facebook-cpc', 1420041606), (7, 0, 'd0950~15d68~354c8~5fbae~daf05~57eae~e633c~b929f', 'google-cpc', 'google-cpc', 1420041621), (8, 0, '195a6~83005~4c660~62ef3~8c79a~a3e3b~b4182~da556', 'utm_google', 'גוגל גוגל', 1420554059), (9, 0, '9525d~a8682~1932e~85d96~b5830~03bf8~9c77d~7ebe2', 'utm-facebook', 'פייסבוק', 1420556056), (10, 0, 'a8df0~23617~904f6~94880~99192~4781e~d8712~221a7', 'h_test', '×—× ×” בדיקה', 1421323080); 

the query may be-

   select `pixel`.*,     `pixel_tags`.`name`as p_name,     `pixel_tags`.`tag` `pixel` join `pixel_tags` on `pixel`.`uuid`=`pixel_tags`.`pixel` `pixel`.`account_id`='1' union select `pixel`.*, null , null  `pixel`  `pixel`.`account_id`='1' , `uuid` not in (select distinct pixel pixel_tags); 

i setting example-

   mysql> select * pixel; +----+------------+------------+------+--------+------+------------+ | id | account_id | project_id | uuid | name   | type | date       | +----+------------+------------+------+--------+------+------------+ | 10 |          2 |          3 |    | test 1 |    0 | 1420553362 | | 11 |          2 |          3 | b    | test 2 |    1 | 1420553933 | |  9 |          1 |          1 | c    | test 3 |    1 | 1420041387 | +----+------------+------------+------+--------+------+------------+ 3 rows in set (0.00 sec)   mysql> select * pixel_tags; +----+------------+-------+-----------+----------+------------+ | id | project_id | pixel | tag       | name     | date       | +----+------------+-------+-----------+----------+------------+ |  6 |          0 | c     | facebook  | facebook | 1420553606 | |  7 |          0 | c     | google    | google   | 1420041621 | |  8 |          0 | b     | do_google | test     | 1420554059 | +----+------------+-------+-----------+----------+------------+ 3 rows in set (0.00 sec)  mysql>  select `pixel`.*,     ->     `pixel_tags`.`name`as p_name,     ->     `pixel_tags`.`tag`     -> `pixel`     -> join `pixel_tags` on `pixel`.`uuid`=`pixel_tags`.`pixel`     -> `pixel`.`account_id`='2'     -> union     -> select `pixel`.*, null , null     -> `pixel`     -> `pixel`.`account_id`='2' , `uuid` not in (select distinct pixel pixel_tags); +----+------------+------------+------+--------+------+------------+--------+-----------+ | id | account_id | project_id | uuid | name   | type | date       | p_name | tag       | +----+------------+------------+------+--------+------+------------+--------+-----------+ | 11 |          2 |          3 | b    | test 2 |    1 | 1420553933 | test   | do_google | | 10 |          2 |          3 |    | test 1 |    0 | 1420553362 | null   | null      | +----+------------+------------+------+--------+------+------------+--------+-----------+ 2 rows in set (0.00 sec) 

if still not getting may help. have removed account_id condition in both queries.

mysql>  select `pixel`.*,     ->     `pixel_tags`.`name`as p_name,     ->     `pixel_tags`.`tag`     -> `pixel`     -> join `pixel_tags` on `pixel`.`uuid`=`pixel_tags`.`pixel`     -> union     -> select `pixel`.*, null , null     -> `pixel`     -> `uuid` not in (select distinct pixel pixel_tags); +----+------------+------------+------+--------+------+------------+----------+-----------+ | id | account_id | project_id | uuid | name   | type | date       | p_name   | tag       | +----+------------+------------+------+--------+------+------------+----------+-----------+ |  9 |          1 |          1 | c    | test 3 |    1 | 1420041387 | facebook | facebook  | |  9 |          1 |          1 | c    | test 3 |    1 | 1420041387 | google   | google    | | 11 |          2 |          3 | b    | test 2 |    1 | 1420553933 | test     | do_google | | 10 |          2 |          3 |    | test 1 |    0 | 1420553362 | null     | null      | +----+------------+------------+------+--------+------+------------+----------+-----------+ 4 rows in set (0.02 sec)