Brad,
This is also a design issue. Generally when designing tables to support a
one-to-many relationship, you put a pointer field in the "many" table and
load it with the ID value of the "one" that it relates to. In this case you
would want a nullable field like "users_id" on your computers table. Since
there is only 1 blank to assign a computer to you get only 2 states, the
computer is assigned to someone or it isn't (it has a value or is null).
Link tables, like you have, are generally only created for a many-to-many
relationship. If several printers could all be used by multiple computers,
that would be an example of a many-to-many relationship.
In either case, testing for the nonexistence of one side of a relationship
can be accomplished through a LEFT JOIN. To use your link table to test to
see if any user's are not in the link table you could write:
SELECT users.ID, users.Name
FROM users
LEFT JOIN comp-user-link
ON comp-user-link.user_id = users.user_id
WHERE comp-user-link is NULL
If you redesign your relationship to eliminate the link table, you would
change this query by replacing all instances of "comp-user-link" with
"computers" to get the same results (users not assigned computers).
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Brad Tilley
<[EMAIL PROTECTED]> To: [EMAIL PROTECTED]
cc:
05/25/2004 02:39 Fax to:
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]
