Best way to join table

[solepixel]

I have a query where I'm trying to match 1 table's primary key to field in another table that holds multiple entries. For example, the field may have 1 value in it, and allows this to work:

LEFT JOIN client_list ON client_domain.dom_id = client_list.client_dom

however sometimes the field has multiple id's separated by a comma. How would I join each domain with that same client?

Here's my full query:

SELECT * FROM client_domain LEFT JOIN client_company ON client_domain.dom_id = client_company.comp_dom LEFT JOIN client_list ON client_domain.dom_id LIKE client_list.client_dom WHERE client_domain.dom_del <> 1 AND client_company.comp_del <> 1 AND client_list.client_del <> 1

 

[r937]

multiple id's separated by a comma == very bad design

change this --

ON client_domain.dom_id LIKE client_list.client_dom

to this --

ON concat(',',client_list.client_dom,',')
LIKE concat('%,',client_domain.dom_id,',%')

why is it bad?

because this WHERE clause rquires a table scan, so the query gets slower and slower the more rows there are

r937.com | rudy.ca

 

[solepixel]

Can you suggest an alternate design? I'm completely willing to modify this.

 

[r937]

if you would kindly show your CREATE TABLE statements for the two tables in question (and any others that might have a comma-delimited list inside a single column)

r937.com | rudy.ca

 

[solepixel]

Here are the 3 tables I'm trying to "Mash up" into 1 display result

CREATE TABLE `client_company` (
  `comp_id` varchar(25) NOT NULL default '',
  `comp_name` varchar(255) default NULL,
  `comp_addr` varchar(255) default NULL,
  `comp_phone` varchar(25) default NULL,
  `comp_city` varchar(255) default NULL,
  `comp_state` varchar(50) default NULL,
  `comp_zip` varchar(25) default NULL,
  `comp_dom` varchar(25) NOT NULL default '',
  `comp_del` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`comp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `client_domain` (
  `dom_id` varchar(255) NOT NULL default '',
  `client_registrar` varchar(255) default NULL,
  `client_domuser` varchar(255) default NULL,
  `client_dompass` varchar(255) default NULL,
  `client_domain` varchar(255) default NULL,
  `client_domftpuser` varchar(255) default NULL,
  `client_domftppass` varchar(255) default NULL,
  `client_domdate` int(25) NOT NULL default '0',
  `client_domstatus` varchar(255) default NULL,
  `dom_del` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`dom_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `client_list` (
  `client_id` int(5) unsigned zerofill NOT NULL auto_increment,
  `client_first` varchar(255) default NULL,
  `client_last` varchar(255) default NULL,
  `client_email` varchar(255) default NULL,
  `client_phone` varchar(25) default NULL,
  `client_phone2` varchar(25) default NULL,
  `client_street` varchar(255) default NULL,
  `client_city` varchar(150) default NULL,
  `client_state` varchar(150) default NULL,
  `client_zip` varchar(25) default NULL,
  `client_dom` varchar(100) NOT NULL default '',
  `client_ftp` varchar(255) default NULL,
  `client_comments` longtext,
  `client_comp` varchar(100) default NULL,
  `client_del` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=349 ;

The way they use PK for client_company and client_domain is a code consisting of a modified unix timestamp. Here's the function, if you care:

function generateID($type){
	$thetime = time();
	if($type == "company"){
		$string = $thetime + 3;
	} else {
		if($type == "computer"){
			$string = $thetime + 7;
		} else {
			$string = $thetime;
		}
	}
	
	$find = array('/2/','/6/','/0/');
	$rep = array('e','b','X');
	$id = preg_replace($find,$rep,$string);
	
	if($type == "ftp"){
		$id = strrev($id);
	}
	
	return $id;
}

 

[r937]

i'm confused

first of all i don't understand the php, but that's okay, it's some sort of process which creates a value

i can see it being used for client_company.comp_id but not for client_domain.dom_id

is client_list.client_dom a comma-delimited list?

perhaps you could show a few rows of data (important columns only) to illustrate the relationships

r937.com | rudy.ca

 

[solepixel]

I think the client_list table serves as a "middle man". The only column which holds multiple values is client_dom (in the client_list table). The client_list table connects to the client_company table via client_comp field. That only holds 1 value, the PK of the corresponding row in the client_company table.

The only tricky part is that the client_list table connects to multiple rows in the client_domain table.

Just note all clients don't have domains and all clients don't have a company, but from what I understand, the client_list table is the one that connects the other 2 together.

So in conclusion, my end result query should be a huge mash up of all columns from these 3 tables, with duplicated data from the client_list and client_company tables for each client_domain record. make sense?

 

[r937]

yes, most of that makes sense, thanks

however, some questions

you said "The only column which holds multiple values is client_dom (in the client_list table)" but client_dom is varchar(100), and dom_id is varchar(255) -- how many 255-byte key values do you think are going to fit into a 100-byte field? is this where that wacky function somehow comes in?

also, you said "The client_list table connects to the client_company table via client_comp field" but client_comp is varchar(100), whereas comp_id is varchar(25) -- why the discrepancy?

these things need to be corrected, and then you can/should define true foreign keys

we will also remove client_dom and create a new one-to-many table

r937.com | rudy.ca

 

[solepixel]

Ok, what I ended up doing is referencing the client_id from the client_list table as a column in the client_domain table. Now I guess my question is, how do I setup a query to display:

all rows in the client_list table
all rows in the client_domain table
client_list connects to client_company by client_id
client_list connects to client_domain by client_id

here's the new structure:

CREATE TABLE `client_company` (
  `comp_id` varchar(25) NOT NULL default '',
  `comp_name` varchar(255) default NULL,
  `comp_addr` varchar(255) default NULL,
  `comp_phone` varchar(25) default NULL,
  `comp_city` varchar(255) default NULL,
  `comp_state` varchar(50) default NULL,
  `comp_zip` varchar(25) default NULL,
  `comp_dom` varchar(25) NOT NULL default '',
  `comp_del` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`comp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `client_domain` (
  `dom_id` varchar(255) NOT NULL default '',
  `client_id` int(5) unsigned zerofill default NULL,
  `client_registrar` varchar(255) default NULL,
  `client_domuser` varchar(255) default NULL,
  `client_dompass` varchar(255) default NULL,
  `client_domain` varchar(255) default NULL,
  `client_domftpuser` varchar(255) default NULL,
  `client_domftppass` varchar(255) default NULL,
  `client_domdate` int(25) NOT NULL default '0',
  `client_domstatus` varchar(255) default NULL,
  `dom_del` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`dom_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `client_list` (
  `client_id` int(5) unsigned zerofill NOT NULL auto_increment,
  `client_first` varchar(255) default NULL,
  `client_last` varchar(255) default NULL,
  `client_email` varchar(255) default NULL,
  `client_phone` varchar(25) default NULL,
  `client_phone2` varchar(25) default NULL,
  `client_street` varchar(255) default NULL,
  `client_city` varchar(150) default NULL,
  `client_state` varchar(150) default NULL,
  `client_zip` varchar(25) default NULL,
  `client_ftp` varchar(255) default NULL,
  `client_comments` longtext,
  `client_comp` varchar(100) default NULL,
  `client_del` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=349 ;

 

[guelphdad]

if you haven't got rid of your comma separated data you still have the same problem. Did you do that?

 

[solepixel]

I just reversed the relationship. Now each domain has a referenced client_id.

 

[r937]

I just reversed the relationship. Now each domain has a referenced client_id.

so you're okay now, then?


i would still strongly suggest that you actually implement real foreign keys

among the things this will require of you is that the datatypes of primary/foreign keys must be an exact match (your fields confused me because they aren't)

as well, you will also need to define indexes on the foreign keys, which will optimize your joins

r937.com | rudy.ca