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

Question

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 Orders 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!

score 0 · Answer 1 · answered Jul 11 '11 at 15:27

0

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'

answered Jul 11 '11 at 15:27

Ovais Khatri

3,201
16
14

OP is not looking for data for a specific user, but rather having one record/line per user. Review and avoid downvotes. – Adriano Carneiro Jul 11 '11 at 15:36
Thanks Ovais but your query is the same as: SELECT ELECT Orders.OrderNo FROM Orders WHERE Orders.P_Id = I need to get all the Names, but have their OrderNo's in a list in a new column. – user839124 Jul 11 '11 at 15:38

Raj More · Accepted Answer · 2011-07-11T15:40:29.617

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)

score 0 · Answer 3 · edited May 23 '17 at 11:48

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:

score 0 · Answer 4 · answered Jul 11 '11 at 15:37

0

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.)

answered Jul 11 '11 at 15:37

MatBailie

83,401
18
103
137

That's what I said in my original question, I can loop over each record and do a query for each of my thousands of records, but I'm hoping there's a better way. :) – user839124 Jul 11 '11 at 15:49
You misread my answer. You don't loop over one set firing of multiple additional queries. You get Both sets back in one go, and loop over both of them together, at the same time : When the P_Id changes, it's a new person, who's name you can get by looping furhter into the Person's record set, until you find the matching P_Id. Because you can guarantee that both record sets are in the same order, this becomes trivial. – MatBailie Jul 11 '11 at 15:51

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

4 Answers4

Linked