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]
