SQL SELECT DISTINCT but combine results from another table

Question

I'm not even sure what to search for so forgive me if this is already covered somewhere.

I have a SELECT statement that pulls data from several tables. As expected if I run this query I get a record for each record in the second table, like this.

|ID     |Name     |Assets
 ------------------------------------
|1      |Bob      |Car
|1      |Bob      |Bicycle
|1      |Bob      |House
|2      |Jane     |Car
|2      |Jane     |House
|3      |Peter    |Boat
|3      |Peter    |Car
|3      |Peter    |Motorcycle

What I want is something like this:

|ID     |Name     |Assets
 ------------------------------------
|1      |Bob      |Car,Bicycle,House
|2      |Jane     |Car,House
|3      |Peter    |Boat,Car,Motorcycle

I have been able to achieve this using PHP by simply hitting the database a second time for each record I get from the first table, but this has made my page slow as it's hitting the database some 40-60 times per page refresh.

Query 1:

SELECT ID,Name FROM People

Then Query 2 within a while loop:

<?php
while($owner_row=odbc_fetch_array($query1) {
$assets = odbc_exec($conn,"SELECT Asset FROM Assets WHERE Owner='$owner_row[Name]'");
$asset_array = odbc_fetch_array($assets);
$asset_string = implode(",",$asset_array);
}

I can then combine the Asset results from each owner into a string and display them as I like.

Is there a way to achieve this in a single query so that I only hit the database once and somehow combine those assets into a single value within the query itself?

http://stackoverflow.com/questions/491566/how-to-get-list-of-values-in-group-by-clause — Niladri Biswas, Sep 12 '12 at 03:24

score 3 · Answer 1 · edited May 23 '17 at 12:12

3

I am not sure what database you are hitting, but if you are using mySQL, there is a lovely function that does this called group_concat which works like this:

select 
    a.ID, 
    a.Name, 
    group_concat(b.Asset) as assetGroup 
from 
    people a
        join Assets b
            on a.ID=b.Owner 
group by 
    a.ID, 
    a.Name

Which would output EXACTLY:

|ID     |Name     |Assets
 ------------------------------------
|1      |Bob      |Car,Bicycle,House
|2      |Jane     |Car,House
|3      |Peter    |Boat,Car,Motorcycle

Edit: I am assuming that each record in the table people has at least one matching record in the Assets table - if not, use an outer join instead on the query.

Edit: I am not familiar with SyBase, but a quick search for a related function gave me bad news: MYSQL group_concat equivalent in Sybase ASE? there isn't a function like this in SyBase, you will have to write a stored procedure to get the results, but it indicates that you can still do this (albeit in a much more difficult manner) directly on the database.

If you don't want to write a stored procedure, you can still join the tables in the query, and then simply return many rows per ID into PHP and simply loop through them along the lines of:

$currentID=0;
$userAssets=array();
while($row=$AnotherRowFromTheDatabase)
{
    if($row['ID']==$currentID)
    {
        $userAssets[]=$row['Asset'];
    }
    else
    {
        $currentID=$row['ID'];
        // More stuff to move the array previously
        // and start a new Assets Array
    }
}

edited May 23 '17 at 12:12

Community

1
1

answered Sep 12 '12 at 01:35

Fluffeh

33,228
16
67
80

He's actually grabbing data from two tables – David Faber Sep 12 '12 at 01:36
@DavidFaber If he is still hitting a mysql, he can use the function in a multi-table query perfectly well. I have however updated the query to work with the table structure he describes. – Fluffeh Sep 12 '12 at 01:39
Hi Guys, As above. It's Sybase via an ODBC connection. – Barbs Sep 12 '12 at 01:45
Ok, Thanks Fluffeh. I might just stick to my nice slow PHP solution in that case. Thanks for trying. :) – Barbs Sep 12 '12 at 01:49
@Barbs I gave you a (probably) much quicker solution than making 40-60 queries against the database in my edit just now. – Fluffeh Sep 12 '12 at 01:50
@Barbs, Fluffeh is right.You should try to use the built in function of sybase for this which will be more efficient. The equivalent of Group_Concat (found in MySql) is LIST function found in Sybase. – Niladri Biswas Sep 12 '12 at 03:22

score 0 · Answer 2 · answered Sep 12 '12 at 03:19

0

Use Sybase LIST function here.

e.g.

SELECT ID,NAME,LIST(ASSETS)
FROM PEOPLE
GROUP BY ID,NAME

answered Sep 12 '12 at 03:19

Niladri Biswas

4,153
2
17
24

Hi Niladri, with a fairly simple query I get "Warning: odbc_exec() [function.odbc-exec]: SQL error: [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Function 'LIST' not found." so there may be a version issue with the database I'm accessing. I'm going to try some other methods (altering design etc to handle the data in a different way) and will put this method to sleep for now. Thanks for your assistance. – Barbs Sep 12 '12 at 03:55

score -1 · Answer 3 · answered Sep 12 '12 at 05:07

Please use the below SQL:

declare @tempUser  Table
(
ID int,
Name nvarchar(250),
AssetNAme nvarchar(250)
)
Insert into @tempUser values(1,'Bob','Car')
Insert into @tempUser values(1,'Bob','Bicycle')
Insert into @tempUser values(1,'Bob','House')
Insert into @tempUser values(2,'Jane','Car')
Insert into @tempUser values(2,'Jane','House')
Insert into @tempUser values(3,'Peter','Boat')
Insert into @tempUser values(3,'Peter','Car')
Insert into @tempUser values(3,'Peter','MotorCycle')


select  tt.ID,tt.Name,(select ITT.AssetNAme + ', ' as 'data()'  from @tempUser as ITT 
where ITT.ID =tt.ID for xml path('')) from @tempUser  as tt group by tt.ID,tt.Name

it's in sybase and not sql server – priyanka.sarkar Sep 12 '12 at 05:57 — priyanka.sarkar, Sep 12 '12 at 05:57

SQL SELECT DISTINCT but combine results from another table

3 Answers3