Concatenate Fields from Related Tables

[AshleyRickards]

Hello,

It's been awhile but I need to join 2 tables and pull say Orders from one and Items from the other and concatenate the values.

 

[lespaul]

If you provide some more details about your table structure and what you want to return (perhaps some sample data or result set) it will be much easier to assist you.

 

[AshleyRickards]

Sure Thanks.

ORDERS
ID Customer
1 Company A
2 Company B
3 Company C



ITEMS
ID OID Desc
1 1 Item 1
2 1 Item 2
3 1 Item 3


SELECT ORDERS AND ALL ITS ITEMS


RESULTS:

1, "Company A", "Item 1, Item 2, Item 3"


Concatinate all rows of a sub table.


Ashley L Rickards
SQL DBA

 

[lespaul]

SELECT ORDERS.ORDERID, CUSTOMER, ITEMS.DESC
FROM ORDERS
INNER JOIN ITEMS ON ORDERS.ORDERID = ITEMS.OID

Will get you:

1 CustomerA Item1
1 CustomerA Item2
1 CustomerA Item3

in order to get all the results on a single line would require some coding. There's another thread from the past day or two where someone was trying to do something similar, if I find the thread I'll post it back here.

Leslie