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
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.
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
Brad Tilley wrote:
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:
Except within the context of a left join.
Every user in a left join
Victor Shawn,
Thanks for the select info and the relationship info. Both of your
examples worked.
The reason we designed a separate linking table is that we wanted to
always keep the computers separate from the users. A container of users
and a container of computers with a linking table
Ah yes, thank you Gerald.
I didn't understand this until now. I guess I should say that 'by
design' the linking table will never contain a null, but the left join
changes that. Thank you for pointing that out.
gerald_clark wrote:
Brad Tilley wrote:
I should better describe the tables:
to:
PM Subject: Re: Search for relationships
that aren't present
Brad Tilley wrote:
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
a LEFT JOIN should do the trick
something like this:
SELECT users.user_id
FROM users
LEFT JOIN comps_users_link ON
(users.user_id=comps_users_link.user_id)
WHERE comps_users_link.computer_id IS NULL
olinux
--- Brad Tilley wrote:
Three tables:
computers (Describes computers)
users
