CODEWITHSUNDEEP
sql-serversql-server-2008-r2subquery
JBurace
JBurace

Reputation: 5623

How to return multiple results in a subquery?

I have a visitor table that looks like this:

VID    Name      Address        DOB
001    Bob       101 St         03/05/2001
002    James     505 Rd         02/06/1985
003    Jill      201 Place      04/06/1970

And a visits table that looks like this:

VID    Date          Reason
001    05/07/2012    Family
001    06/01/2012    Family
003    03/02/2011    Other

I want to do a join query between these two on VID (which I already did) but also have the query show all (multiple) results from visits. So the results would look like:

Name         DOB              Visits
Bob          03/05/2001       05/07/2012 Family, 06/01/2012 Family
James        02/06/1985
Jill         04/06/1970       03/02/2011 Other

Is this possible? I attempted a subquery like:

SELECT Name, DOB, (SELECT Date, Reason FROM visits AS H WHERE H.VID=visitor.VID) As Visits FROM visitor;

But that gave me the obvious Subquery returned more than 1 value error. The data would be parsed by PHP, but I was hoping to accomplish this in one query.

Upvotes: 2

Views: 2226

Answers (2)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You can use a FOR XML PATH construct to concatenate your results into one column

SELECT   Name
         , DOB
         , STUFF((SELECT ', ' 
                         + CONVERT(VARCHAR(16), H.Date, 101)
                         + ' ' 
                         + H.Reason 
                  FROM   visits AS H 
                  WHERE  H.VID = visitor.VID 
                  FOR XML PATH('')), 1, 2, '')
FROM     visitor;

Note that various date formats are supported with convert. I have used 101 in the example wich translates to mm/dd/yyyy.

Have a look at MSDN for all styles supported by convert.

Upvotes: 4

rhoffbecl
rhoffbecl

Reputation: 76

You'll have to join the two tables and then do the processing in php. So

SELECT a.Name, a.DOB, b.Date, b.Reason FROM visitor a, visits b where a.VID=b.VID order by 1,2 ;

Then you just have to walk through the records and start a new line whenever one of the first two columns changes.

Upvotes: 1

Related Questions