英文:
How to make LEFT JOIN query using sqlx?
问题
我想从joke表中获取笑话，其中笑话在jokevote表中被点赞。
以下是查询语句：
var jokes []model.Joke
err := shared.Dbmap.Select(&jokes, "SELECT *
FROM joke
LEFT JOIN jokevote
WHERE joke.user_id=?
AND jokevote.user_id=?
AND jokevote.vote=1
", userId, userId)
if err != nil {
fmt.Println("%v \n", err)
}
但是我得到了这个错误：
Error 1064: You have an error in your SQL syntax; check the manual
that corresponds to your MariaDB server version for the right syntax
to use near 'WHERE joke.user_id=? AND jokevote.user_id=? AND
jokevote.vote=1' at line 1
我还尝试过：
err := shared.Dbmap.Select(&jokes, "SELECT *
FROM joke
LEFT JOIN jokevote
WHERE joke.user_id=jokevote.user_id
AND jokevote.vote=?
", 1)
但是得到了相同的错误。
我查看了文档，但没有找到任何关于这种连接的示例。
所以想知道如何修复它。
英文:
I'd like to get results from joke table where jokes are upvoated in
jokevote table.
Here is the query:
var jokes []model.Joke
err := shared.Dbmap.Select(&jokes, " SELECT *
FROM joke
LEFT JOIN jokevote
WHERE joke.user_id=?
AND jokevote.user_id=?
AND jokevote.vote=1
", userId, userId)
if err != nil {
fmt.Println("%v \n", err)
}
But I get this error:
> Error 1064: You have an error in your SQL syntax; check the manual
> that corresponds to your MariaDB server version for the right syntax
> to use near 'WHERE joke.user_id=? AND jokevote.user_id=? AND
> jokevote.vote=1' at line 1
I have also tried:
err := shared.Dbmap.Select(&jokes, " SELECT *
FROM joke
LEFT JOIN jokevote
WHERE joke.user_id=jokevote.user_id
AND jokevote.vote=?
", 1)
And got the same error.
I looked at the docs and could not find any example of such joins.
So wondering how can I fix it.
答案1
得分: 2
以下是翻译好的内容：
示例可以在Mariadb文档中找到。你所缺少的是on子句，它告诉Mariadb如何连接这两个表。你不能使用旧式的连接语法，将连接条件放在where子句中，而应该使用left join。
SELECT * FROM joke
LEFT JOIN jokevote ON joke.user_id=jokevote.user_id
WHERE jokevote.vote=?
英文:
The examples would me in the mariadb docs. What you are missing is the on clause that tells mariadb how to join the 2 tables. You cannot use the old-school join syntax with left join putting the join criteria into the where clause.
SELECT * FROM joke
LEFT JOIN jokevote ON joke.user_id=jokevote.user_id
WHERE jokevote.vote=?
答案2
得分: 1
SQL查询应该像这样：
"SELECT * FROM joke LEFT JOIN jokevote ON joke.user_id=jokevote.user_id WHERE jokevote.vote=?"
英文:
Sql query should be like:
"SELECT * FROM joke LEFT JOIN jokevote ON joke.user_id=jokevote.user_id WHERE jokevote.vote=?"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论