Turn rows into a string 1

[0ddball]

Ok - bit of a weird one but we're working on the principle that I don't pay for database processing time so I should use as much as I can get.

I want to run a query which returns one column (ignore errors here - I've written it quickly - I'm ok with the basic stuff)

SELECT `name` 
FROM `company_does_sport`, `sports` 
WHERE `company_does_sport`.`sportId` = `sports`.`sportId` 
AND `company_does_sport`.`companyId` = ?cId

And turn the results of that query into a comma delimited list which I can then add as a column in a query on the company table.

Now I know I could do this in my code but I'd rather like the database server to do it for me.

Any ides?


Yet another unchecked rambling brought to you by:
Oddball

 

[KarveR]

Largely it depends on what version of mysql, if your version supports subqueries,

insert into table_1 (select stuff from table_2 where ... );

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[0ddball]

Yeah - we're using 5.0. It's an internal production server and this is for an intranet project so no problems there. Use the new stuff


Yet another unchecked rambling brought to you by:
Oddball

 

[KarveR]

Should be fine then, use your select but wrap it with an INSERT INTO table ();

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

*note, sorry - standard insert syntax applies, specify fields etc

INSERT INTO TABLE (field1,field2,field3...) VALUES (SELECT ....);

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

And turn the results of that query into a comma delimited list

use the GROUP_CONCAT function

r937.com | rudy.ca

 

[KarveR]

Depends how you read it...

I read it as:

item1,item2,item3 <- column delimited list to be inserted into a database...

insert into blah (stuff1,stuff2,stuff3..) values (item1,item2,item3....)

But I see your point also


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[0ddball]

Ok - I'm still having one or two problems so I've posted what I've got. As you can see the query is actually rather complicated in a 'lots of fields' kind of way. Now - this query seems to work but I need it to return rows even when there are no rows returned by the subquery.

What I don't know is how to create one of these `full joins` using a subquery. Here is my code

SELECT
	`companies`.`companyId`,
	`companies`.`name`,
	`industries`.`name`,
	`notes`,
	`isContacted`,
	`address1`,
	`address2`,
	`address3`,
	`city`,
	`county`,
	`postcode`,
	`countryoptions`.`name`,
	`website`,
	`sportLists`.`sportList` AS `sl`
FROM
	`companies`,
	`industries`,
	`countryoptions`,
	(SELECT 
		GROUP_CONCAT(`sports`.`name` SEPARATOR ', ') AS `sportList` 
	FROM 
		`company_does_sport`, 
		`sports` 
	WHERE 
		`company_does_sport`.`sportId` = `sports`.`sportId` 
	AND 	`company_does_sport`.`companyId` = '35c33922-e1e9-4cf1-8bcd-d7c8c68a174f' 
	GROUP BY 
		`companyId`) 
	AS `sportLists`
WHERE
	`companies`.`companyId` = '35c33922-e1e9-4cf1-8bcd-d7c8c68a174f'
AND	`companies`.`industryId` = `industries`.`industryId`
AND 	`countryoptions`.`countryId` = `companies`.`country`

What I want to get out is either all the company info and NULL in the `sl` or the relevent info in `sl`. I'm sure you can work out what I mean


Yet another unchecked rambling brought to you by:
Oddball

 

[r937]

select companies.companyId
     , companies.name
     , industries.name
     , notes
     , isContacted
     , address1
     , address2
     , address3
     , city
     , county
     , postcode
     , countryoptions.name
     , website
     , (
       select group_concat(sports.name
                 separator ', ') as sportlist
         from company_does_sport
       inner
         join sports
           on sports.sportId
            = company_does_sport.sportId
        where company_does_sport.companyid
            = companies.companyid
       ) as sl
  from companies
inner
  join industries
    on industries.industryid
     = companies.industryid
inner
  join countryoptions
    on countryoptions.countryid
     = companies.country
 where companies.companyId
     = '35c33922-e1e9-4cf1-8bcd-d7c8c68a174f'

r937.com | rudy.ca

 

[0ddball]

Crikey. It doesn't seem like it should be so comlicated but what the hey - I'll give it a whirl and you'll get a star if it works.

Thanks for all your help folks!


Yet another unchecked rambling brought to you by:
Oddball