Page 1 of 2

Optimize MySQL Code

Posted: Thu Dec 07, 2006 12:59 pm
by WanamakerStudios
After much help from the great people of this forum, I've gotten my query put together as I needed it. So far I have:

Code: Select all

## CREATE TEMP TABLES, FIND DATA AND SORT
	mysql_query("CREATE TEMPORARY TABLE zipsearch (zipcode INT, distance INT) ENGINE=MEMORY");
	mysql_query("INSERT INTO zipsearch (zipcode, distance) SELECT DISTINCT zip_code, round(69.1 * sqrt(((latitude-(SELECT DISTINCT latitude FROM `zipcodes` WHERE zip_code = $_GET[zip])) * (latitude-(SELECT DISTINCT latitude FROM `zipcodes` WHERE zip_code = $_GET[zip]))) + .6 * ((longitude-(SELECT DISTINCT longitude FROM `zipcodes` WHERE zip_code = $_GET[zip])) * (longitude-(SELECT DISTINCT longitude FROM `zipcodes` WHERE zip_code = $_GET[zip]))))) AS distance FROM `zipcodes` HAVING distance <= (". $_GET['radius'] ." +1) ORDER BY distance ASC");
	mysql_query("CREATE TEMPORARY TABLE dealersearch (lotid VARCHAR(255), zipcode INT) ENGINE=MEMORY");
	mysql_query("INSERT INTO dealersearch (lotid, zipcode) SELECT DISTINCT CIDLOTD, CZIP FROM `lotdata-temp` WHERE 1=1");
	mysql_query("CREATE TEMPORARY TABLE premierdealers (lotid VARCHAR(255), status INT(1)) ENGINE=MEMORY");
	mysql_query("INSERT INTO premierdealers (lotid, status) SELECT DISTINCT LOTID, STATUS FROM `premiumdealers` WHERE 1=1");
	$query = "
		SELECT DISTINCT CVIN, CYEAR, CONCAT(CDESCRIPTION, ' ', CMODEL) AS VEHICLE, CCOLOR, CMILEAGE, CPRICE, STATUS, DISTANCE
		FROM `dealersearch`
		JOIN `vehicles-temp` ON `vehicles-temp`.CIDLOTD = `dealersearch`.lotid
		RIGHT JOIN `carparts` ON (`carparts`.CIDCARP = `vehicles-temp`.CMAKE AND `carparts`.CCATEGORY = 'MAKE')
		JOIN `zipsearch` ON `zipsearch`.zipcode = `dealersearch`.zipcode
		LEFT JOIN `premierdealers` ON `premierdealers`.lotid = `dealersearch`.lotid
		WHERE CMAKE = '" . $_GET['make'] . "' AND CPRICE <> 0 AND CYEAR >= '" . $_GET['minyear'] . "' AND CYEAR <= '" . $_GET['maxyear'] . "' AND CMODEL LIKE '%" . $_GET['model'] . "%'
		ORDER BY STATUS DESC, ". $_GET['sortby'] ." ". $_GET['order'] ."";
Now, what would be the best way to optimize this? There are 8,000+ records in the DealerSearch table and over 600,000 in the VehicleTemp table. With things written as they are, I am at a 9 second query time. How do I help reduce this to help return results to visitors quicker?

Posted: Thu Dec 07, 2006 2:18 pm
by ok
Maybe search only 800 records at a time.

Posted: Thu Dec 07, 2006 2:20 pm
by WanamakerStudios
Well, I did add a LIMIT clause at the very end ... but it doesnt seem to help ...

Posted: Tue Dec 12, 2006 9:20 am
by WanamakerStudios
I updated my coding and repasted. Any ideas on how to optimize it a little?

Posted: Wed Dec 13, 2006 10:16 pm
by fractalvibes
Why can't you do one query against the base tables? Why do you need to create these temp tables?

Posted: Wed Dec 13, 2006 10:21 pm
by WanamakerStudios
Well ... i guess it stems from not knowing 100% of the language and its structure. I have 4 tables that I need to pull information from and paste together so the surfer can sort by any field. I figured temp tables would be the best way to extract only the data I needed and then combine it in the end. Since my last post, I've updated my code a little ... but I'm sure there is still room for improvement. Any suggestions would be helpful and I am willing to provide any information needed! Thanks!

Code: Select all

## COORDINATES OF REQUESTED ZIP	
	$coordinates_query = mysql_query("SELECT DISTINCT latitude, longitude FROM zipcodes WHERE zip_code = '". $_GET[zip] ."'");
	$coordinates = mysql_fetch_array($coordinates_query);
	$latitude = $coordinates['latitude'];
	$longitude = $coordinates['longitude'];

## CREATE TEMP TABLES, FIND DATA AND SORT
	mysql_query("CREATE TEMPORARY TABLE zipsearch (zipcode INT, distance INT, PRIMARY KEY (zipcode)) ENGINE=MEMORY");
	mysql_query("INSERT IGNORE INTO zipsearch (zipcode, distance) SELECT zip_code, round(69.1 * sqrt((POW((latitude-". $latitude ."),2)) + .6 * (POW((longitude-". $longitude ."),2)))) AS distance FROM `zipcodes` HAVING distance <= (". $_GET['radius'] ." +1)");

	mysql_query("CREATE TEMPORARY TABLE dealersearch (lotid VARCHAR(255), zipcode INT, PRIMARY KEY (lotid)) ENGINE=MEMORY");
	mysql_query("INSERT IGNORE INTO dealersearch (lotid, zipcode) SELECT CIDLOTD, CZIP FROM `lotdata-temp` WHERE CZIP = ANY (SELECT zipcode FROM zipsearch)");

	mysql_query("CREATE TEMPORARY TABLE premierdealers (lotid VARCHAR(255), status INT(1), PRIMARY KEY (lotid)) ENGINE=MEMORY");
	mysql_query("INSERT IGNORE INTO premierdealers (lotid, status) SELECT LOTID, STATUS FROM `premiumdealers`");
	$query = "
		SELECT CVIN, CYEAR, CONCAT(CDESCRIPTION, ' ', CMODEL) AS VEHICLE, CCOLOR, CMILEAGE, CPRICE, STATUS, DISTANCE
		FROM `dealersearch`
		JOIN `vehicles-temp` ON `vehicles-temp`.CIDLOTD = `dealersearch`.lotid
		RIGHT JOIN `carparts` ON (`carparts`.CIDCARP = `vehicles-temp`.CMAKE AND `carparts`.CCATEGORY = 'MAKE')
		JOIN `zipsearch` ON `zipsearch`.zipcode = `dealersearch`.zipcode
		LEFT JOIN `premierdealers` ON `premierdealers`.lotid = `dealersearch`.lotid
		WHERE CMAKE = '" . $_GET['make'] . "' AND CPRICE <> 0 AND CYEAR BETWEEN '" . $_GET['minyear'] . "' AND '" . $_GET['maxyear'] . "' AND CMODEL LIKE '%" . $_GET['model'] . "%'
		ORDER BY STATUS DESC, ". $_GET['sortby'] ." ". $_GET['order'] ."";

Posted: Wed Dec 13, 2006 11:09 pm
by fractalvibes
I'd look at making just one query and saving a bit of overhead - quite a bit of network traffic and trips across the pipe to the DB, not to mention creating and inserting into temp tables. I think the latest version of MySQL does support stored procedures perhaps.

fv

Posted: Thu Dec 14, 2006 5:07 am
by WanamakerStudios
Yeah ... but how would I combine everything that I've got going on there?

Posted: Thu Dec 14, 2006 9:41 am
by Begby
You need to get rid of the temp tables and such. This can be probably done in one efficient query.

One thing you can do is calculate the zip code distances and store them in an php array instead of a temp table. Then you can convert the array into a list and put it in a where - WHERE zipCode IN ( {$zipList} ).

Are your tables properly indexed? Is zipCode a primary key and an integer or a non-key string? If at all possible always just use an autoincrement as a primary key and join on that, joining on IDs makes life a lot easier and your queries tend to run faster.

Security: Putting raw $_GET right in your queries is a big giant no-no. You are just begging your site to get hacked.

One thing that will help is if you post the following items:

Exact table structures with indexes (sqldump)

About how many records you are working with

What you want the final result set to look like

Explain the parameters you are using to get the result set, it appears you are finding the closest dealers or something.

Posted: Thu Dec 14, 2006 9:48 am
by WanamakerStudios
If I store the distances in an array, how can I sort the final results by distance then?

Posted: Thu Dec 14, 2006 10:23 am
by Begby
Well you will have the array of distances/zip codes. After you get the result set into a PHP array you can add the distances to the results and sort it, or you can always put the distances into the query and not use a zip code array. There are a lot of ways to do this. Best thing is to post all the information I asked for and that will help get this all sorted out.

Posted: Thu Dec 14, 2006 12:15 pm
by WanamakerStudios
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Ok. Here goes. This are all of the tables that I need to reference for my results and the structure that holds them.

[b]CarParts Table[/b]
[i]This holds the MAKE ID and its corresponding VALUE[/i]
[syntax="sql"]
CREATE TABLE `carparts` (
  `CIDCARP` char(2) NOT NULL default '',
  `CCATEGORY` char(12) NOT NULL default '',
  `CDESCRIPTION` char(25) NOT NULL default '',
  KEY `CIDCARP` (`CIDCARP`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
LotData Table
This holds all of the dealer information for all participating dealers across the country

Code: Select all

CREATE TABLE `lotdata` (
  `CIDLOTD` varchar(5) NOT NULL default '',
  `CIDLICE` varchar(5) NOT NULL default '',
  `CLOTNUMBER` varchar(10) NOT NULL default '',
  `CNAME` varchar(30) NOT NULL default '',
  `CADDRESS1` varchar(30) NOT NULL default '',
  `CADDRESS2` varchar(30) NOT NULL default '',
  `CCITY` varchar(30) NOT NULL default '',
  `CSTATE` char(2) NOT NULL default '',
  `CZIP` varchar(5) NOT NULL default '',
  `CFIPS` varchar(5) NOT NULL default '',
  `CPHONE` varchar(35) NOT NULL default '',
  `CMAINTAINI` char(1) NOT NULL default '',
  `CWARRANTYT` char(1) NOT NULL default '',
  `CPC_LABOR` char(3) NOT NULL default '',
  `CPC_PARTS` char(3) NOT NULL default '',
  `CWARRMONTH` char(2) NOT NULL default '',
  `IWARRMILES` varchar(5) NOT NULL default '',
  `CSERVICEAG` char(1) NOT NULL default '',
  `CPRINTCONT` varchar(30) NOT NULL default '',
  `CPRICEPERC` varchar(5) NOT NULL default '',
  `WEBPAGE1` varchar(60) NOT NULL default '',
  `WEBPAGE2` varchar(60) NOT NULL default '',
  `WEBPAGE3` varchar(60) NOT NULL default '',
  `WEBPAGE4` varchar(60) NOT NULL default '',
  `WEBPAGE5` varchar(60) NOT NULL default '',
  `WEBPAGE6` varchar(60) NOT NULL default '',
  `WEBPAGE7` varchar(60) NOT NULL default '',
  `WEBPAGE8` varchar(60) NOT NULL default '',
  `LOTSTATUS` varchar(10) NOT NULL default '',
  `LOTURL` varchar(30) NOT NULL default '',
  `LASTUPDATE` varchar(8) NOT NULL default '',
  `CONTACT` varchar(30) NOT NULL default '',
  `FAXNUM` varchar(12) NOT NULL default '',
  `LOTSTATUS2` varchar(12) NOT NULL default '',
  `MAXCARSININVENTORY` smallint(2) NOT NULL default '0',
  `CARFAXID` varchar(10) NOT NULL default '',
  `MILEAGECHECK` varchar(8) NOT NULL default '',
  `ISAUTONA` char(1) NOT NULL default '',
  `USEDCARMGRNAME` varchar(30) NOT NULL default '',
  `LOTGROUPID` varchar(10) NOT NULL default '',
  `LASTUPDATEDATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `LASTTRANSMISSIONDATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `NADAREGION` datetime NOT NULL default '0000-00-00 00:00:00',
  `FRANCHISESREPRESENTED` varchar(30) NOT NULL default '',
  KEY `CIDLOTD` (`CIDLOTD`),
  KEY `CZIP` (`CZIP`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
PremiumDealers Table
This table holds a short list of DEALER IDs that we've set as premium (STATUS = 2), featured (STATUS =1) or basic/non-listed (STATUS = 0/NULL)

Code: Select all

CREATE TABLE `premiumdealers` (
  `LOTID` varchar(5) NOT NULL default '',
  `STATUS` char(1) NOT NULL default '',
  KEY `LOTID` (`LOTID`),
  KEY `STATUS` (`STATUS`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
VehicleData Table
This table holds every bit of information about each one of the vehicles at dealers lots across the country

Code: Select all

CREATE TABLE `vehicles-temp` (
  `CIDLOTD` varchar(5) default NULL,
  `CVIN` varchar(17) NOT NULL default '',
  `CSTOCKNUM` varchar(15) default NULL,
  `CDISPOSITI` char(1) default NULL,
  `CMAKE` char(2) default NULL,
  `CMODEL` varchar(25) default NULL,
  `CYEAR` int(4) default NULL,
  `CMILEAGE` int(10) default NULL,
  `CBODYTYPE` char(2) default NULL,
  `CENGINE` char(2) default NULL,
  `CENGINESIZ` varchar(5) default NULL,
  `CINDUCTION` char(2) default NULL,
  `CTRANSMISS` char(2) default NULL,
  `CCOLOR` varchar(19) default NULL,
  `CPRICE` int(25) default NULL,
  `CCOST` varchar(10) default NULL,
  `CWARRANTY` char(1) default NULL,
  `CWARRANTYT` char(1) default NULL,
  `FWARRANTY` char(2) default NULL,
  `FWARRANTYT` char(1) default NULL,
  `FWARRANTYC` varchar(4) default NULL,
  `CPC_LABOR` char(3) default NULL,
  `CPC_PARTS` char(3) default NULL,
  `CWARRMONTH` char(2) default NULL,
  `CWARRMILES` varchar(5) default NULL,
  `CMANUF_MON` char(2) default NULL,
  `CMANUF_YEA` varchar(4) default NULL,
  `CSERVICEAG` char(1) default NULL,
  `DDATE_IN` date default '0000-00-00',
  `DDATE_REMO` date default '0000-00-00',
  `O1` char(2) default NULL,
  `O2` char(2) default NULL,
  `O3` char(2) default NULL,
  `O4` char(2) default NULL,
  `O5` char(2) default NULL,
  `O6` char(2) default NULL,
  `O7` char(2) default NULL,
  `O8` char(2) default NULL,
  `O9` char(2) default NULL,
  `O10` char(2) default NULL,
  `O11` char(2) default NULL,
  `O12` char(2) default NULL,
  `O13` char(2) default NULL,
  `O14` char(2) default NULL,
  `O15` char(2) default NULL,
  `O16` char(2) default NULL,
  `O17` char(2) default NULL,
  `O18` char(2) default NULL,
  `O19` char(2) default NULL,
  `O20` char(2) default NULL,
  `O21` char(2) default NULL,
  `O22` char(2) default NULL,
  `O23` char(2) default NULL,
  `O24` char(2) default NULL,
  `O25` char(2) default NULL,
  `O26` char(2) default NULL,
  `O27` char(2) default NULL,
  `O28` char(2) default NULL,
  `O29` char(2) default NULL,
  `O30` char(2) default NULL,
  `O31` char(2) default NULL,
  `O32` char(2) default NULL,
  `O33` char(2) default NULL,
  `O34` char(2) default NULL,
  `O35` char(2) default NULL,
  `DUPDATED` datetime default '0000-00-00 00:00:00',
  `DCREATED` datetime default '0000-00-00 00:00:00',
  `CPRICING` varchar(8) default NULL,
  `DATEONLOT` datetime default '0000-00-00 00:00:00',
  `FREEHANDTEXT` longtext,
  `RECORDFLAGS` varchar(80) default NULL,
  `MAKETXT` varchar(25) default NULL,
  `MODELTXT` varchar(25) default NULL,
  `BODYTXT` varchar(25) default NULL,
  `ENGINETXT` varchar(25) default NULL,
  `INDUCTIONTXT` varchar(25) default NULL,
  `TRANSTXT` varchar(25) default NULL,
  `EXTERNALCOLOR` varchar(50) default NULL,
  `INTERNALCOLOR` varchar(50) default NULL,
  `LABORCOST` varchar(8) default NULL,
  `LABORPRICE` varchar(8) default NULL,
  `INTERNETCOST` varchar(8) default NULL,
  `INTERNETPRICE` varchar(8) default NULL,
  `MANFACTUREDATE` datetime default '0000-00-00 00:00:00',
  `DATEFIRSTSEEN` datetime default '0000-00-00 00:00:00',
  `NADAID` varchar(8) default NULL,
  `CERTIFICATIONNUM` varchar(20) default NULL,
  `CERTIFICATIONDATE` datetime default '0000-00-00 00:00:00',
  `INSERVICEDATE` datetime default '0000-00-00 00:00:00',
  `TRIMLEVEL` varchar(15) default NULL,
  `SYSTEMWARRANTYNUM` smallint(2) default '0',
  `LASTINVENTORYDATE` datetime default '0000-00-00 00:00:00',
  `OPTIONS` longtext,
  `ADDENDUM` longtext,
  `CUSTOPT` longtext,
  `KBBDATA` varchar(60) default NULL,
  `CARMEMO` varchar(255) default NULL,
  `CARNUMBER` varchar(255) default NULL,
  `STYLEID` int(4) default '0',
  `F100` varchar(255) NOT NULL default '',
  `F101` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`CVIN`),
  KEY `CIDLOTD` (`CIDLOTD`),
  KEY `CYEAR` (`CYEAR`),
  KEY `CCOLOR` (`CCOLOR`),
  KEY `CCOST` (`CCOST`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ZipCode Table
This holds all of the zipcodes and their corresponding latitude/longitude coordinates

Code: Select all

CREATE TABLE `zipcodes` (
  `detail_code` char(1) NOT NULL default '',
  `zip_code` varchar(5) NOT NULL default '',
  `city_state_key` varchar(6) NOT NULL default '',
  `zip_code_class` char(1) NOT NULL default '',
  `city_state_name` varchar(28) NOT NULL default '',
  `city_abbr` varchar(13) NOT NULL default '',
  `facility_code` char(1) NOT NULL default '',
  `mail_name` char(1) NOT NULL default '',
  `city_key` varchar(6) NOT NULL default '',
  `city` varchar(28) NOT NULL default '',
  `delivery` char(1) NOT NULL default '',
  `cart_rate` char(1) NOT NULL default '',
  `unique_zip` char(1) NOT NULL default '',
  `finance_number` varchar(6) NOT NULL default '',
  `state` char(2) NOT NULL default '',
  `fips_code` char(3) NOT NULL default '',
  `county` varchar(25) NOT NULL default '',
  `latitude` varchar(7) NOT NULL default '',
  `longitude` varchar(8) NOT NULL default '',
  `area_code` varchar(15) NOT NULL default '',
  `time_zone` char(2) NOT NULL default '',
  `elevation` varchar(5) NOT NULL default '',
  `persons_per_household` varchar(4) NOT NULL default '',
  `population` varchar(8) NOT NULL default '',
  `square_miles` varchar(6) NOT NULL default '',
  `households_per_zipcode` varchar(8) NOT NULL default '',
  `population_white` varchar(8) NOT NULL default '',
  `population_black` varchar(8) NOT NULL default '',
  `population_hispanic` varchar(8) NOT NULL default '',
  `income_per_household` varchar(8) NOT NULL default '',
  `avg_house_value` varchar(8) NOT NULL default '',
  KEY `zip_code` (`zip_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Record Count
CarParts Table: 400
LotData Table: 8,000
PremiumDealers Table: 10
VehicleData Table: 600,000
ZipCode Table: 80,000

Final Results
The final results need to display: Year, Make, Model, Color, Cost, Odometer, Distance From Given Zip Code

Parameters
The user is providing a year range, make, model, zipcode and radius around zipcode. I need to be able to find all of the dealers within the radius of the given zipcode and display all of the vehicles on each of their lots. I also need the user to be able to sort the results by any one of the displayed fields. ie: they can sort price highest to lowest or closest to farthest distance from them, ect.


If you need anymore info, please let me know! I greatly appreciate the help!


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Thu Dec 14, 2006 2:44 pm
by Begby
Ok, so here is a function for the distance between two latitude/longitude points. I leave it up to you to translate that into SQL, I think your existing function is correct but I dammit Jim I am a code monkey not a mathematician. Since you want to order by distance and have a lot of results, its probably going to be best to do it in SQL.

Code: Select all

function calcDist($lon1,$lat1,$lon2,$lat2)
{
	$r = 3963.0;
	$multiplier = 1;
	return $multiplier * $r * acos(sin($lat1/57.2958) *
 			sin($lat2/57.2958) +  cos($lat1/57.2958) *
 			cos($lat2/57.2958) * cos($lon2/57.2958 - $lon1/57.2958));
	}
You have a lot records to deal with and some math processing is slow, so anything to limit how many records to look at is going to help. Keep in mind that 1 unit of latitude is roughly equal to 69.2 miles. So, if they are searching for a radius of 50 miles, and their zip code latitude is 43.4, you can ignore any zips with a latitude greater than 44.4 or less than 42.4. So store the minLatitude and maxLatitude. The smaller the radius the faster your query, so you may want to limit how far of a search is allowed.

So now we have

Code: Select all

SELECT
  round(69.1 * sqrt((POW((latitude-". $latitude ."),2)) + .6 * (POW((longitude-". $longitude ."),2))))
    AS distance
FROM
  zipcodes z
WHERE
  z.latitude BETWEEN {$minLatitude} AND {$maxLatitude}
HAVING
  distance <= {$radius}
Test it and make sure it works

Now you have to add in the other tables and get the data you want joined to the zip codes and other parameters. This is roughly what you want below. I in no way vouch that this query is going to work but its along the lines of what you want. Others here better at SQL than me or with more time might be able to help further.

When you are writing a query this big, the most important thing is that you start with a small base query (the query above), get that working, then add only one table at a time to it. Don't try and write the whole damn query at once.

Code: Select all

SELECT
 Year, Make, Model, Color, Cost, Odometer, Distance From Given Zip Code 
  round(69.1 * sqrt((POW((z.latitude-". $latitude ."),2)) + .6 * (POW((z.longitude-". $longitude ."),2))))
    AS distance,
FROM
  lotdata l
LEFT JOIN
  vehices v
ON
  v.CIDLOTD = l.CIDLOTD
LEFT JOIN
  zipcodes z
ON
  l.CZIP = z.zip_code
LEFT JOIN
  carparts cp
ON 
  cp.CIDCARP = v.CMAKE
WHERE
  z.latitude BETWEEN {$minLatitude} AND {$maxLatitude}
AND
  cp.CCATEGORY = 'MAKE'
AND
  v.CMAKE = {$make} ....  fill in the rest here
ORDER BY
  ... Order on whatever you need here ...
HAVING
  distance <= {$radius}
LIMIT
  ... limit this so you are only displaying so many per page, another big performance gain ....
Also, I notice a lot of your fields are text fields. If if all possible make the ones you are joining on integer fields. If there is a limit to what can go in a field, such as CCATEGORY, make it an enum or have it be a join to another external categories table. Searching on integers is quicker than searching integers.

I hope this helps you a bit.

Posted: Sun Dec 17, 2006 6:19 pm
by WanamakerStudios
Begby ... you are the man! Thanks a bunch! It works wonderfully! The only problem that I am having is that when I go to add my premiumdealers table to determine who gets show first (and hightlighted), I get multiple results of the same vehicle. Any ideas? I coded it as follow:

Code: Select all

LEFT JOIN `premiumdealers` p
		ON p.lotid = v.CIDLOTD

Posted: Wed Dec 20, 2006 7:38 pm
by Begby
WanamakerStudios wrote:Begby ... you are the man! Thanks a bunch! It works wonderfully! The only problem that I am having is that when I go to add my premiumdealers table to determine who gets show first (and hightlighted), I get multiple results of the same vehicle. Any ideas? I coded it as follow:

Code: Select all

LEFT JOIN `premiumdealers` p
		ON p.lotid = v.CIDLOTD
Try making it 'SELECT DISTINCT' instead of 'SELECT'.

If that doesn't work post your entire working SQL statment and I'll check it out. It can probably be solved using an SQL case statment or an IF statement.