Is there a better way to write this query?

Question

So let's say I'm trying to get a list of all my users who belong to a certain user group. Easy:

SELECT *
  FROM users, usergroups
 WHERE usergroups.user_group_id = 1

NOW, let's say I want to get a list of all users who belong to a certain user group AND who have an email that ends in .edu.

SELECT *
  FROM users, usergroups
 WHERE usergroups.user_group_id = 1
   AND users.email LIKE '%.edu'

That works great. Now let's say we want to get all of the above, plus users belonging to user group 2--but we don't care about the second group's email addresses. This query doesn't work:

SELECT *
  FROM users, usergroups
 WHERE usergroups.user_group_id IN (1,2)
   AND users.email LIKE '%.edu'

Because it filters users from the second group. Right now I'm doing something like this:

SELECT *
  FROM users as usrs, usergroups as groups1, usergroups as groups2
 WHERE (groups1s.user_group_id = 1 AND users.email LIKE '%.edu')
    OR groups2.user_group_id = 2

This gives me the results I want, but I hate the way it looks and works. Is there a cleaner way to do this?

EDIT

I didn't include joins on my last iteration up there. Here's what the query should really look like:

SELECT *
FROM users as usrs JOIN
usergroups as groups1 on usrs.group_id = groups1.group_id JOIN
usergroups as groups2 on usrs.group_id = groups2.group_id
WHERE (groups1.user_group_id = 1 AND users.email LIKE '%.edu')
OR groups2.user_group_id = 2

Answer 1

The way you are doing it may work, even though it looks uglier, because of SQL syntax. What doesn't make sense to me is why there is no join between users and usergroups on user id:

... where usergroups.user_id=users.user_id

Unless I am missing something, because you are doing a cross join between users and usergroups. It would help us a whole bunch, if you listed the columns in each of your tables.

Answer 2

Fix your JOINs.

You are always returning every row from users (ignore email filter for now) once for every row in usergroups because you have no JOIN, no matter what group they belong to. You have a simple cross join/cartesian product.

Then, use UNION or UNION ALL to remove the OR. Or leave the OR in place.

 SELECT *
  FROM
     users as usrs
     JOIN
     usergroups as groups1 ON usrs.foo = groups1.foo
 WHERE
    groups1s.user_group_id = 1 AND users.email LIKE '%.edu'
UNION --maybe UNION ALL
SELECT *
  FROM
     users as usrs
     JOIN
     usergroups as group2  ON  usrs.foo = groups2.foo
WHERE
     groups2.user_group_id = 2

Answer 3

Going off of what Michael Goldshteyn said about re-writing it using JOINS, and Joe Stefnelli's comment about the cross join, your initial query, rewritten, would be:

SELECT *
FROM users 
JOIN user_groups ON users.user_group_id = user_groups.user_group_id
WHERE users.email LIKE '%.edu'
AND user_groups.user_group_id = 1

Adding the second group would result in this:

SELECT *
FROM users AS users
JOIN user_groups AS user_groups ON users.user_group_id = user_groups_1.user_group_id
WHERE ( ( users.email LIKE '%.edu' AND user_groups_1.user_group_id = 1 )
        OR user_groups_2.user_group_id = 2 ) 

Or you could even do a union (personally I wouldn't do this):

SELECT *
FROM users AS users_1 
JOIN user_groups AS user_groups_1 ON users_1.user_group_id = user_groups_1.user_group_id
WHERE users_1.email LIKE '%.edu'
AND user_groups_1.user_group_id = 1
UNION
SELECT *
FROM users AS users_2
JOIN user_groups AS user_groups_2 ON users_2.user_group_id = user_groups_2.user_group_id
AND user_groups_2.user_group_id = 2

Answer 4

I'll go out on a limb a bit and assume there is a relationship between users and usergroups. You'd then write your query like this:

SELECT *
    FROM users as usrs
        INNER JOIN usergroups as groups1
            ON usrs.GroupID = groups1.GroupID
    WHERE (groups1.user_group_id = 1 AND usrs.email LIKE '%.edu')
        OR groups1.user_group_id = 2

Answer 5

There is no need to select usergroups twice using different aliases. You could do simply:

SELECT *
  FROM users as usrs, usergroups
 WHERE (usergroups.user_group_id = 1 AND users.email LIKE '%.edu')
    OR usergroups.user_group_id = 2

or, even better (using join):

SELECT *
  FROM users as usrs 
  JOIN usergroups on usergroups.userid = users.id
 WHERE (usergroups.user_group_id = 1 AND users.email LIKE '%.edu')
    OR usergroups.user_group_id = 2

Answer 6

I don't see anything wrong with your latest edit with the joins in place. You could do a Union but I think that'd be uglier imo.

Answer 7

Optimizing the Query.

For large tables (maybe this is not the case) you should think on performance penalties your query might have. So I prefer the following approach: first I select into some temporary table the rows I'm going to work with, next I delete the rows I don't need, finally I select the result set and delete objects in memory. Note: This query uses Transact SQL.

select u.*, g.user_group_id into #TEMP from users u, usergroups g where u.group_id = g.group_id and g.user_group_id in (1,2) 
delete from #TEMP where user_group_id = 1 and email not like '%.edu'
select * from #TEMP
drop table #TEMP