Create a view which joins on a comma separated value

Question

I request help for my homework in SQL.

I have a table with musical instruments:

id  instrument_name         instrument_at_shops
-----------------------------------------------
1   Electric Guitar         1;2;3;4;   
2   Midi Keyboard           1;3;
3   Tom Drum                2; 
4   Electric Guitar         1; 
5   Electric Guitar         0;

And a table with shops:

shop_id  shop_name  shop_address
-----------------------------------------------
1        Shop1      City1 Street 1, building 1                              
2        Shop2      City1 Street 5, building 4                              
3        Shop3      City2 Street 4, building 4                              
4        Shop4      City3 Street 10, building 7

In my musical instruments table I have several numbers which represent id's of the shops that have the particular instrument.

The problem is: how to create a view which contains a shop name and an instrument which is in this shop?

The logic is this:

SELECT shop_name, instrument_name 
FROM Shop 
CROSS JOIN Instrument
WHERE instrumet_at_shops CONTAINS shop_id

But I cannot create an appropriate query or view design.

Thank you in advance!

Gordon Linoff · Answer 1 · 2020-06-03T00:55:18.750

You should really put your effort into fixing the data model. It is broken, broken, broken:

Do not store multiple values in a string.
Do not store numbers are strings.
Foreign key relationships should be properly declared.
SQL has lousy string handling functionality.
SQL has a GREAT way to store lists -- it is called a table.

If you are stuck with a really, really bad data mode. . . . well, you should try to fix it. But if not, you can use string manipulations:

select i.*, s.shop_name
from instrument i join
     shops s
     on concat(';', i.instrument_at_shops, ';') like concat('%;', shop_id, ';%');

Unfortunately, there is really no way to optimize this query

score 0 · Answer 2 · answered Jun 03 '20 at 03:08

Ideally, if possible, you should normalise your data.

What is Normalisation (or Normalization)?

The most basic way you can do this, in your case, is to make the data in the [instrument_at_shops] column atomic, so it'll look something like this...

id  |  instrument_name  |  instrument_at_shops
-----------------------------------------------
1   |  Electric Guitar  |  1
1   |  Electric Guitar  |  2
1   |  Electric Guitar  |  3
1   |  Electric Guitar  |  4
2   |  Midi Keyboard    |  1
2   |  Midi Keyboard    |  3
...

There are other normal forms you can apply, which will help if you are dealing with a lot of data. But if this is the extent of your dataset, making these values atomic should be sufficient.

Once you've done this, you should be able to create a view with a lot more ease.

E.g.

SELECT      s.shop_name, i.instrument_name
FROM        Shop AS s
RIGHT JOIN  Instrument AS i
    ON      s.shop_id = i.instrument_at_shops ;
GO

score 0 · Answer 3 · answered Jun 03 '20 at 04:50

As suggested by @Gordon Linoff, you have problems in your data model, with multi-value attributes. But, with your current schema, below code will get you the result you need.

Go for normalized design, which will help in easier queries and better performance.

dbfiddle for reference

DECLARE @instrument table(id int, instrument_name varchar(50), instrument_at_shops varchar(100))

INSERT INTO @instrument values
(1   ,'Electric Guitar',         '1;2;3;4;'),
(2   ,'Midi Keyboard',           '1;3;'),
(3   ,'Tom Drum',                '2;'),
(4   ,'Electric Guitar',         '1;'),
(5   ,'Electric Guitar',         '0;');

DECLARE @Shops table(shop_id int, shop_name varchar(20))

INSERT INTO @shops
values (1,'shop1'), (2,'shop2'), (3,'shop3'), (4,'shop4');

SELECT s.shop_name, STRING_AGG(instrument_Name,',') as instruments
FROM @instrument
CROSS APPLY STRING_SPLIT(instrument_at_shops,';') as t
INNER JOIN @Shops as s
on s.shop_Id = t.value
group by shop_name

+-----------+-----------------------------------------------+
| shop_name |                  instruments                  |
+-----------+-----------------------------------------------+
| shop1     | Electric Guitar,Midi Keyboard,Electric Guitar |
| shop2     | Tom Drum,Electric Guitar                      |
| shop3     | Electric Guitar,Midi Keyboard                 |
| shop4     | Electric Guitar                               |
+-----------+-----------------------------------------------+

Create a view which joins on a comma separated value

3 Answers3