Help realize the task There is a table "Advertising". The Category field comes or in the form of iab format codes., Or in the form of text.

ID Type Category
1 picture Books
2 Video IAB25 Non-Standard Content
2 Video Social Networks
2 Video IAB26-1 illegal Content
2 Video IAB25-3

There is also a "Directory" table

Code Name
IAB25 Non-standard content
IAB25-1 Non-generated UGC
IAB26 illegal content
IAB26-1 illegal content
IAB25-3 Ozaneous Content

I need to calculate creative creatives for each category, but at the same time that the codes (IAB ...) are replaced with the values ​​from reference books. I tried to john the directory with the table and group the codes, but then everything that does not fall into the directory has the value of NULL.

For greater clarity. In the Name field, the values ​​from reference books. I make a grouping and get at the exit.

and instead of those values ​​that I miss in the directory I get NULL.

at the output I want to get such a result, where instead of IAB codes ... will be the corresponding values ​​from the reference books

Create Table Content (ID INT, TYPE VARCHAR (255), Category Varchar (255), Category_Value Varchar (255));
(1, 'picture', 'IAB25', 'coupe'),
(2, 'video', 'social', ''),
(2, 'video', 'IAB25-1', 'Convertible'),
(2, 'video', 'books', ''),
(2, 'video', 'IAB25', 'coupe');

How to group such a result?

ID Type Category Count
1 picture coupe 2
2 Video Social 1
2 Video Convertible 1
2 Video Books 1

and not such

ID Type Category Count
1 picture IAB25 2
2 Video IAB25-1 1
2 Video Books 1
2 Video Social 1

Please specify your specific problem or provide more detailed information about what you need. In the current form it is difficult to understand what you are asking.

Дух сообщества2021-09-14 12:48:22

Applied screenshots. Maybe it will be more clear

Александр Серебряков2021-09-14 13:03:13

Instead of screenshots, add the structure in the form of DML (i.e. create table ...) and an example of data (in the form of Insert Into ...). There you need to include a minimum of columns and data to demonstrate the problem. Also, for the specified data, you need to set the query parameters (if any) and the expected result.

Roman Konoval2021-09-14 14:22:15

tried to specify everything as you asked

Александр Серебряков2021-09-14 14:45:12