Mysql fetch query?

Question

MySQL

Good day.
I'm sitting on the request and the brain is already starting to blunt.
There are two tables - table `p` with articles and `pt` with labels. Each article has many tags in the `pt` table, they are linked by the `id_p` key in each table.
Tables `p`:
SELECT * FROM `p`<br/>
Result:

id_p | p_title<br/>
________________<br/>
1 | статья 1<br/>
2 | статья 2<br/>
3 | статья 3<br/>
4 | статья 4<br/>
.....<br/>
262 | статья 262<br/>

Table `pt:
SELECT * FROM `pt` WHERE `id_p` = 262<br/>
Result:

id_pt | id_p | pt_val<br/>
_______________<br/>
7403 | 262 | 556<br/>
7402 | 262 | 535<br/>
7401 | 262 | 4448<br/>
7400 | 262 | 4447<br/>
7399 | 262 | 4445<br/>

Question. How to write a WHERE clause so that when selecting from the `p` table, all records with labels 4447 and 4448 are selected, but not with 556 and 535?
I do:

SELECT * FROM `p`<br/>
INNER JOIN `pt`<br/>
WHERE `id_p` = 262 AND `pt_val` = 4447 AND `pt_val` = 4448<br/>

The result is zero rows. But if you do it through OR, then you get two records with the corresponding labels:

SELECT * FROM `p`<br/>
INNER JOIN `pt`<br/>
WHERE `id_p` = 262 AND (`pt_val` = 4447 OR `pt_val` = 4448)<br/>

Need to get one. But with a large number of records in the `p` table and labels in `pt`, the result includes records with `p` that should not be there

Answer 1

An old problem and an old direct solution - nested queries.
select * from p where
id_p in (select distinct(id_p) from pt andpt_val in (4447 ,4448))
and
id_p not in (select distinct(id_p) from pt andpt_val in (556, 535)
) values ​​can be added to taste.
But this thing will be heavy ... but without changing the structure, there is no other way.

Answer 2

Can't see the forest for the trees? :) GROUP BYnot canceled the same kind.
SELECT p.* FROM p
LEFT JOIN pt ON p.id_p=pt.id_p
WHERE p.id_p=262 AND pt_val IN (4447, 4448)
GROUP BY p.id_p

Answer 3

This is probably the easiest way to join the pt table twice.

Answer 4

And in general, it is customary to designate columns like this, otherwise you don’t understand anything:
Table articles
id
title
Table article_tags
id
article_id
value
SELECT DISTINCTROW
articles.id,
articles.title,
tags1.id as article_id (if you need to remove fields with the same name from different tables )
FROM articles
LEFT JOIN article_tags AS tags1 ON articles.id = tags1.article_id
LEFT JOIN article_tags AS tags2 ON articles.id = tags2.article_id
WHERE articles.id = 262
AND tags1.value = 4447
AND tags2.value = 4448

Answer 5

SELECT * FROM `p`
INNER JOIN `pt`
WHERE `id_p` = 262 AND `pt_val` IN (4447,4448
)