-1

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

Votes + Comments
Andydeans should learn how to state a problem. It would clear his mind.
2
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by andydeans
0

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

-1

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.