Csv Export Of 1->m Tables

[blinks2]

In the following code, searchkey is a 1->1 table, being joined with two 1->MANY tables (searchkey_keyword and searchkey_author). Resulting output consists of multiple lines. I'd like to output this to an Excel file, but have each record (i.e. each instance in the searchkey table) on a single line, with the output from each 1->MANY tables in a single cell, separated by a delimiter. Is this possible from SQLyog? Or alternative method?

TIA

SELECT

searchkey.rk_id AS rk_id,

searchkey.rk_title AS rk_title,

searchkey_keyword.rk_keyword AS rk_keyword,

searchkey_author.rk_author AS rk_author

FROM search_key

LEFT JOIN searchkey_keyword ON rk_id=rk_keyword_id

LEFT JOIN searchkey_author ON rk_id=rk_author_id

WHERE rk_id=”12345″;

[peterlaursen]

You will need to write a SELECT query where the server returns the result as you want it. Then you can use SQLyog CSV-port from RESULT tab.

I do no fully understand your example. I would need some small dumps and a more detailed description (best a screenshot from Excel) of what you want.

But a simple hint would be to to use concat_ws() function to generate a single result-column with data from more table-columns. Try to compare output of two simple statements

Code:

SELECT coloum1, column2 FROM sometable

and

Code:

SELECT concat_ws(',',column1,column2) FROM sometable

MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

[blinks2]

Peter, thank you for your reply. A simpler example and resulting output is –

SELECT

searchkey.rk_id AS rk_id,

searchkey.rk_title AS rk_title,

searchkey_author.rk_author AS rk_author,

searchkey_keywords.rk_keywords AS rk_keywords

FROM searchkey

LEFT JOIN searchkey_author ON rk_id=rk_author_id

LEFT JOIN searchkey_keywords ON rk_id=rk_keywords_id

WHERE rk_id=”12345″;

yields the following output –

rk_id rk_title rk_author rk_keywords

12345 Why physics is the best Brown, B Australia

12345 Why physics is the best Brown, B physics

12345 Why physics is the best Smith, J Australia

12345 Why physics is the best Smith, J physics

whereas the output I want is –

rk_id rk_title rk_author rk_keywords

12345 Why physics is the best Brown, B; Smith, J Australia; physics

[blinks2]

Problem solved –

group_concat(DISTINCT searchkey_author.rk_author ORDER BY searchkey_author.rk_author DESC SEPARATOR ';') AS rk_author

Thanks!

[Author]

Posts