0

I've got a query (it's quite long, but here's the gist of it)

Name.find_by_sql(['SELECT IFNULL(users.last_name, "nil") FROM users
                  JOIN user_groups ON user_groups.user_id = users.id
                  JOIN groups ON groups.id = user_groups.grous_id
                  WHERE users.first_name IN (?)', ['Sam','Sally']]) AND groups.name='baseball'

Now Sam is in the baseball group, but Sally is not.

I would hope that I would get back (Sam's last name is Hill)

['Hill','nil']

But I'm only getting ['Hill']. I'm not sure why I don't get the 'nil' returned. I've tried 'LEFT OUTER JOIN' on each of the join statements as well, as I thought maybe that is where the 'nil' was getting lost, but I'm still only getting the one item returned.

pedalpete
  • 21,076
  • 45
  • 128
  • 239

1 Answers1

2

You're not getting a response back because your filter on groups.name is in the WHERE clause. This will only return users that are in the baseball group. If you add the condition at the JOIN, then you'll get users back that are not part of the baseball group, and they'll have the expected NULL value:

JOIN groups ON groups.id = user_groups.grous_id AND groups.name='baseball'
Dan Short
  • 9,598
  • 2
  • 28
  • 53
  • That seems to be part of the way there Dan, unfortunately, I'm getting back ALL users that are not part of the baseball group, and I'm trying to get back only those users from the 'IN' statement. Also, I only get all the users back when using a 'RIGHT OUTER JOIN', if I just use JOIN, I only get back 'Hill' – pedalpete Aug 10 '11 at 18:22
  • If you're getting all users back, then the users.first_name filter definitely isn't getting applied correctly. Unfortunately, I'm not familiar with the syntax you're using. Perhaps just try hardcoding `users.first_name IN ('Sam', 'Sally')' to make sure it's your app causing the issue, and not the query itself. – Dan Short Aug 10 '11 at 18:31