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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
