How to make a selection of posts from the database?

Good morning! Please help me to choose the posts from the database with the condition. 2 days scratching their head not understand how to win a query) I Have 3 tables:
1474f74133794e5b824eb73e25aebc07.png
I need to choose the posts as shown in the picture:
1fc3ae1216c947f4bd65db9ee29b4abf.png
I get to sample all the posts which pivotal table has the status checked without conditions. But you must choose the post as shown in the picture, for example a post with id 39 if the column cheked-out status is checked in all rows. This means that all users have approved the post and it should show. Please tell me how to do such a condition in the query?
September 19th 19 at 00:22
2 answers
September 19th 19 at 00:24
In the join I'm not strong but does not tell where the checked and notChecked it is desirable to replace the 1 and 0 will increase speed.
And in posts you cannot add a column user_id - a lot of problems I think would ))
Column you can add but it does not solve the problem. I just have specifics when you create a post assigned to several users to test it. You can see in pivotal table, their is a different number. So I have no way to record a bunch of users assigned to check the post in 1 user_id field as you suggested. And what about the replacement status checked thanks I will consider and change everything. Win the main query with this condition. - giuseppe.Beah commented on September 19th 19 at 00:27
Speed does not particularly increase, the gain will be only in the network transmission, if it is not planned to split the database and engine are on different server, then certainly, it is only necessary on the "—Āheked" to hang up the index and change the type to SET - Mittie22 commented on September 19th 19 at 00:30
: I'm with you Anton I'll do that! Is it possible in the sample in addition to the id of the posts are self posts? - giuseppe.Beah commented on September 19th 19 at 00:33
over, add the left join post p on p.id = pu2.post_id - Mittie22 commented on September 19th 19 at 00:36
Well, SELECT change SELECT DISTINCT p.*, although not very efficient,
better
SELECT * FROM posts p WHERE p.id IN(
(SELECT DISTINCT
pu2.post_id
FROM (SELECT
t.post_id,
COUNT(1)
 FROM (SELECT
pu.post_id,
cheked-out
 FROM post_user pu
 GROUP BY pu.post_id,
 pu.cheked-out) t
 GROUP BY t.post_id
 HAVING COUNT(1) = 1) t2
 LEFT JOIN post_user pu2
 ON pu2.post_id = t2.post_id

WHERE pu2.cheked-out = "checked"))
- Mittie22 commented on September 19th 19 at 00:39
Strangely with such a request raises the error #1054 - Unknown column 'posts.city_id' in 'order clause' - giuseppe.Beah commented on September 19th 19 at 00:42
: Throw your request, I have this error no - Mittie22 commented on September 19th 19 at 00:45
Make the request as you indicated.

SELECT * FROM posts p WHERE p.id IN(
(SELECT DISTINCT
pu2.post_id
FROM (SELECT
t.post_id,
COUNT(1)
FROM (SELECT
pu.post_id,
cheked-out
FROM post_user pu
GROUP BY pu.post_id,
pu.cheked-out) t
GROUP BY t.post_id
HAVING COUNT(1) = 1) t2
LEFT JOIN post_user pu2
ON pu2.post_id = t2.post_id

WHERE pu2.cheked-out = "checked"))

https://drive.google.com/file/d/0B8tEf6QRhrDMc09Dc... - giuseppe.Beah commented on September 19th 19 at 00:48
: You're now working on a local PC, put a normal IDE though www.devart.com/ru/dbforge/mysql/studio it's free. while the functionality is huge. - Mittie22 commented on September 19th 19 at 00:51
try it, I think that phpMyAdmin chudit - Mittie22 commented on September 19th 19 at 00:54
: Yes use on the local computer database. Now just shake this program the other day a little tried it and was shocked at its functionality! It is possible to create really complex queries!!! Thank you again for your help and support!!! - giuseppe.Beah commented on September 19th 19 at 00:57
: Yes nezachto) - Mittie22 commented on September 19th 19 at 01:00
September 19th 19 at 00:26
select * from posts p
left join post_user us
 on us.post_id = p.id
left join users u
 on us.user_id = u.id
where us.checked="checked"
In this request writes an error: #1054 - Unknown column 'us.checked' in 'where clause' - giuseppe.Beah commented on September 19th 19 at 00:29
It is clear that You have a table error in the word "cheked-out" or fix the request
on
where us.cheked-out="checked"
or a table - Mittie22 commented on September 19th 19 at 00:32
: Yes you are right sorry didn't notice the typo)) Now there is a selection of posts wherever the status is checked. And I have 12 posts displayed)) and need to display 3 posts as indicated in the picture, I marked them with red. As now appears, too, so I did. But how to make the conditions to selected posts marked with the part I'll never know. - giuseppe.Beah commented on September 19th 19 at 00:35
Ah, didn't read your last sentence in the post, and can make the dump tables row by 100 to play?

Well, vskidku, so be it
select * from ( select us.post_id,count(1) c from posts p
 left join post_user us
 on p.id = us.post_id
 group by us.checked,p.post_id ) t
left join post_user us
 on us.post_id = t.post_id
left join users u
 on us.user_id = u.id
where t.c=1
- Mittie22 commented on September 19th 19 at 00:38
: Yes of course the base test that is a reference) https://drive.google.com/file/d/0B8tEf6QRhrDMZjZ2d... - Mittie22 commented on September 19th 19 at 00:41
Here you go
SELECT DISTINCT
pu2.post_id
FROM (SELECT
t.post_id,
COUNT(1)
 FROM (SELECT
pu.post_id,
cheked-out
 FROM post_user pu
 GROUP BY pu.post_id,
 pu.cheked-out) t
 GROUP BY t.post_id
 HAVING COUNT(1) = 1) t2
 LEFT JOIN post_user pu2
 ON pu2.post_id = t2.post_id
WHERE pu2.cheked-out = "checked"
- giuseppe.Beah commented on September 19th 19 at 00:44
You just guru)) the query was built just class!!! Thank you very much!!! Going to display posts)) you are such complex queries do if not a secret? Or do you have extensive experience in working with databases? - Mittie22 commented on September 19th 19 at 00:47
Well experience with DBMS I have probably 7-9 years, just do not remember, and that such queries to build just need to think of how DBMS))))
look,
first query
SELECT
pu.post_id,
cheked-out
 FROM post_user pu
 GROUP BY pu.post_id,
 pu.cheked-out

we have sgruppirovany post and type
we base will return a list of posts, and merge the same types into one line.
there were 300 entries was only 33,
wrapping it in single query that selected only those posts that have the same type what we say COUNT == 1
well, and then just select the desired type in this case, only "checked" - giuseppe.Beah commented on September 19th 19 at 00:50
You have really vast experience in working with databases. I'm just a video course look at the databases. Just recently began to surface the difficulties in the design of complex databases. When, for example, make the app more complicated blog the problems begin how to organize the structure of the database)) Here is affected by the lack of experience and therefore decided to study this issue in more detail) - Mittie22 commented on September 19th 19 at 00:53

Find more questions by tags MySQL