create view help

[andydeans]

hi,

i have just applied a view to my database and when tested works fine if their is a value from the tasks and allrenewals tables of "clientID" or clients_ClientID however i need to be able to view the entries even if the clientid or clients_clientid from the 2 tables is null.

i cannot seem to get it right and have tried changing the join around to 3 different combinations.

hope someone can help urgently.

CREATE ALGORITHM=UNDEFINED DEFINER=`web1local-local`@`%` SQL SECURITY DEFINER VIEW `calendar` AS 
select `p`.`TaskID` AS `TaskID`,'tasks' AS `Task`,`c`.`ClientID` AS `ClientID`,`c`.`App1FirstName` AS `App1FirstName`,`c`.`App1LastName` AS `App1LastName`,
`c`.`App2FirstName` AS `App2FirstName`,`c`.`App2LastName` AS `App2LastName`,`u`.`UserID` AS `UserID`,`u`.`FirstName` AS `Firstname`,
`u`.`LastName` AS `Lastname`,`p`.`TaskTitle` AS `Title`,`p`.`TaskDueDate` AS `DueDate`,`p`.`Completed` AS `NPW`,`p`.`ProductAssociated` AS `Product`,`p`.`TaskDescription` AS `Description`  
from ((`clients` `c` join `tasks` `p` on((`c`.`ClientID` = `p`.`clients_ClientID`))) join `users` `u` on((`p`.`Tasks_UserID` = `u`.`UserID`)))
where `p`.`Completed` = 'No' 
union select `p`.`Product` AS `Product`,'allrenewals' AS `Review`,`c`.`ClientID` AS `ClientID`,`c`.`App1FirstName` AS `App1FirstName`,`c`.`App1LastName` AS `App1LastName`,
`c`.`App2FirstName` AS `App2FirstName`,`c`.`App2LastName` AS `App2LastName`,`u`.`UserID` AS `UserID`,`u`.`FirstName` AS `FirstName`,
`u`.`LastName` AS `LastName`,`p`.`ProductID` AS `Title`,`p`.`ReviewDate` AS `DueDate`,`p`.`NPW` AS `NPW`,`p`.`ProductID` AS `Product`,`p`.`Provider` AS `Description` 
from ((`clients` `c` join `allrenewals` `p` on((`c`.`ClientID` = `p`.`ClientID`))) join `users` `u` on((`p`.`UserID` = `u`.`UserID`)))
WHERE `p`.`NPW` = 'N';

thanks

[smantscheff]

Study the LEFT JOIN syntax and semantics. That's what you need.
http://dev.mysql.com/doc/refman/5.0/en/join.html

[andydeans]

hi,

thanks for this. i will check it out. in the meantime could you or anyone point me in right direction to where it would apply in this create view join?

i assume it is before the table that it does not matter has data in it or not?

cheers

[smantscheff]

Check it out first. I'm not prepared to spell my way through your view full of (problemwise) irrelevant fields, ugly apostrophes and ad libitum formatting (I suppose you are using Navicat?). Submit a clean test case with table structure and test data and I'm willing to delve deeper into it.
Apart from that, the idea of a left join is to have all rows from the first table and all matching data from the second one, with NULL values filled in for non-matched rows in the second table. I think that is what you want.

[andydeans]

Not Navicat. It was exported out of php my admin that way.