Link to home
Start Free TrialLog in
Avatar of NickMalloy
NickMalloyFlag for United States of America

asked on

Help with a script with one to many table.

I have three tables I am joining.

tblRequest
tblAudit
tblQuestions

tblRequest has a field named RequestID(PK). The other two have multiple records with an RequestID. I had my script setup where I would show one record from tblRequest and the latest auditID from tblAudit. Everything worked great! I now determined I need to grab all the records from tblQuestions where an Assignment ID = 11 and also show them as well in this list. Right it is showing the same RequestID's multiple times. I would simply like to see the record once with an 11 if it exist otherwise a null for AssignmentID What am I doing wrong?  

SELECT     c.RequestID, c.EmployeeID, c.Emailaddress, c.BestContact, c.UserGuid, c.RequestDate, c.PhoneRequest, c.UserName, c.LastEdited, c.LastEditedBy,
                      c.DateSubmittedIn, c.SubmittedBy, c.PhoneCall, d.StatusID, d.AuditID, c.DisplayName, e.AssignmentID
FROM         dbo.tblRequest AS c INNER JOIN
                      dbo.tblAudit AS d ON c.RequestID = d.RequestID LEFT OUTER JOIN
                      dbo.tblQuestions AS e ON c.RequestID = e.RequestID
WHERE    (d.AuditID IN
                          (SELECT     MAX(AuditID) AS Expr1
                            FROM          dbo.tblAudit AS a
                            WHERE      (RequestID = c.RequestID))) and (e.AskQuestionID IN
                          (SELECT     MAX(AskQuestionID) AS Expr1
                            FROM          dbo.tblQuestions AS a
                            WHERE      (RequestID = e.RequestID) AND (AssignmentID = '11')))
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

That looks contradictory to me:

>> I need to grab all the records from tblQuestions where an Assignment ID = 11 and also show them as well in this list. << 
>> I would simply like to see the record once with an 11 if it exist otherwise a null for AssignmentID What am I doing wrong?  <<
Avatar of NickMalloy

ASKER

the main table is tblRequest. tblAudit could have mutilple RequestID's. I grab the latest record in my where clause so that view only shows the requestID one time. I don't have multiple requestID's showing. Now I want to add the questions table and show an 11 in AssignmentID if there is one and have it null otherwise. Again, not seeing multiple requestID's.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial