SQL - Joining multiple records to one record
I've got a SQL Server database with the the following tables:
Client (ClientID, ClientName)
SalesAgent (AgentID, AgentName)
Item (ItemID, Description)
Purchase (PurchaseID, ClientID, ItemID, Price)
PurchaseSalesAgent (PurchaseID, AgentID)
Each purchase is only ever one item to one client but there can have been multiple agents involved. I want to return the following list of columns:
ClientName, Description, Price, Agents
Where Agents is the names of all the agents involved in the purchase. Either as a comma separated list or as multiple columns with one agent in each.
I'm looking for a way that's compatible with SQL Server 2000 but I'd also be interested in if there's a better way of doing i开发者_开发知识库t in SQL Server 2008.
In SQL 2008, you should use FOR XML PATH('')... something like:
select c.clientname, i.description, p.price,
stuff((select ', ' + a.AgentName
from SalesAgent sa join PurchaseSalesAgent psa on psa.AgentID = sa.AgentID
where psa.PurchaseID = p.purchaseID
for xml path('')),1,2,'') as Agents
from client c
join purchase p on p.clientid = c.clientid
join item i on i.itemid = p.itemid
;
In SQL 2000, there's no nice way of doing it.
Using Sql Server 2008, you can try
DECLARE @Client TABLE (ClientID INT, ClientName VARCHAR(10))
DECLARE @SalesAgent TABLE (AgentID INT, AgentName VARCHAR(10))
DECLARE @Item TABLE (ItemID INT, Description VARCHAR(10))
DECLARE @Purchase TABLE (PurchaseID INT , ClientID INT, ItemID INT, Price FLOAT)
DECLARE @PurchaseSalesAgent TABLE (PurchaseID INT, AgentID INT)
SELECT c.ClientName,
i.Description,
p.Price ,
(
SELECT sa.AgentName + ','
FROM @SalesAgent sa
WHERE sa.AgentID = pa.AgentID
FOR XML PATH('')
) Agents
FROm @Client c INNER JOIN
@Purchase p ON c.ClientID = p.ClientID INNER JOIN
@PurchaseSalesAgent pa ON p.PurchaseID = pa.PurchaseID INNER JOIN
@Item i ON p.ItemID = i.ItemID
EDIT
I made the assumption that Purchase TABLE has a column ItemID INT to link to the Items TABLE
精彩评论