LEFT OUTER JOIN question

Databases
1

Just wondering if the following is possible - I have a query which looks like:

SELECT trade_users.UserID
     , trade_users.Firstname
     , trade_users.Lastname
     , trade_users.Company
     , lodge_activities.CategoryID
     , lodge_activities.Activity
  FROM trade_users
  LEFT OUTER JOIN user_activities_it ON user_activities_it.UserID=trade_users.UserID 
  LEFT OUTER JOIN lodge_activities ON lodge_activities.ActivityID=user_activities_it.ActivityID
  WHERE  lodge_activities.Category_ID = 19
 GROUP BY trade_users.UserID

This gives me the results I am looking for - trade_users with a particular category (19) in the lodge_activities table.

What I am also trying to do is create a second list of trade_users which do not have that category in the lodge_activities table.

I can get it to list trade_users with no records in the lodge_activities table using

   WHERE  lodge_activities.Category_ID IS NULL

But not list any that don’t have a Category_ID of 19 - thought it might be:

   WHERE Category_ID IS NULL OR Category_ID <> 19
2

I fail to see why you have a GROUP BY when you don’t use a aggregate function.

NULLS can be tricky, it doesn’t matter the database (which I assume is MySQL)

Maybe your condition would be WHERE (lodge_activities.Category_ID <>19 OR lodge_activities.Category_ID IS NULL)

That’s what I would try

3

please allow me to comment on this

you got the results you were looking for, but i think by accident

with LEFT OUTER JOINS you get NULL for columns in the right table when rows don’t match

but if you add a WHERE condition on a column in the right table, such as –

      WHERE  lodge_activities.Category_ID = 19

then this means you don’t want the unmatched rows at all, you want a specific value

so the query behaves as an inner join, and you actually want an inner join

so that’s why you like the results you’re getting

okay, now on to your question – “a second list of trade_users which do not have that category in the lodge_activities table”

let’s cut to the chase – you want the same category filter, but you want it as part of the outer join, and ~not~ as part of a filter

that means it has to be in the ON clause, not the WHERE clause

SELECT trade_users.UserID
     , trade_users.Firstname
     , trade_users.Lastname
     , trade_users.Company
     , lodge_activities.CategoryID
     , lodge_activities.Activity
  FROM trade_users
LEFT OUTER 
  JOIN user_activities_it 
    ON user_activities_it.UserID = trade_users.UserID 
LEFT OUTER 
  JOIN lodge_activities 
    ON lodge_activities.ActivityID = user_activities_it.ActivityID
   AND lodge_activities.Category_ID = 19
 WHERE lodge_activities.ActivityID IS NULL
1 Like
4

Thanks - you’re quite right about the first one really just needing an inner join - I have amended that one now.

With your change above, that is giving me all the trade_users.

Basically category 19 is ‘country’, so I’m trying to create lists of all the trade users with and without a country, so that someone can keep track of all the ones we have added a country for.

So in plain English terms I’m looking for trade users which have no activities plus trade users with any activities other than those with a Category_ID of 19.

(Excuse the naming convention - obviously countries are not activities. When the project started they were just activities, but location kind of got included - ‘keywords’ would be a better term probably.)

5

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