0

I'm having some kind of brain cramp here.

I have two tables:

MAIN: ID, Company Name, Address

SEGMENT ID, Company_id, Segment (company_id links to ID in MAIN table)

I want to export this into a csv. There are multiple segment rows associated with each Main row

Ideally my export will look like

1, Ford, 123 Main Street, cars, trucks, vans
2, Harley Davidson, 234 Elm Street, motorcycles
3, John Deer, 345 Oak Street, tractors, riding mowers

In other words each row will have a variable number of fields

I thought I could do something like

SELECT m.id, m.name, m.address, (select s.segment where m.id = s.company_id)
FROM main m, segment s

but that's putting all segments on every row. I think I need an addition to the where clause in my nested select to specify 'for the current row' but I'm not sure how to do that.

As is probably clear, I'm not very experienced with MySQL but sometimes the bus rolls over you even when you're not the right man for the job.

Update: I should have been more clear. My struggle was getting the appropriate result set, not the actual exporting to csv (I use phpMyAdmin so can cheese that part). Adding a Group By phrase, as illustrated by Raphaël Althaus below, did the trick.

Pete Smith
  • 33
  • 4
  • Have you tried the [usual method](http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format)? – tadman Jul 09 '14 at 16:16
  • @tadman how would you use this with "variable number of fields" (real question) ? – Raphaël Althaus Jul 09 '14 at 16:19
  • If you can get a result-set with the rows you want, converting that to CSV is just a matter of applying the correct output options. – tadman Jul 09 '14 at 16:27
  • @tadman of course it is. But how can a normal select return a variable number of columns ? That's the tricky point... – Raphaël Althaus Jul 09 '14 at 16:28
  • A normal select can't, it doesn't make any sense. Each row must contain an identical number of columns, and any sane CSV file should follow the same rules. A separate treatment with `GROUP_CONCAT` for what is apparently tags, which should be a single column, is probably not a bad plan, but the regular data is not special this way. – tadman Jul 09 '14 at 16:30

3 Answers3

4

For a csv export, you may concat all your values, and use a group_concat for segments.

If you want a csv with ; separator, you could do something like that :

select concat(m.id, 
              ';', 
              m.name, 
              ';',
              m.address, 
              ';', 
              group_concat(s.segment separator ';'))
from main m
join segment s on s.company_id = m.Id
group by m.id, m.name, m.address

If you need another separator, you'll have to change all occurences of ; in this query.

See this sample sqlfiddle for something like that.

CAUTION

Of course, as mentioned by tadman, this will fail miserably if one of your fields contains the separator.

Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122
  • CSV stands for *comma* separated values, and this completely fails to account for values that may have a comma or semicolon in them. – tadman Jul 09 '14 at 16:15
  • @tadman I always use csv with `;` separators. Should I change this to respect the acronym ? Anyway, does it change a lot to the query ? By the way, the answer explains how to change separator... for the second part, I agree. But I don't see how you could manage "variable number of fields" without a trick like that. – Raphaël Althaus Jul 09 '14 at 16:17
  • CSV with semicolons is not CSV in the slightest. – tadman Jul 09 '14 at 16:26
  • @tadman well, that's kind a detail I think. The other part (field containing the separator) is more problematic. – Raphaël Althaus Jul 09 '14 at 16:27
  • It's extremely common to have data with commas in it which is why the CSV specification, loose as it is, requires quoting values which contain them. This is, unfortunately, a lot harder to do than simply using `GROUP_CONCAT`. – tadman Jul 09 '14 at 16:28
  • It was the group by part of the query that was tripping me up. This solution works like a dream from me. Don't have enough rep to upvote it tho :(. – Pete Smith Jul 09 '14 at 16:53
  • @PeteSmith just accept the answer if it meets your needs ;) – Raphaël Althaus Jul 09 '14 at 17:13
  • @RaphaëlAlthaus Aha! I'm new here and eyes slid right past that ghosted checkmark. Got it now, and thanks again. – Pete Smith Jul 09 '14 at 19:09
0
SELECT m.*,s.segment FROM main AS m join segment AS s ON(m.id = s.company_id) WHERE 1

For adding additional condition

SELECT m.*,s.segment FROM main AS m join segment AS s ON(m.id = s.company_id) WHERE s.segment='car'
Kermit
  • 33,827
  • 13
  • 85
  • 121
Vaisakh Rajagopal
  • 1,189
  • 1
  • 14
  • 23
  • This one gives me multiple lines for each company. In other words if a given company is associated with 3 segments that company gets spit out 3 times, one row for each segment. – Pete Smith Jul 09 '14 at 16:52
0

If you are using the MySQL Query Browser there is a built in option to export to a .csv - after you get your SELECT performing as you wish - and you don't have to do the CONCAT that Raphael mentioned - just get the order correct. Good luck.

user3741598
  • 297
  • 1
  • 12