0

I am trying to join two tables together in a view. My first table combines two tables but now I am trying to join that table to a second table. I am trying to use a join to make sure the second table which contains roles which are performed by the user, matches up with the first table but if there are no records in the second table, I still want all the records from table 1. I know my explanation is a little confused, so my code is as follows:

CREATE VIEW `data` AS SELECT 
`information`.`username`,  `information`.`department`, 
`information`.`company`, `information`.`title`, 
`information`.`internal_number` AS `user_internal_phone`,
`information`.`external_number`AS `user_external_phone`, 
`information`.`mail`, `information`.`cn` AS `name`, `information`.`hours`, 
`information`.`languages`, `information`.`functions`, 
`information`.`service` AS `contact_point_name_one`, 
`information`.`subservice` AS `contact_point_name_two`, 
`information`.`internal_phone` AS `contact_internal_phone`, 
`information`.`external_phone` AS `contact_external_phone`, 
`information`.`keywords`, `information`.`description`, 
`information`.`provided_by`, `information`.`id`, 
GROUP_CONCAT(`staff_roles`.`role` SEPARATOR ', ') AS `roles`, 
`staff_roles`.`user` 
FROM `information` 
CROSS JOIN `staff_roles` ON `information`.`username` = `staff_roles`.`user`;

I get an error when I do an outer join and a cross join and an inner join both return rows where there is a row in both tables yet I want it to display the rows where there is nothing in the second table as well. The purpose of using a join is so that, where there is a match, the row from table 2 should match the row on table 1

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
wrichards0
  • 187
  • 4
  • 18

2 Answers2

2

Use LEFT JOIN. The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. Just replace your CROSS JOIN with LEFT JOIN

Battle Mage
  • 369
  • 2
  • 5
0

Simply use LEFT JOIN instead of CROSS JOIN:

CREATE VIEW `data` AS SELECT 
...
FROM `information` 
LEFT JOIN `staff_roles` ON `information`.`username` = `staff_roles`.`user`;

Take a look at http://www.w3schools.com/sql/sql_join_left.asp for further details about why LEFT JOIN.

MarcM
  • 2,173
  • 22
  • 32