S S S S - 2 months ago 11
SQL Question

Creating a view with join on multiple labels

I have the following tabels as an example

Raquet

--------------------
| id | Name |
| 1 | Raquet 1 |
| 2 | Raquet 2 |
--------------------


String

--------------------
| id | Name |
| 1 | String 1 |
| 2 | String 2 |
| 3 | String 3 |
| 4 | String 4 |
--------------------


Label

--------------------
| id | Name |
| 1 | Label 1 |
| 2 | Label 2 |
| 3 | Label 3 |
| 4 | Label 4 |
--------------------


Raquet_Labels

--------------------
| r_id | l_id |
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
--------------------


String_Labels

--------------------
| s_id | l_id |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
| 3 | 3 |
| 4 | 3 |
| 4 | 4 |
--------------------


I want to match all the list all the Raquet, String combinations where the String should have all the labels of the corresponding Raquet.

For example

Raquet 1 has labels 1, 2
Raquet 2 has labels 3, 4

String 1 has labels 1, 2, 3
String 2 has labels 1, 2
String 3 has labels 1, 3
String 4 has labels 3, 4

From the above -
String 1 has all the labels that are part of Raquet 1
String 2 has all the labels that are part of Raquet 1
String 3 doesn't have the labels that are part of any Raquet
String 4 has all the labels that are part of Raquet 2

Following is the expectation

--------------------
| r_id | s_id |
| 1 | 1 |
| 1 | 2 |
| 2 | 4 |
--------------------


SQL to create the required data.

create table raquet(id integer not null, name varchar(256) not null);
insert into raquet values (1,'Raquet 1'), (2,'Raquet 2'), (3,’Raquet 3’);
create table string(id integer not null, name varchar(256) not null);
insert into string values (1,'String 1'), (2,'String 2'), (3,'String 3'), (4,'String 4');
create table label(id integer not null, name varchar(256) not null);
insert into label values (1,'Label 1'), (2,'Label 2'), (3,'Label 3'), (4,'Label 4');
create table raquet_labels(r_id integer not null, l_id integer not null);
insert into raquet_labels values (1,1), (1,2), (2,3), (2,4);
create table string_labels(s_id integer not null, l_id integer not null);
insert into string_labels values (1,1), (1,2), (1,3), (2,1), (2,2), (3,1), (3,3), (4,3), (4,4);

Answer

This is a task that looks easy on first glance, but isn't. Here is one way:

  1. We create of the records we would like to see, i.e. all raquet labels combined with all strings. This is a cross join of table Raquet_Labels with table String. That is all matches we would like to see, and now we must test whether the strings really fulfill this.
  2. We outer join the existing racket labels, so we get all matches and all mismatches.
  3. We aggregate our data: For a raquet and a string: do we only have matches or not? We can do this with count; count(*) counts all records, but count(<column from the outer-joined table>) only counts matches. Either the counts are equal (every label matched) or not.

Query:

select rl.r_id, s.id as s_id
from raquet_labels rl
cross join string s
left join string_labels sl on sl.s.id = s.id and sl.l_id = rl.l_id
group by rl.r_id, s.id
having count(*) = count(sl.s_id);

And here is another way:

  1. We create a set of all possible combinations of rackets and strings. That is a cross join of the two tables.
  2. For each such combination we check if we find a racket label without the matching string label. If not so, there is no racket label that has no match, so we keep the pair.

Query:

select r.id as r_id, s.id as s_id
from racket r
cross join string s
where not exists
(
  select l_id from raquet_labels rl where rl.r_id = r.id
  except
  select l_id from string_labels sl where sl.s_id = s.id
);
Comments