Can someone help me pinpoint why I’m getting an error on this query? This is to pull up a member’s record as sometimes the email they have in the members table is different than the email address associated with their PayPal account, which is stored in the payments table.
Thanks!
SELECT
m.firstname
m.memberID
FROM
members m
FULL OUTER JOIN
payments p
ON
m.memberID = p.memberID
WHERE
(m.email = 'test@aol.com' or p.email = 'test@aol.com');
Yes, the payments table does have an ‘email’ column. Here is the error, which isn’t overly helpful:
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 '.memberID
FROM
members m
FULL OUTER JOIN
payments p
ON
m.memberID = p.membe'
You’re missing a comma after “m.firstname” - 'SELECT m.firstname m.memberID'
HERE -----------------------------------------------------------------------------------^
insert obligatory remark how your error would’ve been trivial to find if you were using the leading comma convention (search in this forum)
but to answer the question in the title, what type of join to use, the answer is, LEFT OUTER JOIN
if you ever have a payment for a non-existing member, something would be terribly wrong, and so what you want is the database to enforce a restriction that every payment has to be for an existing member (google foreign key)…
… and therefore you don’t need a FULL OUTER JOIN, which would attempt to include payments that don’t match any member