So you do have two ways to associate users with computers. One is directly
on the computers table and the other is through the link table. That leaves
me with two questions to answer:
Question 1) Are there any users that do not have an ID in the user_id
field on the computers table?
Question 2) Are there any users that no not have an ID in the user_id
field of the comp-user-link table?
It's the function of the LEFT JOIN clause to return all rows from one table
(the one on the LEFT of the statement) and only those rows from the other
table where the "ON" clause is satisfied. For all rows where the ON clause
is NOT satisfied, the query engine NULLs-out all of the columns in the
other table.
So to answer Q1:
SELECT users.user_id
FROM users LEFT JOIN computers ON computers.user_id = users.user_id
where computers.user_id is NULL
This works because if there is a row in the users table that will not line
up with a row in the computers table, all values that would have normally
been provided by the actual data from the computers table will return as
NULL. This is independent of the actual table definition.
To answer Q2:
SELECT users.user_id
FROM users LEFT JOIN comp-user-link ON comp-user-link.user_id =
users.user_id
where comp-user-link.user_id is NULL
The LEFT JOIN "fills in" the columns of the comp-user-link table on the
non-aligned rows (where there exists a user but no link) with the null
values for you.
Respecfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Brad Tilley
<[EMAIL PROTECTED]> To: Victor Pendleton <[EMAIL
PROTECTED]>
cc: "'[EMAIL PROTECTED] '"
<[EMAIL PROTECTED]>
05/25/2004 02:58 Fax to:
PM Subject: Re: Search for relationships
that aren't present
I should better describe the tables:
computers has 'comp_id' as its primary, unique key.
users has 'user_id' as its primary, unique key.
comp-user-link has two (and only two fields) that are *never* null:
'comp_id' (which must be unique), and 'user_id'
Every computer is linked to a user... the problem is some users (those
who have left, or returned computers for various reasons) are still in
our users table for historical purposes... we maintain a 'transfers'
table to show when computers were given to users... this is why we never
throw users away even after they're no longer associated with us. Does
that make sense?
Anyway, I only want to show the users that currently are not linked to a
computer and the linking table does not contain nulls.
Thanks.
Victor Pendleton wrote:
> If you are using a version that supports sub-selects you could perform a
> subquery.
> Otherwise,the solution depends on your primary keys.
> SELECT u.* > FROM users u
> LEFT OUTER JOIN
> comp-user-link c
> ON u.userID = c.userID
> WHERE c.userID IS NULL
>
> -----Original Message-----
> From: Brad Tilley
> To: [EMAIL PROTECTED]
> Sent: 5/25/04 1:39 PM
> Subject: Search for relationships that aren't present
>
> Three tables:
>
> computers (Describes computers)
> users (Describes users)
> comp-user-link (Links users to computers in a 1 user
to many
> computers
> relationship)
>
> Could someone offer advice on how to construct a select statement to
> show which users aren't in the comp-user-link table? Sort of the reverse
>
> of this:
>
> select * from comp-user-link, users where users.user_id =
> comps_users_link.user_id
>
> Basically, I'm trying to see which users aren't currently linked to a
> computer.
>
> As always,
>
> Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
