“可选”SQL查询中的WHERE子句-“Optional”WHEREclauseon(My)SQLquery

I'm having a bit of a problem with a SELECT query in MySQL and I'd be thankful for some pointers. Please feel free to point me towards an existing answer (if there is one and I missed it).

我对MySQL中的SELECT查询有一点疑问，我要感谢一些指针。请随时给我指出一个现有的答案(如果有的话，我错过了)。

The query is currently as follows:

目前查询如下:

SELECT e.*, ie.aaa, ue.bbb, ue.ccc
FROM ie
LEFT JOIN e ON ie.e_id = e.e_id
LEFT JOIN ue ON ie.e_id = ue.e_id
WHERE ie.other_id = ? AND ue.unrelated_id = ?
ORDER BY ...

There are three tables: ie, e and ue.

有三个表:ie、e和ue。

Tables ie and ue are relationships of e, and therefore contain foreign keys to it (e_id). ? represents an input parameter.

表ie和ue是e的关系，因此包含了它的外键(e_id)。吗?代表了一个输入参数。

The problem is the ue.unrelated_id = ? part. What I'm really trying to do here is:

问题出在ue。unrelated_id = ?部分。我真正想做的是:

• To return ue.ccc if and only if there is a ue relationship for unrelated_id = ?. If it doesn't exist, I want this field to be null.
• 返回问题。如果且仅当unrelated_id =有ue关系时，则为ccc。如果它不存在，我希望这个字段为空。
• Even if the ue relationship for unrelated_id = ? doesn't exist, this query should always return the remaining fields (ie is guaranteed to exist for other_id = ?).
• 即使unrelated_id的ue关系= ?该查询不存在，应该始终返回其余字段(即保证存在other_id = ?)。

Unfortunately, if I remove this where clause, I get ue.ccc for a "random" unrelated_id. But if I keep it, the query won't return any results at all if ue doesn't exist for this unrelated_id! I also tried adding OR ue.unrelated_id IS NOT NULL, but this makes the query return no results if the ue table is empty.

不幸的是，如果我删除这个where子句，我得到ue。一个“随机”的unrelated_id的ccc。但是如果我保留它，查询将不会返回任何结果，如果这个unrelated_id不存在ue !我也尝试了添加或ue。unrelated_id不是NULL，但如果ue表为空，这将使查询不返回任何结果。

Any ideas? Please drop a comment if you need further clarification. I should answer quickly in the next few hours.

什么好主意吗?如果需要进一步澄清，请留下评论。我应该在接下来的几个小时内迅速回答。

1 个解决方案

SELECT e.*, ie.aaa, ue.bbb, ue.ccc
FROM ie
LEFT JOIN e ON ie.e_id = e.e_id
LEFT JOIN ue ON ie.e_id = ue.e_id AND ue.unrelated_id = ?
WHERE ie.other_id = ? 
ORDER BY ...

SELECT e.*, ie.aaa, ue.bbb, ue.ccc
FROM ie
LEFT JOIN e ON ie.e_id = e.e_id
LEFT JOIN ue ON ie.e_id = ue.e_id
WHERE ie.other_id = ? AND (ue.unrelated_id IS NULL OR ue.unrelated_id = ?)
ORDER BY ...