Bart, One other thing... based on your description of your needs, you seem to have a one-to-many relationship between computers and users. In that case, there wouldn't be a need for the intersection table comp_user_link. You could simply add a user_id column to your computers table, make it allow nulls (for computers that don't have users), then your query would look like this and you would have eliminated the overhead of an extra table.
select * from users left join computers on (users.user_id = computers.user_id) where computers.user_id is NULL; Lou ----- Original Message ----- From: "Bart Nessux" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, May 10, 2004 8:07 PM Subject: Re: Validation/Linking Table Question > Thanks Lou, I'll give this a go! > > > >From: "Lou Olsten" <[EMAIL PROTECTED]> > >Reply-To: "Lou Olsten" <[EMAIL PROTECTED]> > >To: "Bart Nessux" <[EMAIL PROTECTED]>,<[EMAIL PROTECTED]> > >Subject: Re: Validation/Linking Table Question > >Date: Mon, 10 May 2004 17:52:38 -0400 > > > >Assuming that your pre 4.1, meaning that you can't use a subquery.... this > >will do it, BUT comp_id must be set to allow NULLS for this to work > >otherwise the optimizer will handle it differently > >(http://dev.mysql.com/doc/mysql/en/LEFT_JOIN_optimisation.html). It is > >legal to create a primary key on a column that allows nulls, so it's just a > >question of whether or not you want to do that. > > > >select * from users left join comp_user_link on (users.user_id = > >comp_user_link.user_id) > >where comp_user_link.comp_id is null; > > > > > >----- Original Message ----- > >From: "Bart Nessux" <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]> > >Sent: Monday, May 10, 2004 1:38 PM > >Subject: Validation/Linking Table Question > > > > > > > I have three tables in a MySQL DB... among others: > > > > > > 1. computers > > > 2. users > > > 3. comp_user_link > > > > > > This particular DB is used for inventory and tracking purposes. I think > > > the table names are self explanatory. Computers has 'comp_id'... users > > > has 'user_id' as their primary indexed keys. These keys are linked > > > together in the comp_user_link table. A user may have more than one > > > computer (For example, Bob may have a desktop and a laptop at the same > > > time), but no computer may be linked to more than one user > > > simultaneously (Bob and Tom should never have the same laptop at the > > > same time). > > > > > > Anyway, what I'd like to do is to find all 'user_ids' that aren't linked > > > to a computer. Any tips on how to arrange a SELECT statement to do that? > > > > > > Thanks, > > > > > > Bart > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > >http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > _________________________________________________________________ > Best Restaurant Giveaway Ever! Vote for your favorites for a chance to win > $1 million! http://local.msn.com/special/giveaway.asp > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
