In php, im making a search function in Notification2 table. Notification has the DocumentID(DocID) which must be inner join to Documents table. Documents has a colunm called PreparedBy. In PreparedBy column, it only consist the UserID of the user so Documents table must be inner join to Users table because Im searching for the FirstName combined with the last name of the UserId in PreparedBy. In Users table, to get the full name i must combine the FirstName and Last Name
Ive come up to this but does not give the the results i want. Refer to screen shots..
dbo.Notification2.id as NotificationsID,
dbo.Documents.PreparedBy as PreparedByID,
(Users.FirstName+' '+Users.LastName) as PreparedByName,
dbo.Notification2.DocID as NotificationDocumentID
(Notification2 inner join Documents on dbo.Notification2.DocID = dbo.Documents.DocID)
inner join dbo.Users ON dbo.Documents.PreparedBy = dbo.Users.UserID
where (Users.FirstName+' '+Users.LastName) like ''