Getting results from a double join when I shouldn't be

[busboy]

Because user 110 already has a row in the campaigns table, I wasn't expecting to get any rows back when executing this query....but I ended up getting a total count of how many guides user 110 has. Did I screw up my double join query?

Thanks!



SELECT COUNT(*) as total
FROM (users INNER JOIN users as guides ON guides.uid = users.guide)
LEFT OUTER JOIN campaigns
ON campaigns.uID = users.uID
AND campaigns.campaign = 'Upgrade to gold'
AND campaigns.answer IS NULL
WHERE users.guide = 110;

[ScallioXTX]

users? guides? campaigns? could we have some context here please?

[busboy]

users = table
guide = a column in the users table

campaigns = table

[ScallioXTX]

LEFT OUTER JOIN basically states "If there is a campaign with uID equal to user.id, JOIN it in the row, otherwise put all fields to NULL". Since this is optional, in the grand scheme of things you can neglect it, and your query boils down to

 
SELECT COUNT(*) as total 
FROM (users INNER JOIN users as guides ON guides.uid = users.guide) 
WHERE users.guide = 110;

Which will indeed get you the number of guides for a user.
What are you trying to achieve?

[busboy]

What I'm trying to achieve is this: If a user has a row in the campaign table for this particular campaign, then don't return anything at all when the query is executed. That way when they login again, they won't be shown the campaign again. Does this help?

[ScallioXTX]

Yup, it does
Sounds like you want something like this

SELECT COUNT(*) as total 
FROM (users INNER JOIN users as guides ON guides.uid = users.guide) 
LEFT OUTER JOIN campaigns 
ON campaigns.uID = users.uID 
AND campaigns.campaign = 'Upgrade to gold' 
AND campaigns.answer IS NULL
WHERE users.guide = 110
AND campaigns.uID NOT IN (
  SELECT uID FROM users
)

[busboy]

Yes, I came up with something similar. But both your example and mine produce a single row, called total, where the value is zero. How can we tweak this so that no rows are returned at all if a person has already responded to the campaign?

Thanks!

[ScallioXTX]

You query for the COUNT, and that always returns something (indeed if there is nothing to find the COUNT is zero).
If you don't want to retrieve any results when there are none, you should query for the actual data instead of COUNT