I have a MySQL database with 3 tables,
computers (which has the following columns):
computersid (Unique Key)
Oh, my my my, what a mess of a database schema! This business of creating a one-to-many relationship by putting a list of values in a string makes life hard. What you really need is a separate
computer_software join table.
You have a saving grace here. You have a list of all the possible software items in their own table, and your string format is nicely delimited.
So, you can issue this SQL query (http://sqlfiddle.com/#!2/36a672/4/0) to JOIN together the
SELECT c.name, c.id, s.name AS sname,s.version FROM software AS s JOIN computers AS c ON c.softwareid LIKE CONCAT('%#',s.id,'#%') ORDER BY c.name, s.name, s.version
This will perform horribly. But, so what, it will still be faster than starting over with your design. It uses a whole lot of operations like this to match up the two tables.
'#1# #4# #53# #67# #32#' LIKE '%#53#%'
It uses the
CONCAT() function to create strings such as
So, I suggest you move forward by creating a view computers_software, with this command.
CREATE VIEW computers_software AS SELECT c.id AS computer_id, s.id AS software_id FROM software AS s JOIN computers AS c ON c.softwareid LIKE CONCAT('%#',s.id,'#%')
This view -- this virtual table -- this virtual join table -- contains pairs of
(computer_id, software_id) values. The presence of a row means that the specified computer has the specified software.
Then to determine what software is in what location, you do this (not debugged):
SELECT L.location, s.name, s.version, COUNT(*) AS copies FROM location AS L JOIN computers AS c ON l.id = c.locationid JOIN computers_software AS cs ON c.id = cs.computer_id JOIN software AS s ON cs.software_id = s.id GROUP BY L.location, s.name, s.version
Again, the trick to doing this in a sql-fluent way is to build the many-to-many relationship called computers_software. I'm suggesting you do it as a view for now.
Ordinarily an application developer would create a table like that simply because it works better. You might consider making that switch.