I have tables like this

# Things

# ThingUser

I want to select all Things where user_id=123 OR it is related to user 123 through ThingUser.

I think maybe I'm close with something like this:

select * from things
  left outer join thing_users on things.id=thing_users.thing_id and thing_users.user_id = 123
    things.user_id = 123 OR
    thing_users.id IS NOT NULL

But on my very large dataset this hangs for several seconds and then I quit it.

  • 1
    "... this hangs for several seconds and then I quit it". Why did you quit it??? Don't you want the result? – Eric Feb 14 at 23:24
  • 1
    What's the point of LEFT JOIN if you put condition thing_users.id IS NOT NULL? Isn't it the same as just JOIN? – Eric Feb 14 at 23:25

I concluded that the only / most appropriate way to achieve this is simply with a UNION. These two queries unioned together:

  • where user_id=123
  • it is related to user 123 through ThingUser
|improve this answer|||||

1) Use Test environment if you have one

2) Alter your query to give you back a smaller sample size to see if that's what you're looking for:

SELECT TOP 100 * from things
  INNER JOIN thing_users
 ON things.id=thing_users.id
  WHERE things.user_id = 123
|improve this answer|||||
  • 1
    TOP is only valid in SQL Server, not a standard SQL clause – ypercubeᵀᴹ Feb 15 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.