SQL and Coldfusion left join tables getting duplicate results as a list in one column

lets say I have two tables: Persons (P_Id, Name) and Orders (O_Id, OrderNo, P_Id)... I want to do a left join which would be:

SELECT Persons.Name, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.Name

This would give me multiple rows for the same Person with different OrderNo. What I really need to be able to get is one row for each person and all the OrderNo belonging to that person in a list.

With coldfusion I can query the Persons table, loop over each record and for each record do a query on Ord开发者_如何学运维ers and get the results for that P_Id and put it in a list and add it as a new called "OrdersList" to my first query. But I have thousands of records which would mean doing thousands of queries! There must be a better way to do this!

Look up FOR XML - that will let you pivot the order numbers.

Check this out

With Person AS
(
    Select 1 PersonId, 'John' PersonName
    Union Select 2, 'Jane'
),
Orders As
(
    Select 1 OrderId, 1 PersonId, Convert (DateTime, '1/1/2011') OrderDate
    Union Select 2, 1 , Convert (DateTime, '1/2/2011')
    Union Select 3, 1 , Convert (DateTime, '1/5/2011')
    Union Select 4, 1 , Convert (DateTime, '1/7/2011')
    Union Select 5, 1 , Convert (DateTime, '1/9/2011')
    Union Select 6, 2 , Convert (DateTime, '1/2/2011')
    Union Select 7, 2 , Convert (DateTime, '1/5/2011')
    Union Select 8, 2 , Convert (DateTime, '1/7/2011')
)
Select PersonId, 
(
    Select STUFF((SELECT  ', ' + cast(O.OrderId as nvarchar)
    FROM Orders O
    Where 1=1
        And O.PersonId = Person.PersonId
    FOR XML PATH('')), 1, 1, '') 
) OrderList
From Person

The output is

PersonId    OrderList
----------- -----------------------
1            1, 2, 3, 4, 5
2            6, 7, 8

(2 row(s) affected)

try this:

 declare @tmp table(PersonName varchar(100),OrderNo bigint)

  insert into @tmp(PersonName)
   select Person.Name from Persons
   where Person.Name = 'Edward'

    insert into @tmp(OrderNo)
    SELECT  Orders.OrderNo FROM Persons 
    JOIN Orders ON Persons.P_Id=Orders.P_Id 
    where  Persons.Name = 'Edward'

If you are using SQL Server, you can use FOR XML PATH:

select 
    p.ID,
    p.Name 
    stuff((select ', ' + convert(varchar, o.OrderNo)
           from Orders o where o.P_Id = p.P_Id
           for xml path('')),
          1,2,'') [Orders]
from Persons p

The STUFF function is to get rid of the final ', ' which will be appended at the end.

You can also see another examples here:

• SQL same unit between two tables needs order numbers in 1 cell
• SQL Query to get aggregated result in comma seperators along with group by column in SQL Server

What I really need to be able to get is one row for each person and all the OrderNo belonging to that person in a list.

No, you don't, you really, really don't.

You can do what you've already done, and loop through the results. When the first column changes value, you know you've moved on to a new person. One issue may be that you're returning the name again and again and again, once for each order id. In which case return two record sets, each in the same order...

SELECT Persons.P_Id, Persons.Name
FROM Persons
ORDER BY Persons.Name

SELECT Persons.P_Id, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.Name

(You don't need a LEFT JOIN now, because you can infer a person with no orders as you loop through the two record sets.)