[SOLVED] Join problem

[neo115162]

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( 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( unsigned NOT NULL auto_increment,

  partner_1 mediumint( unsigned NOT NULL,

  partner_2 mediumint( 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.

 

[Wildbug]

Any time you find yourself (or someone else) saying "I need to find results that are in one table and not in another" you should think "this is a job for LEFT JOIN!"

 

You want contestants who are not already in the couples table, right?

 

SELECT user_id FROM contestants LEFT JOIN couples ON user_id=partner_1 OR user_id=partner2 WHERE partner_1 IS NULL

 

That should work.

[Illusion]

why not the results with partner_2 IS NULL.

[Wildbug]

Any row in "contestants" with no matching user_id in "couples" will have an all-NULL row LEFT JOINed to it.  I could have used any of the fields in couples to find the NULL condition, but I only need one.  I picked partner_1, but you could use partner_2 or couple_id (which might be a better choice from a semantic perspective).

 

Hmm, on closer examination, not just any column can be used.  partner_2 is default NULL; partner_1 and couple_id are NOT NULL, so you should use either of them since partner_2 could be NULL on a partner_1-matching row.

[neo115162]

Ah man, this works!

Thank you so much for helping me out, I really really appreciate it.