I need to perform a database join where unfortunately the database structure is not in the first normal form. I cannot change the table structure/layout because this is application generated and I can only create read-only queries to fetch data for my own statistic component.
The schema looks like the following. There is the table services which consists several attributes where especially the attribute property is relevant. The following are example rows of the report table:
table: report
id | vehicles | ...
-------------------
1 | 1,2,3 | ...
2 | 2,3 | ...
3 | 2 | ...
The vehicle tables looks like this:
table: vehicle
vid | description
---------------
1 | Vehicle A
2 | Vehicle B
3 | Vehicle C
My goal ist to count how often a vehicle was part of a report. The desired result rows are:
desired result
description | count(*)
----------------------
Vehicle A | 1
Vehicle B | 3
Vehicle C | 2
The pseudo query for this would be something like:
SELECT v.description, Count(*)
FROM report r
JOIN vehicles v
ON v.vid IN(r.vehicles)
GROUP BY v.description;
Is there a MySQL way to handle this on the database side? Or do I have to select everything, explode the vehicles in the application login and perform the "join" outside the database?
I would prefer it to do this on the database side. I can only read both tables but can create views, if necessary (which I want to avoid, too, if possivle).
I found out that MySQL supports a FIND_IN_SET function where you can pass a string to search and a list to be searched.
Hence, a query which works is:
SELECT v.description as description, COUNT(*) as count
FROM report r
JOIN vehicles v
ON FIND_IN_SET(v.vid, r.vehicles)
GROUP BY (v.description);