Posted by Henrik Goldman on 10/02/24 11:55

Hi there,

I'm new to sql and thus I'm having problems with a specific query which I
hope you guys can help me with.

Basicly I have a few tables which I'm trying to do a query on:

Table groups contains information about specific groups e.g. "Windows" or
"Unix".
Table users contains information about specific users e.g. "a", "b" or "c".
Table users_groups contain information about group relationship (a user can
be in multiple groups) e.g. (a, Windows), (b, Unix), (a, Unix).
In this case user c is ungrouped.

Now I'd like to find the users which does belong to group Windows and those
who do not:

select distinct username from users_groups where groupname = "Windows" order
by username asc;

This works pretty well for finding users in the specific group. In this case
the result is a.



However I'd like to get the opposite result (b and c) but I'm stuck.

The problem is that I'd like a list of all users excluding those which are
in "Windows"

Here is a partial query:

select distinct users.username from users left join users_groups on
users.username = users_groups.username where users_groups.username is null
order by users.username asc;

This only gives me those users who are not grouped at all. This mean that
user b is not in those results.



Please advise.

Thanks in advance.



-- Henrik

• Next in forum: Re: format files for use with bulk insert
• Prev in forum: Re: format files for use with bulk insert
• Thread view: Selecting on multiple tables

[Reply to this message]