MySQL连接查询帮助
基本上我有一个类似下面的 mysql 查询:
mysql_query("SELECT n.title, v.value FROM posts n INNER JOIN votes v ON n.id = v.id");
我需要做的是从 posts 表中获取标题并从 votes 表中获取当前的投票值。
目前,除非发生投票,否则投票不会存储到投票表中。 因此,所有 0 票的帖子都不在投票表中。
这会导致错误,即它试图获取标题和投票值,其中帖子 id = 投票值 id。 如果投票表中没有任何记录,则 value 应返回 NULL 或 0,但目前整个查询返回 null,所以我什至无法返回标题。
我怎样才能在一个查询中解决这一切? 我不想使用多个查询... 谢谢 :)
Basically I have a mysql query something like the following:
mysql_query("SELECT n.title, v.value FROM posts n INNER JOIN votes v ON n.id = v.id");
And what I need to do is to grab the title from the posts table and the current vote value from the votes table.
At the moment, votes aren't stored into the votes table unless a vote happens. So all posts with 0 votes aren't in the votes table.
This causes an error that it's trying to get the title AND vote value where the post id = the vote value id. If there isn't any record in the votes table, then value should return as NULL or 0 but at the moment the whole query returns as null so I can't even return the title.
How can I fix this all in one query? I don't want to have to use multiple queries...
Thanks :)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
使用左联接而不是内部
联接 内部联接要求联接两侧的表中的行相匹配。
左连接(或使用其全名的左外连接)从左表中选择所有行,然后从右表中匹配行,如果右表中没有匹配行则返回空值,就像您要求的那样。
(也有右外连接,但是通过改变ON子句中的条件可以达到相同的效果)
Use a left join instead of an inner
An inner join requires matching rows from the table on both sides of the join.
A left join (or left outer join to use its full name) selects all rows from the left table, and then matching rows from the right, returning null values if there are no matching rows in the right table, just like you are asking for.
(There are also right outer joins, but the same effect can be achieved by changing the conditions around in the ON clause)
SELECT n.title, v.value FROM posts as n, votes as v WHERE n.id = v.id
尝试一下。
SELECT n.title, v.value FROM posts as n, votes as v WHERE n.id = v.id
Try that.