how to get unconnected matches

[jasonc]

CREATE TABLE IF NOT EXISTS `contacts` (
  `contact_id` smallint(15) NOT NULL auto_increment,
  `account_id` smallint(15) NOT NULL,
  `fullname` tinytext NOT NULL,
  PRIMARY KEY  (`contact_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=101 ;

INSERT INTO `contacts` (`contact_id`, `account_id`, `fullname`) VALUES
(1, 37, 'Joe Bloggs'),
(2, 53, 'Peter Smith');


CREATE TABLE IF NOT EXISTS `customers` (
  `account_id` smallint(15) NOT NULL auto_increment,
  `fullname` char(100) NOT NULL,
  PRIMARY KEY  (`account_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=128 ;

INSERT INTO `customers` (`account_id`, `fullname`) VALUES
(37, 'Simon Mitchell'),
(53, 'Sarah Lane');

 

How do I get the results to show where either a contact and customer is not connected with each other and visa versa.

 

Where a contact does not have a customer and where a customer does not have a contact.

[jasonc]

sorry i got that wrong.  I only need to know where a contact does not connect with a customer.

 

CREATE TABLE IF NOT EXISTS `contacts` (
  `contact_id` smallint(15) NOT NULL auto_increment,
  `account_id` smallint(15) NOT NULL,
  `fullname` tinytext NOT NULL,
  PRIMARY KEY  (`contact_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=101 ;

INSERT INTO `contacts` (`contact_id`, `account_id`, `fullname`) VALUES
(1, 37, 'Joe Bloggs'),
(2, 53, 'Peter Smith'),
(3, 55, 'Jamie Short');


CREATE TABLE IF NOT EXISTS `customers` (
  `account_id` smallint(15) NOT NULL auto_increment,
  `fullname` char(100) NOT NULL,
  PRIMARY KEY  (`account_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=128 ;

INSERT INTO `customers` (`account_id`, `fullname`) VALUES
(37, 'Simon Mitchell'),
(53, 'Sarah Lane');

 

How do I get the results to show where either a contact and customer is not connected with each other.

 

Where a contact does not have a customer.

[mikosiko]

assuming that your "customer" table is the Master and "contacts" hold the Details then the "non connection" (orphan records) between Contacts and Customer should never happens (the contrary of course is possible), because that will constitute a violation of the DB Referential Integrity (here is an article that can help you to dig a little more into the concept) http://www.databasejournal.com/features/mysql/article.php/2248101/Referential-Integrity-in-MySQL.htm

 

The Referencial Integrity must be controlled either by your code or (best solution) for the definition of an explicit CONSTRAINT in the DB (Foreign Key), the definition/usage of integrity constraints depend of which Storage Engine your choose.. MyIsam or InnoDb

 

Integrity Constraints are only enforced automatically on InnoDb S.E (if you define its of course)

 

In your case a FK (Foreign Key) should be defined on your contacts table using the field "account_id" (if you SE is Innodb)

here are examples and concepts details : http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html

 

and in case that you don't want to go in that route, a simple SELECTS using JOIN's or Sub-Queries will answers your questions, like

 

SELECT DISTINCT account_id
    FROM contacts
    WHERE NOT EXISTS (SELECT 1 FROM customers WHERE customers.account_id = contacts.account_id);

 

and more to read into : http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html