Question about the right kind of join to use

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');

payments doesn’t have an email field? Or one of the fields on the tables doesn’t match what’s on the query.

Without seeing the error message, it’s kind of hard to guess…

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 -----------------------------------------------------------------------------------^

HTH,

:slight_smile:

Thanks WolfShade!

sorry, late to the party

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

did that make sense?

1 Like

Yes, I updated my query in the code. Thanks!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.