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:
I need to choose the posts as shown in the picture:
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?
I need to choose the posts as shown in the picture:
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?
asked September 19th 19 at 00:22
2 answers
answered on 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 ))
And in posts you cannot add a column user_id - a lot of problems I think would ))
answered on 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' 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
or a table commented on September 19th 19 at 00:32
on
where us.cheked-out="checked"or a table 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. 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
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: Yes of course the base test that is a reference) https://drive.google.com/file/d/0B8tEf6QRhrDMZjZ2d... 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" 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? 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
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" commented on September 19th 19 at 00:50
look,
first query
SELECT
pu.post_id,
cheked-out
FROM post_user pu
GROUP BY pu.post_id,
pu.cheked-outwe 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" 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) commented on September 19th 19 at 00:53
Find more questions by tags MySQL
better
- Mittie22 commented on September 19th 19 at 00:39
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