I’m struggling as a junior to create a view based on 5 tables: Country, Business, SubBusiness, Role and Type
x
CREATE VIEW my_view AS
SELECT DISTINCT `a`.`country`, `b`.`business`, `c`.`sub_business`, `d`.`role`, `e`.`type`
FROM ((`country` `a` LEFT JOIN `business` `b` on ((`a`.`username`=`b`.`username`)))
LEFT JOIN `sub_business` `c` on ((`a`.`username`=`c`.`username`)))
LEFT JOIN `role` `d` on ((`a`.`username`=`d`.`username`)))
LEFT JOIN `type` `e` on ((`a`.`username`=`e`.`username`)));
It works for joining 3 tables, after that I receive a syntax error. And also, because USERNAME is in all the tables is the a way to make more performant? Thank you in advance for all the inputs !
Advertisement
Answer
You have wrong (and useless) sequence of ()
CREATE VIEW my_view AS
SELECT DISTINCT `a`.`country`, `b`.`business`, `c`.`sub_business`, `d`.`role`, `e`.`type`
FROM `country` `a`
LEFT JOIN `business` `b` on `a`.`username`=`b`.`username`
LEFT JOIN `sub_business` `c` on `a`.`username`=`c`.`username`
LEFT JOIN `role` `d` on `a`.`username`=`d`.`username`
LEFT JOIN `type` `e` on `a`.`username`=`e`.`username`;