0

I have two tables with following data:

  CREATE TABLE host
    (`hostid` int, `datastoreid` int, `latency` int)
;
CREATE TABLE vm_datastores
    (`vmid` int, `hostid` int, `ds_id_list` varchar(200))
;
INSERT INTO host
    (`hostid`, `datastoreid`, `latency`)
VALUES
    (1, 11, 6),
    (1, 22, 7),
    (1, 33, 8),
    (1, 44, 9),
    (2, 11, 10)
;
INSERT INTO vm_datastores
    (`vmid`, `hostid`, `ds_id_list`)
VALUES
    (222, 1, '11,22'),
    (333, 1, '33'),
    (444, 1, '33'),
    (555, 2, '44'),
    (666, 1, '11')
;

Vm to host relationship is 1:m

vm to datastore relationship is 1:m

datastore to host relationship is m:n

Now with this, I want the target rows to be something like:

vmid hostid latency
 222    1   13 --------> this is the sum of latencies 6+7 because ds_id_list contains two values
 333    1   8
 444    1   8
 555    2   NULL -------> because for host 2, datastore 44 there is no entry in table
 666    1   6

How can I achieve this ? SQL fiddle with these values available here: http://sqlfiddle.com/#!9/2bcc9d/1

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Sach106
  • 63
  • 6
  • See: [is-storing-a-delimited-list-in-a-database-column-really-that-bad](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Paul Spiegel Jun 17 '20 at 18:45
  • It's a bad idea to put comma-separated values in SQL tables. But if you're stuck with it, use `FIND_IN_SET` in the `ON` condition of the `JOIN`. – Barmar Jun 17 '20 at 18:45

0 Answers0