...

View Full Version : how to get unconnected matches



jasonpc1
05-20-2011, 05:56 PM
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.

sunfighter
05-20-2011, 09:03 PM
I am guessing that when a customer is added to the DB you assign them a contact and that number is added to the account_id. So what your looking for is the contact s that have no customers. Now, someone better then I might show you an easier way but this will list all contacts and show who there customers are. It will show a blank when there is no customer.



$query = "SELECT contacts.fullname, customers.fullname FROM contacts LEFT JOIN customers ON contacts.account_id = customers.account_id";
$result = mysql_query($query);

while($row = mysql_fetch_row($result))
{
echo $row[0]. " - ". $row[1];
echo "<br />";
}


PS You do NOT want the account_id in your customers DB to be AUTO_INCREMENTing. This column needs to be insertable(sp?) to link it to your contact DB.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum