Re: bulduing a select to get excluded datas

<robertdeniro_at_consultant.com> wrote in message news:1112663410.662034.146260_at_z14g2000cwz.googlegroups.com...
> Hello, I have a problem because I am trying to find results from a
> select statement, where the datas from the first table are the ones
> that are NOT included in the second tables.
>
> I have a table containing the list of known users: users_list
>
> map_id, ->id refering to another table
> rank, ->rank of the user
> other datas...
>
> several entries can have the same map_id, the key of this table is the
> association of (map_id, rank)
>
> and I the table of users : users
>
> map_id,
> datas about the user.... -> several rows of datas for this particular
> user
>
> The table "users" can have users that are not yet known, therefore they
> are not in the "users_list" table
>
> I want to write a web page that takes any users that is not known yet,
> so I can fill the missing datas and add the user in the users_list
> The difficulty is that one known user can have several ranks
> so to display the list of users that are not yet known, I tried the
> following statement:
>
> select users.map_id,users_list.rank from users,users_list where
> users.map_id!=users_list.map_id
>
> but it doesn't work! I get multiple entries of "map_id" joined with
> "rank"
> what I want is the list of all references "map_id" in the table "users"
> for which the map_id is not included in the table "users_list"
>
> If anyone can help me, I'd be very thankful, because I'm lost!
>

Another reply has already suggested you check out a NOT EXISTS subquery in the WHERE clause.

In addition, you should check out the MINUS operation (Oracle dialect) which I believe is EXCLUDE in standard SQL. This is a set operation in the same ilk as UNION. Received on Wed Apr 06 2005 - 10:22:50 CEST