Inaccurate results

[corbeeresearch]

Hi,

 

My sql statement is having a slight inaccuracy with the carType and the price.

 

I'm trying to get the carbrand.brandName into a drop down list that would

exclude empty price, carmodel.cartype with blank or unknown, some specific carbrand.brandName

but include only cars with bestmatch.match = best and a given year by the user.

 

I don't get why the price and the cartype is not displaying correctly on some records.

 

Please help me!

 

Here is my select statement

SELECT `carbrand`.`brandID` , `carbrand`.`brandName` , `carmodel`.`brandID` , `carmodel`.`carType` , `caryear`.`caryearID` , `caryear`.`price` , `caryear`.`brandID` , `bestmatch`.`carBrand`
FROM (
`carbrand`
)
INNER JOIN `caryear` ON `caryear`.`brandID` = `carbrand`.`brandID`
INNER JOIN `bestmatch` ON `bestmatch`.`carBrand` = `carbrand`.`brandID`
INNER JOIN `carmodel` ON `carmodel`.`brandID` = `carbrand`.`brandID`
WHERE `caryear`.`year` = '2009'
AND `caryear`.`price` >0
AND `carmodel`.`cartype` <> ""
AND `carmodel`.`cartype` <> "unknown"
AND `carbrand`.`brandID` <>15
AND `carbrand`.`brandID` <>17
AND `carbrand`.`brandID` <>29
AND `bestmatch`.`match` = 'Best'
GROUP BY `brandName`
LIMIT 0 , 30

 

and here is my create table

CREATE TABLE `carbrand` (
`brandID` int(99) NOT NULL AUTO_INCREMENT,
`brandName` varchar(60) DEFAULT NULL,
`logo` varchar(100) DEFAULT NULL,
PRIMARY KEY (`brandID`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=latin1

CREATE TABLE `caryear` (
`caryearID` int(99) NOT NULL AUTO_INCREMENT,
`year` int(99) DEFAULT NULL,
`price` varchar(50) DEFAULT '0',
`brandID` int(99) DEFAULT NULL,
`modelID` int(99) DEFAULT NULL,
`status` enum('active','inactive') NOT NULL DEFAULT 'active',
PRIMARY KEY (`caryearID`)
) ENGINE=InnoDB AUTO_INCREMENT=21034 DEFAULT CHARSET=latin1

CREATE TABLE `carmodel` (
`modelID` int(99) NOT NULL AUTO_INCREMENT,
`modelName` varchar(100) DEFAULT NULL,
`brandID` int(99) DEFAULT NULL,
`carType` enum('unknown','cv','pc') NOT NULL DEFAULT 'unknown',
PRIMARY KEY (`modelID`)
) ENGINE=InnoDB AUTO_INCREMENT=1618 DEFAULT CHARSET=latin1

CREATE TABLE `bestmatch` (
`bestid` int(11) NOT NULL AUTO_INCREMENT,
`carBrand` int(11) NOT NULL,
`insurance` int(11) NOT NULL,
`match` varchar(30) DEFAULT NULL,
PRIMARY KEY (`bestid`)
) ENGINE=InnoDB AUTO_INCREMENT=235 DEFAULT CHARSET=latin1

 

 

 

Thanks

[fenway]

Define "not correctly".