I have seeen some examples of what I am trying to do using COALESCE and FOR XML (seems like the better solution). I just can't quite get the syntax right.
Here is what I have (I will shorten the fields to only the key ones):
Table Fields
------ -------------------------------
Requisition ID, Number
IssuedPO ID, Number
Job ID, Number
Job_Activity ID, JobID (fkey)
RequisitionItems ID, RequisitionID(fkey), IssuedPOID(fkey), Job_ActivityID (fkey)
I need a query that will list ONE Requisition per line with its associated Jobs and IssuedPOs. (The requisition number start with "R-" and the Job Number start with "J-").
Example:
R-123 | "PO1; PO2; PO3" | "J-12345; J-6780"
Sure thing Adam!
Here is a query that returns multiple rows. I have to use outer joins, since not all Requisitions have RequisitionItems that are assigned to Jobs and/or IssuedPOs (in that case their fkey IDs would just be null of course).
SELECT DISTINCT Requisition.Number, IssuedPO.Number, Job.Number
FROM Requisition
INNER JOIN RequisitionItem on RequisitionItem.RequisitionID = Requisition.ID
LEFT OUTER JOIN Job_Activity on RequisitionItem.JobActivityID = Job_Activity.ID
LEFT OUTER JOIN Job on Job_Activity.JobID = Job.ID
LEFT OUTER JOIN IssuedPO on RequisitionItem.IssuedPOID = IssuedPO.ID
