Join problem

Hello somebody,
I have a problem with my query... I am trying to display results from one table that is not in the other. Column partner_1 and partner_2 from table couples is linked to user_id on table contestants. Each time I run a query it gives me either double or triple results even results that are in the couples table which shouldn't be.

Here are some of the queries I've tried already:
SELECT contestants.user_id
FROM contestants
INNER JOIN couples
ON contestants.user_id <> couples.partner_1
AND contestants.user_id <> couples.partner_2

SELECT contestants.user_id
FROM contestants,couples
WHERE contestants.user_id <> couples.partner_1
AND contestants.user_id <> couples.partner_2

---

Here is my exported tables:
CREATE TABLE contestants (
user_id mediumint(8) unsigned NOT NULL auto_increment,
fname varchar(30) NOT NULL,
lname varchar(30) NOT NULL,
address tinytext NOT NULL,
city tinytext NOT NULL,
state varchar(30) NOT NULL,
country varchar(26) NOT NULL,
email varchar(30) NOT NULL,
sex set('M','F') NOT NULL default 'F',
DOB date NOT NULL COMMENT 'User''s date of birth (year-month-day)',
gebruiker varchar(25) character set latin1 collate latin1_general_cs NOT NULL COMMENT 'Username of the contestant',
wachtwoord tinytext character set latin1 collate latin1_general_cs NOT NULL COMMENT 'Password of the contestant',
DOR datetime NOT NULL COMMENT 'GMT Date and time of registration',
PRIMARY KEY (user_id),
UNIQUE KEY gebruiker (gebruiker),
UNIQUE KEY email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE couples (
couple_id mediumint(8) unsigned NOT NULL auto_increment,
partner_1 mediumint(8) unsigned NOT NULL,
partner_2 mediumint(8) unsigned default NULL,
PRIMARY KEY (couple_id),
KEY partner_1 (partner_1),
KEY partner_2 (partner_2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `couples`
ADD CONSTRAINT couples_ibfk_3 FOREIGN KEY (partner_2) REFERENCES contestants (user_id) ON UPDATE CASCADE,
ADD CONSTRAINT couples_ibfk_2 FOREIGN KEY (partner_1) REFERENCES contestants (user_id) ON UPDATE CASCADE;

Can anybody help me with this problem?

Thanks in advance.

If I understand the problem correctly, you're looking for contestants without partners. This is the approach I typically use, and is known as an "anti-join," i.e. you want to find all the rows that don't match the condition. The trick is to simply exploit the behavior of an outer join, and look specifically for the unmatched NULL columns (make sure you aren't putting a nullable column in the WHERE clause or you might get dubious results).

Code Snippet

SELECT contestants.user_id
FROM contestants
LEFT OUTER JOIN couples
ON contestants.user_id = couples.partner_1
OR contestants.user_id = couples.partner_2
WHERE couples.partner_1 IS NULL

I obviously don't have your data to test against, but this will probably get you going in the right direction.

Now, I see you've got 'ENGINE=InnoDB' on your CREATE TABLE scripts, which makes me think this is running against MySQL and not SQL Server. I can't be totally sure this works there, but it's pretty standard syntax so I suspect it will.
|||Hello Davidbrit2,
You really saved my life!
Thank you so much.

Terrance