I'm working on a forum and, like in every forum, there are threads and responses. These are the tables I use in the database to store them:
我正在一个论坛上工作,就像在每个论坛中一样,都有线程和响应。这些是我在数据库中用来存储它们的表:
post
------------
id
user_id
time
type
effective_id
thread
------------
id
update_time
text
response_number
deleted
response
------------
id
container_id
text
deleted
The post.type is an enum('thread', 'response'), and the post.effective_id has to match a thread.id or a response.id according to what the post.type indicates.
post.type是一个枚举('thread','response'),post.effective_id必须根据post.type指示的内容匹配thread.id或response.id.
As you can see, I'm factorizing everything that the threads and responses have in common.
正如您所看到的,我正在分解线程和响应的共同点。
Here's the problem I've encountered: I want to determine if a given post was deleted (having it's id as information) in a single query and without moving the deleted field to the post table.
这是我遇到的问题:我想确定一个查询中是否删除了某个帖子(将其id作为信息),而不将删除的字段移动到post表中。
These would be the queries I'd use if I knew beforehand if the given id belongs to a thread or a response.
如果我事先知道给定的id是属于线程还是响应,那么这些将是我使用的查询。
SELECT thread.deleted
FROM post INNER JOIN thread ON post.effective_id = thread.id
or
SELECT response.deleted
FROM post INNER JOIN response ON post.effective_id = response.id
But how can I say in SQL, "if the post.type is thread then INNER JOIN with thread and get the deleted field, if post.type is response then INNER JOIN with response and get the delete field."? I'd need some kind of dynamic "if"
但是我怎么能在SQL中说“如果post.type是线程然后INNER JOIN与线程并获取删除字段,如果post.type是响应然后INNER JOIN与响应并获得删除字段。”?我需要一些动态的“如果”
Is this even possible to do with the specified conditions?
这甚至可以用指定的条件吗?
Thanks!
2 个解决方案
#1
2
Take a look at the case in the below query :
看看下面的查询中的情况:
SELECT CASE
WHEN post.type = 'thread' THEN thread.deleted
WHEN post.type = 'response' THEN response.deleted
ELSE 'default'
END
FROM post
LEFT JOIN thread ON post.effective_id = thread.id
LEFT JOIN response ON post.effective_id = response.id
#2
1
Use two left joins:
使用两个左连接:
SELECT IFNULL(thread.deleted, response.deleted) AS deleted
FROM post
LEFT JOIN thread ON post.effective_id = thread.id
AND post.type = 'thread'
LEFT JOIN response ON post.effective_id = response.id
AND post.type = 'response'
#1
2
Take a look at the case in the below query :
看看下面的查询中的情况:
SELECT CASE
WHEN post.type = 'thread' THEN thread.deleted
WHEN post.type = 'response' THEN response.deleted
ELSE 'default'
END
FROM post
LEFT JOIN thread ON post.effective_id = thread.id
LEFT JOIN response ON post.effective_id = response.id
#2
1
Use two left joins:
使用两个左连接:
SELECT IFNULL(thread.deleted, response.deleted) AS deleted
FROM post
LEFT JOIN thread ON post.effective_id = thread.id
AND post.type = 'thread'
LEFT JOIN response ON post.effective_id = response.id
AND post.type = 'response'