找到你要的答案

Q:How does one do a SQL join with multiple comparisons?

Q:如何做一个SQL多比较加入?

OK, so I'm using MySQL 5.16, and I'm attempting to join two very similar tables of patrons based on their ID and whether the amount of money they spent is different between the tables. I'm essentially trying to look at the amounts in table_a and find all the corresponding entries in table_b that either don't exist yet or are different.

Here is my example code:

SELECT  table_a.*
FROM    table_a LEFT JOIN
        table_b   ON  table_a.id = table_b.id
AND     table_a.total_spent != table_b.total_spent

The error I'm getting is a syntax error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'total_spent != table_b.total_spent' at line 5

It's probably something obvious that I'm just missing, but nobody online that I've found has asked this specific question. The desired results of this query is a list of all the entries in table_a that either don't have a corresponding entry in table_b, or do, but that entry has a different total_spent.

EDIT:

OK, I messed up the LEFT JOIN. Here is the code now:

SELECT  table_a.*
FROM    table_a LEFT JOIN
        table_b   ON  table_a.id = table_b.id
WHERE   table_a.total_spent != table_b.total_spent
OR      (table_a.total_spent IS NOT NULL AND table_b.total_spent IS NULL)
OR      (table_a.total_spent IS NULL AND table_b.total_spent IS NOT NULL)

好的,所以我使用MySQL 5.16,我试图加入两个非常相似的表的顾客根据他们的ID和他们是否花的表之间不同的金额。我基本上是想看看table_a金额和找到所有相应的条目table_b,要么不存在或是不同的。

下面是我的例子代码:

SELECT  table_a.*
FROM    table_a LEFT JOIN
        table_b   ON  table_a.id = table_b.id
AND     table_a.total_spent != table_b.total_spent

我得到的错误是语法错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'total_spent != table_b.total_spent' at line 5

这可能是很明显的,我只是失踪,但没有在线,我发现已经问了这个具体问题。这个查询的结果是一个列表中的所有条目table_a要么没有table_b,对应的条目或做,但这项都有一个不同的total_spent。

编辑:

好吧,我搞砸了左键。现在是代码:

SELECT  table_a.*
FROM    table_a LEFT JOIN
        table_b   ON  table_a.id = table_b.id
WHERE   table_a.total_spent != table_b.total_spent
OR      (table_a.total_spent IS NOT NULL AND table_b.total_spent IS NULL)
OR      (table_a.total_spent IS NULL AND table_b.total_spent IS NOT NULL)
answer1: 回答1:

The syntax error is most likely a spelling error. Check your table definitions.

However:

I'm essentially trying to look at the amounts in table_a and find all the corresponding entries in table_b that either don't exist yet or are different.

Your query doesn't do this.

SELECT  table_a.*
FROM    table_a LEFT JOIN
        table_b   ON  table_a.id = table_b.id
WHERE   table_a.total_spent != table_b.total_spent
OR      (table_a.total_spent IS NOT NULL AND table_b.total_spent IS NULL)
OR      (table_a.total_spent IS NULL AND table_b.total_spent IS NOT NULL)

The first condition tests if both tables have a value and they're not equal.

The second condition tests if table_a has a value and table_b doesn't. It will also test indirectly if table_b has no record at all, so adding OR table_b IS NULL is actually redundant.

The third condition tests if table_a is missing a value and table_b has the value. I can't be certain if the last entry is useful for you. It depends if table_a.total_spent is nullable.

You will not see any values from table_b where no record from table_a is present since you're running a LEFT JOIN.

语法错误很可能是拼写错误。检查表定义。

然而:

I'm essentially trying to look at the amounts in table_a and find all the corresponding entries in table_b that either don't exist yet or are different.

您的查询不这样做。

SELECT  table_a.*
FROM    table_a LEFT JOIN
        table_b   ON  table_a.id = table_b.id
WHERE   table_a.total_spent != table_b.total_spent
OR      (table_a.total_spent IS NOT NULL AND table_b.total_spent IS NULL)
OR      (table_a.total_spent IS NULL AND table_b.total_spent IS NOT NULL)

第一个条件测试两个表是否有值,它们不相等。

第二条件试验,如果table_a具有价值和table_b没有它也将测试间接如果table_b根本没有记录,所以添加或table_b无效其实是多余的。

第三个条件测试,如果table_a缺少价值和table_b有价值。我不能肯定最后一个条目是否对你有用。这取决于table_a.total_spent是空。

你不会看到任何值从table_b哪里table_a没有记录是因为你正在运行一个左连接。

answer2: 回答2:

Try excluding the table b id null condition.

SELECT  table_a.*
FROM    table_a LEFT JOIN table_b   ON  table_a.id = table_b.id
WHERE   table_a.total_spent <> table_b.total_spent

尝试排除表B ID空条件。

SELECT  table_a.*
FROM    table_a LEFT JOIN table_b   ON  table_a.id = table_b.id
WHERE   table_a.total_spent <> table_b.total_spent
answer3: 回答3:

I just tried your statement, see here http://sqlfiddle.com/#!9/6fa5a/1 and it basically works. However, you will never get a result, since the LEFT JOIN condition table_a.id = table_b.id is explicitely excluded in the WHERE clause. This allows only records, where the table_b can not be linked. Therefore table_b.total_spent will never be available for a comparison (even with a negative result).

Edit

I have been fiddling a bit further. Maybe this is what you wanted: http://sqlfiddle.com/#!9/11541d/2 ? I added a bit more useful data - different ids! - and removed the second condition of the WHERE clause.

Rearranged my fiddle (http://sqlfiddle.com/#!9/9df69/2) so it can now accommodate your query in the original form:

SELECT  table_a.*
FROM    table_a LEFT JOIN
        table_b ON  table_a.id = table_b.id
WHERE   table_b.id IS NULL
AND     table_a.total_spent != table_b.total_spent

And there I do not get a syntax errror (no result either, but that is explained above).

2. Edit

OK, that is quite a different WHERE clause. You can do it shorter with

SELECT  table_a.*
FROM    table_a LEFT JOIN
        table_b   ON  table_a.id = table_b.id
WHERE   ifnull(table_a.total_spent,-1) != ifnull(table_b.total_spent,-1)

The -1 in the ifnull() represents a value that should otherwise not exist as a 'legal' value, but this makes it possible to get away with just one comparison.

我只是想你的话,在这里看到的HTTP:/ / sqlfiddle。COM / #!9 / 6fa5a / 1和它的基本工作原理。然而,你永远不会得到一个结果,由于左连接条件table_a.id = table_b.id明确地排除在WHERE子句。这只允许记录,其中table_b无法联系。因此table_b.total_spent永不可用作比较(甚至是负的结果)。

编辑

我一直摆弄远一点。也许这就是你想要的:HTTP:/ / sqlfiddle。COM / #!9 / 11541d / 2?我增加了一些有用的数据-不同的IDS!-删除WHERE子句的第二条件。

重新安排我的小提琴(HTTP:/ / sqlfiddle。COM / #!9 / 9df69 / 2)所以现在可以容纳你的原表中查询:

SELECT  table_a.*
FROM    table_a LEFT JOIN
        table_b ON  table_a.id = table_b.id
WHERE   table_b.id IS NULL
AND     table_a.total_spent != table_b.total_spent

我没有得到一个语法错误(没有结果,但这是上面的说明)。

2. 编辑

好吧,这是一个完全不同的条款。你可以做得更短

SELECT  table_a.*
FROM    table_a LEFT JOIN
        table_b   ON  table_a.id = table_b.id
WHERE   ifnull(table_a.total_spent,-1) != ifnull(table_b.total_spent,-1)

在ifnull() - 1值应不作为法律价值的存在,但它可以拿一个比较。

answer4: 回答4:

While I'm not actually sure of the proper syntax (I would try using "<>" or something equivalent if it did not work, I think there is a logic error. You are attempting a LEFT JOIN on the table where an ID exists; but then looking for where the ID is null. I believe the logic flaw lies in the WHERE clause; not the AND clause.

SELECT  table_a.*
FROM    table_a 
LEFT JOIN table_b ON table_a.id = table_b.id
WHERE   table_b.id IS NULL
AND     table_a.total_spent != table_b.total_spent

Just my thoughts :) -Josh

虽然我并不确定的正确语法(我会尝试使用“<;>;”或其他类似的东西如果它不工作,我觉得这是一个逻辑错误。您试图在id存在的表上进行左连接,但然后查找id为空的位置。我相信逻辑缺陷在于WHERE子句,而非子句。

SELECT  table_a.*
FROM    table_a 
LEFT JOIN table_b ON table_a.id = table_b.id
WHERE   table_b.id IS NULL
AND     table_a.total_spent != table_b.total_spent

Just my thoughts :) -Josh

answer5: 回答5:

Have you tried: table_a.total_spent <> table_b.total_spent

你有没有试过:table_a.total_spent <;>;table_b.total_spent

mysql  sql  join