Removing rows from SELECT based on columns in another table

Question

Basically, I am looking for a way to filter rows from SELECTone table based on specific values ​​in the rows of another table.

I am experimenting with the structure of the example below. I have a blog content table (one row per blog post) and another blog metadata table (one row for a key-value pair, each row with a column associated with a blog entry, many rows in a blog post). I want to print a line postsonly if metadatathere are no lines in where metadata.pid=posts.pid AND metadata.k='optout'. That is, for the structure of the example below, I just want to return a string posts.id=1.

(Based on what I tried) it JOINdoes not delete messages that have some metadata, where metadata.k='optout', because another line of metadata for this pidmeans that it does this in the results.

mysql> select * from posts;
+-----+-------+--------------+
| pid | title | content      |
+-----+-------+--------------+
|   1 | Foo   | Some content |
|   2 | Bar   | More content |
|   3 | Baz   | Something    |
+-----+-------+--------------+
3 rows in set (0.00 sec)

mysql> select * from metadata;
+------+-----+--------+-----------+
| mdid | pid | k      | v         |
+------+-----+--------+-----------+
|    1 |   1 | date   | yesterday |
|    2 |   1 | thumb  | img.jpg   |
|    3 |   2 | date   | today     |
|    4 |   2 | optout | true      |
|    5 |   3 | date   | tomorrow  |
|    6 |   3 | optout | true      |
+------+-----+--------+-----------+
6 rows in set (0.00 sec)

A subquery can give me the opposite of what I want:

mysql> select posts.* from posts where pid = any (select pid from metadata where k = 'optout');
+-----+-------+--------------+
| pid | title | content      |
+-----+-------+--------------+
|   2 | Bar   | More content |
|   3 | Baz   | Something    |
+-----+-------+--------------+
2 rows in set (0.00 sec)

... but using pid != any (...), gives me all 3 lines in the messages, because each pidhas a metadata line, where k!='optout'.

Answer 1

It looks like you want to do LEFT JOINand then check the results, in which the value of the joined table NULLindicates that such a joined record does not exist.

For instance:

SELECT * FROM posts 
LEFT JOIN metadata ON (posts.pid = metadata.pid AND metadata.k = 'optout')
WHERE metadata.mdid IS NULL;

posts, metadata k = 'optout'.

edit: , ; , () , .

edit 2: , LEFT JOIN JOIN ( INNER JOIN , MySQL).

, :

SELECT posts.*, metadata.mdid, metadata.k, metadata.v 
FROM posts 
INNER JOIN metadata ON posts.pid = metadata.pid;

SELECT posts.*, metadata.mdid, metadata.k, metadata.v 
FROM posts 
LEFT JOIN metadata ON posts.pid = metadata.pid;

:

+-----+-------+--------------+------+-------+-----------+
| pid | title | content      | mdid | k     | v         |
+-----+-------+--------------+------+-------+-----------+
|   1 | Foo   | Some content |    1 | date  | yesterday |
|   1 | Foo   | Some content |    2 | thumb | img.jpg   |
+-----+-------+--------------+------+-------+-----------+

, , "optout", . -, INNER JOIN:

SELECT posts.*, metadata.mdid, metadata.k, metadata.v 
FROM posts 
INNER JOIN metadata ON (posts.pid = metadata.pid AND metadata.k = "optout");

, :

Empty set (0.00 sec)

, LEFT JOIN:

SELECT posts.*, metadata.mdid, metadata.k, metadata.v 
FROM posts 
LEFT JOIN metadata ON (posts.pid = metadata.pid AND metadata.k = "optout");

:

+-----+-------+--------------+------+------+------+
| pid | title | content      | mdid | k    | v    |
+-----+-------+--------------+------+------+------+
|   1 | Foo   | Some content | NULL | NULL | NULL |
+-----+-------+--------------+------+------+------+

INNER JOIN a LEFT JOIN , INNER JOIN , BOTH . LEFT JOIN , , - . , , , .

, :

SELECT posts.*, metadata.mdid, metadata.k, metadata.v 
LEFT JOIN metadata ON (posts.pid = metadata.pid AND metadata.k = 'optout')
WHERE metadata.mdid IS NULL;

, :

+-----+-------+--------------+------+------+------+
| pid | title | content      | mdid | k    | v    |
+-----+-------+--------------+------+------+------+
|   1 | Foo   | Some content | NULL | NULL | NULL |
+-----+-------+--------------+------+------+------+

, ! - , , , , .

Answer 2

-

select  p.* 
from    posts p
where   NOT EXISTS (
                        select  pid 
                        from    metadata 
                        where   k = 'optout' 
                        and     pid = p.pid
                    )