EN VI

SQL: join of 3 select queries from the same table (inner join and left join)?

How to SQL: join of 3 select queries from the same table (inner join and left join)

I'm using MySQL and I ran into a problem. I need to intersect the output of 3 queries done on the same table. That's the table:

CREATE TABLE posting (
    doc integer NOT NULL,
    word varchar(30) NOT NULL,
    freq integer NOT NULL,
    primary key (doc, word));

Then I inserted some values.

SELECT * FROM posting;

+-----+----------------+------+
| doc | word           | freq |
+-----+----------------+------+
|   1 | app            |   40 |
|   1 | classification |   20 |
|   1 | context        |   30 |
|   1 | information    |   15 |
|   1 | mobile         |   20 |
|   2 | app            |   40 |
|   2 | context        |   30 |
|   2 | discovery      |   15 |
|   2 | mobile         |   20 |
|   2 | recommandation |   30 |
|   2 | wall           |   15 |
|   3 | app            |   40 |
|   3 | discovery      |   10 |
|   3 | ideal          |   10 |
|   3 | mobile         |   20 |
|   3 | search         |   30 |
|   3 | server         |   25 |
|   4 | app            |   40 |
|   4 | killer         |   25 |
|   4 | mobile         |   20 |
|   4 | recommandation |   10 |
|   4 | search         |   30 |
|   5 | app            |   40 |
|   5 | beyond         |   20 |
|   5 | mobile         |   20 |
|   5 | model          |   15 |
|   5 | service        |   20 |
|   5 | share          |   30 |
|   5 | store          |   15 |
+-----+----------------+------+

And I made some basic queries.

SELECT DISTINCT doc FROM posting WHERE word LIKE 'mobile' AND freq >= 20;
+-----+
| doc |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+

So the set is M = {1, 2, 3, 4, 5}

SELECT DISTINCT doc FROM posting WHERE word LIKE 'context' AND freq >= 20;
+-----+
| doc |
+-----+
|   1 |
|   2 |
+-----+

So the set is C = {1, 2}

SELECT DISTINCT doc FROM posting WHERE word LIKE 'search' AND freq >= 20;
+-----+
| doc |
+-----+
|   3 |
|   4 |
+-----+

So the set is S = {3, 4}

SELECT DISTINCT doc FROM posting WHERE word LIKE 'app' AND freq >= 20;
+-----+
| doc |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+

So the set is A = {1, 2, 3, 4, 5}

Now I need to make 2 queries.

Intersect the first, the second and the third. --> M ∩ C ∩ S = {1,2,3,4,5} ∩ {1,2} ∩ {3,4} = {}

(SELECT DISTINCT doc FROM posting WHERE word LIKE 'mobile' AND freq >= 20
INNER JOIN
posting WHERE word LIKE 'context' AND freq >= 20)
INNER JOIN
posting WHERE word LIKE 'search' AND freq >= 20;

Intersect the first, the fourth and the complementary set of the second. --> M ∩ A ∩ complementaryC = {1,2,3,4,5} ∩ {1,2,3,4,5} ∩ {3,4,5} = {3,4,5}

(SELECT DISTINCT doc FROM posting WHERE word LIKE 'mobile' AND freq >= 20
INNER JOIN
posting WHERE wordLIKE 'apps' AND freq >= 20)
LEFT JOIN
posting WHERE word LIKE 'context' AND freq >= 20;

Solution:

For intersecting results across multiple conditions in a single table, INNER JOIN is used with the table itself based on matching document IDs and specific conditions for each intersecting set:

SELECT DISTINCT m.doc 
FROM posting m
INNER JOIN posting c ON m.doc = c.doc AND c.word = 'context' AND c.freq >= 20
INNER JOIN posting s ON m.doc = s.doc AND s.word = 'search' AND s.freq >= 20
WHERE m.word = 'mobile' AND m.freq >= 20;

For finding the complementary set of a given condition, use a LEFT JOIN combined with a WHERE ... IS NULL check to find entries that do not match the condition:

SELECT DISTINCT m.doc 
FROM posting m
INNER JOIN posting a ON m.doc = a.doc AND a.word = 'app' AND a.freq >= 20
LEFT JOIN posting c ON m.doc = c.doc AND c.word = 'context' AND c.freq >= 20
WHERE m.word = 'mobile' AND m.freq >= 20 AND c.doc IS NULL;