3

I need to create VIEW in MySQL which can combine two tables in such way that for each row from first table there must be added columns from second table but as row data have to be formatted as multiple filds with multiple CSV for every single row.

My original approach is with MySQL VIEW but I was unable to find the way to show multiple row data from second table as CSV single cell data in view table.

Here are some examples:

1st table: gears
+------------+------------------------+-----------------+
| MainGearID | MainGearName           | MainGearType    |
+------------+------------------------+-----------------+
|          1 | Main Drive             | Spur            |
|          2 | Secondary Drive        | Spur            |
|          3 | Backup Secondary Drive | Hypoid          |
|          4 | AUX Drive              | Rack and pinion |
+------------+------------------------+-----------------+

2nd table: orbitinggears:
+----------+------------+--------------+--------------+
| OrbitaID | MainGearID | OrbitalType  | OrbitalValue |
+----------+------------+--------------+--------------+
|        1 |          1 | Spur         | 112          |
|        2 |          1 | Spur         | 280          |
|        3 |          2 | Spur         | 144          |
|        4 |          2 | Spur         | 248          |
|        5 |          3 | Helical      | 18           |
|        6 |          4 | Spur         | 144          |
+----------+------------+--------------+--------------+

Required View:
+------------+------------------------+-----------------+----------+---------+
| MainGearID | MainGearName           | MainGearType    | Spur     | Helical |
+------------+------------------------+-----------------+----------+---------+
|          1 | Main Drive             | Spur            | 112,280, |         |
|          2 | Secondary Drive        | Spur            | 144,248, |         |
|          3 | Backup Secondary Drive | Hypoid          |          | 18,     |
|          4 | AUX Drive              | Rack and pinion | 144,     |         |
+------------+------------------------+-----------------+----------+---------+

Does anybody have an idea how to create view in this way?

vzr
  • 105
  • 1
  • 7

1 Answers1

4

You can use GROUP_CONCAT with IF():

CREATE VIEW v6 AS 
SELECT a.MainGearID, a.MainGearName, a.MainGearType, 
   GROUP_CONCAT(IF(b.OrbitalType='Spur',b.OrbitalValue,null)) AS Spur,
   GROUP_CONCAT(IF(b.OrbitalType='Helical',b.OrbitalValue,null)) AS Helical
 FROM gears a
 JOIN orbitinggears b on b.MainGearID=a.MainGearID
 GROUP BY a.MainGearID;
vzr
  • 105
  • 1
  • 7
vhu
  • 12,244
  • 11
  • 38
  • 48
  • I tried with your approach. Now view table structure is good but output data is not. With this I get only 1 row with all spur-type orbiters inside that row, even with those orbiters which are not in contact with each other (in this case not connected via logical key MainGearID) :( – vzr Jul 24 '15 at 07:48
  • @Endlessless, I missed the `GROUP BY` part, which has now been added. – vhu Jul 24 '15 at 07:50
  • Thanks, now it's working like a charm. I just edited this query: added aliases and replaced USING with ON so now query in your answer can be easily adapted for general use if someone have similar requirement with different tables. – vzr Jul 24 '15 at 08:52