How to merge recordset rows

[bikeman01]

I have a recordset which produces the following output from a mysql db:

record1 - Car1 - Ford - Fiesta
record2 - Car1 - Colour - Blue
record3 - Car1 - Price - 2995
recordx - Car2 - VW - Golf
recordy - Car2 - Colour - Red
recordz - Car2 - Price - 4995

I want to write an sql statement which combines all the rows relevant to each vehicle and output them in the same row e.g.:

Record1 - Car1 - Ford - Fiesta - Blue - 2995
Recordx - Car2 - VW - Golf - Red - 4995

Thanks for any assistance

[Newsgroup_User]

.oO(bikeman01)

>I have a recordset which produces the following output from a mysql db:
>
> record1 - Car1 - Ford - Fiesta
> record2 - Car1 - Colour - Blue
> record3 - Car1 - Price - 2995
> recordx - Car2 - VW - Golf
> recordy - Car2 - Colour - Red
> recordz - Car2 - Price - 4995

This looks a bit strange and not like a proper DB design. Can you post
some table details (the CREATE statement)?

> I want to write an sql statement which combines all the rows relevant to each
>vehicle and output them in the same row e.g.:
>
> Record1 - Car1 - Ford - Fiesta - Blue - 2995
> Recordx - Car2 - VW - Golf - Red - 4995

With a better DB design it would be as simple as

SELECT
id, brand, type, color, price, ...
FROM
...

For your current table a GROUP_CONCAT clause might help. See the MySQL
manual for details.

Micha

[bikeman01]

Thanks but I can't get it to work. I keep getting a MySQL error 1064 when I test in DW.
GROUP_CONCAT is not listed as a supported SQL keyword in my DW8 helpfile - is it possible to use this with DW8?

[Newsgroup_User]

.oO(bikeman01)

>Thank sbut I cant get it to work - is GROUP_CONCAT a supported keyword in DW8?

It's an SQL command, which is why I pointed you to the MySQL manual.
There you'll find explanations and examples.

But that was just a quick suggestion for your current database design.
There might be a better way, which is why I asked for table details.

Micha

[bikeman01]

I realise it is an sql command and I can use it... however my question related to DW8.
Now I have the syntax correct I see that it cannot be used and causes DW to generate an invalid sql syntax error.
I am still looking for a solution which will allow me to use the sql/php code writing functions within DW8.
ps the datbase is part of an opensource app so I cannot change it.