Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Apr 2010
    Posts
    417
    Thanks
    4
    Thanked 1 Time in 1 Post

    how to get unconnected matches

    Code:
    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.
    Last edited by jasonpc1; 05-20-2011 at 07:24 PM.

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,696
    Thanks
    25
    Thanked 658 Times in 657 Posts
    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.

    Code:
    $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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •