|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
cant return specific info from data
I want to return all jobIDs (whether read or unread) but in case a jobID has both read and unread notes i just want to return the unread part? So in the case of below:
jobID 167 unRead jobID 168 unRead jobID 168 Read jobID 169 Read the output would be would be jobID 167 unRead jobID 168 unRead jobID 169 Read I'm sure its not too difficult but i've tried a lot.. to no avail see below (and I thinks its long winded for what i need) CREATE PROCEDURE spGetJobsByUnreadAndReadByUserID @UserID INT AS SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact, Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who FROM Master_Jobs INNER JOIN Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND Job_Assignments.UserID = Users.UserID WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND BackUp_Read = 'UnRead' ORDER BY BackUp_Read ASC SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact, Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who FROM Master_Jobs INNER JOIN Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND Job_Assignments.UserID = Users.UserID WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND BackUp_Read = 'Read' ORDER BY BackUp_Read ASC GO |
![]() |
| Viewing: Dev Articles Community Forums > Databases > Microsoft SQL Server > cant return specific info from data |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|