Sql Select from another table (loop?)

Question

My SQL skills aren't great hence the post.

I'm trying to get all the contact names based on a company out.

For example I have two statements:

Select Id, CompanyName, Address From Clients
Select ClientId, ContactName From Contacts

You may have many contacts to a single client

Result: (I need all the contact names in a single column)

ContactName         Company    Address
----------------------------------------
Johh, Steve         123 Comp   12345 Address
David,Mike, Sarah   44 Comp    111 Address

A working example would be very much appreciated.

What RDBMS? Doing this is non-standard-sql, but many RDBMS have functions to do this. — lc.
– lc., Commented Nov 8, 2012 at 18:20

Oleksandr Fedorenko · Accepted Answer · 2012-11-08 23:03:10Z

1

SELECT DISTINCT (
SELECT ISNULL(ct.ContactName, '') + ', '
FROM dbo.Clients cl JOIN dbo.Contacts ct ON cl.Id = ct.ClientId
WHERE cl.ID = cl2.Id
FOR XML PATH('')) AS ContactName, CAST(cl2.Id AS nvarchar(7)) + ' ' + cl2.CompanyName AS Company, Address
FROM dbo.Clients cl2
ORDER BY 2

Demo on SQLFiddle

edited Nov 8, 2012 at 23:03

answered Nov 8, 2012 at 22:57

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Prakash · Accepted Answer · 2012-11-08 19:05:49Z

Firstly build all the Contact Names for a Company into a Single Column. Assuming the database to be SQL Server, I'm using a Common Table Expression to store the single column contact list. Once the CTE is built, join it with the Clients table to get the ContactNames. FOR XML is used to concatenate rows.

WITH CTEContactList(ClientID,ContactNames) 
AS
(

 SELECT c1.ClientID,
        Names = SUBSTRING(( SELECT ', ' + c2.ContactName
                            FROM Contacts c2
                            WHERE c1.ClientID = c2.ClientID
                            FOR XML PATH ('')),3,8000 ))
 FROM Contacts c1
 GROUP BY c1.ClientID

 )

SELECT 
       cl.ID,
       cl.CompanyName,
       cl.Address,
       ctelist.ContactNames

FROM Clients cl
INNER JOIN CTEContactList ctelist
ON cl.ID = cteList.ClientID

marc_s · Accepted Answer · 2012-11-08 19:17:23Z

0

Sounds like you need to do a table join.

Example: two tables here

1. Person
2. Orders

Query:

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

edited Nov 8, 2012 at 19:17

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 8, 2012 at 18:24

alloyking

3513 silver badges10 bronze badges

4 Comments

Michael Fredrickson Over a year ago

This is what I thought at first too, but the OP wants to denormalize the results and roll all the contactNames as comma-separated in a single row.

Prakash Over a year ago

This won't work because the OP requires all contacts of the company in a single column.

alloyking Over a year ago

What is the associated link between the two tables? Is it ClientId from Contacts and Id from Clients? Anyway to show some example data from each table?

D-W Over a year ago

Clientid (Contacts) = Id (Clients)

user330315 · Accepted Answer · 2012-11-08 23:04:51Z

0

You didn't specify your DBMS, so I'm assuming PostgreSQL:

select string_agg(ct.contactName, ', '), cl.companyname, cl.address
from contacts ct
  join clients cl on cl.id = ct.clientId
group by cl.companyname, cl.address

answered Nov 8, 2012 at 23:04

user330315

Collectives™ on Stack Overflow

Sql Select from another table (loop?)

4 Answers 4

Comments

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related