带有多个连接的 SQL 语句 [英] SQL statement with several joins

问题描述

我必须诚实地告诉你，我不擅长数据库查询，这个问题可能很简单.

I must be honest and tell you that I am not good at database queries and this question is probably quite simple.

我有三张桌子

Post
    ID
    entry
Category
    ID
    name
CategoryBinding
    ID
    postID
    categoryID

我的正常查询是获取所有帖子及其所属类别

My normal query is to get all posts with the categories it is put into

SELECT * FROM `Post` AS `p` 
LEFT JOIN `CategoryBinding` AS `cb` ON p.ID = cb.postID 
LEFT JOIN `Category` AS `c` ON cb.categoryID = c.ID

返回的查询类似于:

ID    entry    ID    name    ID    postID    categoryID
1     entry1    1     php      1      1            1
1     entry1    2     asp      1      1            2

2     entry2    1     php      1      2            1

3     entry3    null  null    null    null        null

现在我想获取属于某个类别 ID 的所有帖子以及帖子所在的所有类别.
即我想获得与第一个查询相同的东西，但只获得属于某个类别的帖子.现在我只想获取属于 asp 类别的帖子.那是

Now I want to get all posts that belongs to a certain category ID with all the categories the post is put into.
I.E I want to get the same things as in the first query BUT only the posts that belong to a certain category. Now I only want to get the posts that belongs the category asp. That is

ID    entry    ID    name    ID    postID    categoryID
1     entry1    1     php      1      1            1
1     entry1    2     asp      1      1            2

你知道我该怎么做吗?

如果有人帮助我，我将非常感激，因为这更像是一个为我工作"的问题.

I will be very thankful if someone helps me since this is more like a "Do the work for me" question.

推荐答案

SELECT * 
FROM `Post` AS `p` 
LEFT JOIN `CategoryBinding` AS `cb` ON p.ID = cb.postID 
LEFT JOIN `Category` AS `c` ON cb.categoryID = c.ID
INNER JOIN `Post` AS `p2` ON p.id = p2.id
WHERE p.id in
(
    SELECT p2.id
    FROM `Post` as `p2`
    LEFT JOIN `CategoryBinding` AS `cb` ON p2.ID = cb.postID 
    LEFT JOIN `Category` AS `c` ON cb.categoryID = c.ID
    WHERE c.id = @SomeCategory
)

好的，最后一击.

这将返回重复的行，只需根据您的需要添加一个组即可.

This will return duplicate rows, just add a group by to whatever you want.

这篇关于带有多个连接的 SQL 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！