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.
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)