mysql - 我的加入在应该工作时失败了

Question

这只是一个简单的连接，但为什么会失败？

  "id"  "borrowMax" "holder"    "category"  "country"
  "1"   "2"         "0"         "10"        "US"
  "2"   "0"         "1"         "0"         "US"

我实际上是在尝试将这两个 sql 合并为一个。

select id, holder from mytable where id = 2
select borrowMax from mytable where id = (holder from the above select) and category = 10

我是怎么做到的

SELECT col1.id, col1.holder, col2.borrowMax
FROM collection_db col1
JOIN collection_db col2
ON col2.holder = col1.id
WHERE col1.id = 2 //2 = Value supplied by me
AND col2.category = 10

我想要得到的结果:

"id"    "holder"    "borrowMax"
"2" "1" "2"

Answer 1

我认为结果是正确的

col2.holder = col1.id will give 

"id"  "borrowMax" "holder"    "category"  "country"
 1         ....     1  ...........................

那么另一个条件是 col1.id = 2

这里没有2的记录。所以你没有得到任何记录