Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Aug 2010
    Posts
    17

    Unanswered: how do I optimize this code?

    Hi,
    I have a problem with my code is generating too much processed, took about 10 seconds to show up.

    My friend suggested to convert it to subquery, but how do I do that?

    Are there other ways to speed this up? the data contains 1000+ records

    SELECT `carbrand`.`brandID`, `carbrand`.`brandName`, `carmodel`.`brandID`, `carmodel`.`carType`, `caryear`.`caryearID`, `caryear`.`price`, `caryear`.`brandID`, `bestmatch`.`carBrand`

    FROM (`carbrand`)

    LEFT JOIN `caryear` ON `caryear`.`brandID` = `carbrand`.`brandID`
    LEFT JOIN `bestmatch` ON `bestmatch`.`carBrand` = `carbrand`.`brandID`
    LEFT JOIN `carmodel` ON `carmodel`.`brandID` = `carbrand`.`brandID`

    WHERE `caryear`.`price` > 0 AND `bestmatch`.`match` <> 'Not Covered' AND `carbrand`.`brandID` <> 15 AND `carbrand`.`brandID` <> 17 AND `carbrand`.`brandID` <> 29 AND `carmodel`.`cartype` <> "" AND `carmodel`.`cartype` <> "unknown"
    GROUP BY `brandName`

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    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 
       AND caryear.price > 0
    INNER
      JOIN bestmatch
        ON bestmatch.carBrand = carbrand.brandID 
       AND bestmatch.match <> 'Not Covered'
    INNER
      JOIN carmodel
        ON carmodel.brandID = carbrand.brandID 
       AND carmodel.cartype <> '' 
       AND carmodel.cartype <> 'unknown' 
     WHERE carbrand.brandID NOT IN ( 15, 17, 29 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2010
    Posts
    17
    Thanks, it works, but I just found that some of the results where not accurate, for example if you input a data in caryear table's brandid, modelid and year then compared it with the join some values in the cartype and prices are different

    did I miss something?
    Thanks again

    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by corbeeresearch View Post
    ... for example if you input a data in caryear table's brandid, modelid and year then compared it with the join some values in the cartype and prices are different
    sorry, cannot investigate this problem without actual data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2010
    Posts
    17
    Code:
    INSERT INTO `carbrand` (`brandID`, `brandName`, `logo`) VALUES
    (1, 'ALFA ROMEO', 'alfa-romeo.jpg'),
    (2, 'ANFRA', NULL),
    (3, 'AUDI', 'audi.jpg'),
    (4, 'BMW', 'bmw.jpg'),
    (5, 'CHANA', 'chana.jpg'),
    (6, 'CHERY', 'chery.jpg'),
    (7, 'CHEVROLET', 'chevrolet.jpg'),
    (8, 'CHRYSLER', 'chrysler.jpg'),
    (9, 'DAEWOO', 'daewoo.jpg'),
    (11, 'DODGE', 'dodge.jpg'),
    (13, 'FORD', 'ford.jpg'),
    (14, 'FOTON', 'foton.jpg'),
    (15, 'HONDA MINDANAO, VISAYAS', 'honda.jpg'),
    (16, 'HONDA', 'honda.jpg'),
    (17, 'HONDA SOUTH, EASTERN AND CENRAL MANILA', 'honda.jpg'),
    (18, 'HYUNDAI CENTRAL MANILA', 'hyundai.jpg'),
    (19, 'ISUZU', 'isuzu.jpg'),
    (20, 'JAGUAR', 'jaguar.jpg'),
    (21, 'JEEP', 'jeep.jpg'),
    (22, 'KIA', 'kia.jpg'),
    (23, 'LAND ROVER', 'land-rover.jpg'),
    (24, 'LEXUS', 'lexus.jpg'),
    (25, 'MAZDA', 'mazda.jpg'),
    (26, 'MERCEDES-BENZ', 'mercedes-benz.jpg'),
    (27, 'MITSUBISHI', 'mitsubishi.jpg'),
    (28, 'NISSAN', 'nissan.jpg'),
    (29, 'NISSAN GMA, LUZON, VISAYAS, MINDANAO', 'nissan.jpg'),
    (30, 'PEUGEOT', 'peugeot.jpg'),
    (31, 'PORSCHE', 'porsche.jpg'),
    (32, 'SSANGYONG', 'ssangyong.jpg'),
    (33, 'SUBARU', 'subaru.jpg'),
    (34, 'SUZUKI', 'suzuki.jpg'),
    (35, 'TOYOTA', 'toyota.jpg'),
    (36, 'VOLKSWAGEN', 'volkswagen.jpg'),
    (37, 'VOLVO', 'volvo.jpg'),
    (38, 'GREAT WALL', 'greatwall.jpg'),
    (39, 'JAC', NULL),
    (40, 'LIFAN', 'lifan.jpg'),
    (41, 'MINI', 'mini.jpg'),
    (42, 'NEXUS', NULL),
    (43, 'WULING', 'wuling.jpg'),
    (44, 'NORKIS GROUP OF COMPANIES', 'norkis.jpg'),
    (45, 'DAIHATSU', 'daihatsu.jpg'),
    (46, 'FERRARI', 'ferrari.jpg'),
    (47, 'GEELY', 'geely.jpg'),
    (48, 'HUMMER', 'hummer.jpg'),
    (49, 'OPEL', 'opel.jpg');

  6. #6
    Join Date
    Aug 2010
    Posts
    17
    Code:
    INSERT INTO `carmodel` (`modelID`, `modelName`, `brandID`, `carType`) VALUES
    (1, '155 2.0L', 1, ''),
    (2, 'GTV 3.0L V6', 1, ''),
    (3, 'Spider 2.0L', 1, ''),
    (4, 'TRANSPORTER 180 G DUAL AIR-CON', 2, ''),
    (5, 'JITNEY 140 G', 2, ''),
    (6, 'MINI AMBULANCE 250 D', 2, ''),
    (7, 'PICK-UP SINGLE CAB 250 D', 2, ''),
    (8, 'TRANS 140 G', 2, ''),
    (9, 'A4 2.0 PREMIUM 2.0L', 3, ''),
    (10, 'A3 1.6L', 3, 'pc'),
    (11, 'A3 2.0 TDI DIESEL', 3, 'pc'),
    (12, 'A3 3.2 QUATTRO AWD M/T', 3, 'pc'),
    (13, 'A3 3.2 QUATTRO AWD A/T', 3, 'pc'),
    (14, 'A4 1.8 GP', 3, 'pc'),
    (15, 'A4 1.8T ', 3, 'pc'),
    (16, 'A4 1.8 TFSI ', 3, 'pc'),
    (17, 'A4 1.8T QUATTRO AWD', 3, 'pc'),
    (18, 'A4 2.0L', 3, 'pc'),
    (19, 'A4 2.0 AVANT ', 3, 'pc'),
    (20, 'A4 2.0 TDI 2.0L DIESEL', 3, 'pc'),
    (21, 'A4 2.0 TDI AVANT 2.0L DIESEL', 3, 'pc'),
    (22, 'A4 3.2 FSI QUATTRO AWD', 3, 'pc'),
    (23, 'A5 3.2L V6', 3, 'pc'),
    (24, 'A6 2.4L V6', 3, 'pc'),
    (25, 'A6 2.4 HIGHLINE 2.4L V6', 3, ''),
    (26, 'A6 3.0 TDI QUATTRO 3.0L V6 AWD', 3, 'pc'),
    (27, 'A6 3.2L V6', 3, 'pc'),
    (28, 'A6 4.2 QUATTRO 4.2L V8 AWD', 3, 'pc'),
    (29, 'A8 3.0 TDI 3.0L V6 DIESEL', 3, 'pc'),
    (30, 'A8 3.2L V6', 3, 'pc'),
    (31, 'A8 4.2 QUATTRO 4.2L V8 AWD', 3, 'pc'),
    (32, 'A8 6.0 QUATTRO 6.0L W12 AWD', 3, 'pc'),
    (33, 'Q7 3.0 TDI 3.0L V6 4X4', 3, 'pc'),
    (34, 'Q7 4.2L V8 4X4', 3, 'pc'),
    (35, 'RS4 4.2L V8 AWD', 3, 'pc'),
    (36, 'S4 4.2L V8 AWD', 3, ''),
    (37, 'S5 4.2L V8', 3, 'pc'),
    (38, 'TT 1.8T 1.8L', 3, 'pc'),
    (39, 'TT 1.8T QUATTRO 1.8L AWD', 3, 'pc'),
    (40, 'TT 2.0L', 3, 'pc'),
    (41, 'TT 3.2 QUATTRO 3.2L V6 AWD', 3, 'pc'),
    (42, '116i 1.6L', 4, 'pc'),
    (43, '116i EXECUTIVE 1.6L', 4, 'pc'),
    (44, '118i 1.8L', 4, 'pc'),
    (45, '118i EXECUTIVE 2.0L', 4, 'pc'),
    (46, '118I SPORT 1.8L', 4, 'pc'),
    (47, '120D SPORT 2.0L DIESEL', 4, 'pc'),
    (48, '120i 2.0L', 4, 'unknown'),
    (49, '120I CABRIOLET 2.0L', 4, 'pc'),
    (50, '120i EXECUTIVE 2.0L', 4, 'unknown'),
    (51, '120i SPORT 2.0L', 4, 'unknown'),
    (52, '316I 1.6L', 4, 'unknown'),
    (53, '318I 1.8L', 4, 'unknown'),
    (54, '318i 2.0L', 4, 'pc'),
    (55, '318I EXECUTIVE 1.8L', 4, 'unknown'),
    (56, '318I EXECUTIVE 2.0L', 4, 'pc'),
    (57, '318I M SPORT 2.0L', 4, 'pc'),
    (58, '320D SPORT 2.0L DIESEL', 4, 'pc'),
    (59, '320I 2.0L', 4, 'pc'),
    (60, '320I EXECUTIVE 2.0L', 4, 'pc'),
    (61, '320I SPORT 2.0L', 4, 'pc'),
    (62, '323I 2.3L', 4, 'unknown'),
    (63, '325CI 2.5L', 4, 'unknown'),
    (64, '325i CONVERTIBLE 2.5L', 4, 'pc'),
    (65, '325i COUPE 2.5L', 4, 'pc'),
    (66, '325I EXECUTIVE 2.5L', 4, 'pc'),
    (67, '325I M SPORT 2.5L', 4, 'pc'),
    (68, '325I M TECH 2.5L', 4, 'unknown'),
    (69, '325I SMG 2.5L', 4, 'unknown'),
    (70, '325TI 2.5L', 4, 'unknown'),
    (71, '330I EXECUTIVE 3.0L', 4, 'unknown'),
    (72, '330I M SPORT 3.0L', 4, ''),
    (73, '335i CONVERTIBLE 3.0L', 4, 'pc'),
    (74, '335I COUPE 3.0L', 4, 'pc'),
    (75, '520D 2.0L DIESEL', 4, 'pc'),
    (76, '520I 2.0L', 4, 'pc'),
    (77, '523I EXECUTIVE 2.5L', 4, ''),
    (78, '523I M SPORT 2.5L', 4, ''),
    (79, '525D TOURING 3.0L DIESEL', 4, 'pc'),
    (80, '525I 2.5L', 4, ''),
    (81, '530D SPORT 3.0L DIESEL', 4, ''),
    (82, '550I 4.8L', 4, 'pc'),
    (83, '630I CONVERTIBLE 3.0L', 4, 'pc'),
    (84, '630I COUPE 3.0L', 4, 'pc'),
    (85, '650I CONVERTIBLE 5.0L', 4, 'pc'),
    (86, '650I COUPE 5.0L', 4, 'pc'),
    (87, '735IL 3.5L', 4, ''),
    (88, '740LI EXCLUSIVE 4.0L', 4, 'pc'),
    (89, '750LI 4.8L', 4, 'pc'),

  7. #7
    Join Date
    Aug 2010
    Posts
    17
    I'm just going to attach it, it's too big
    Attached Files Attached Files

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by corbeeresearch View Post
    I'm just going to attach it, it's too big
    sorry, each of those gives me this error:
    The Compressed (zipped) Folder is invalid or corrupted.

    i don't need to see a ton of data, just a few rows which will demonstrate your "prices are different" problem

    the carmodel and carbrand data you posted earlier loaded just fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2010
    Posts
    17
    code brandID (continued next thread)
    Code:
    INSERT INTO `caryear` (`caryearID`, `year`, `price`, `brandID`, `modelID`, `status`) VALUES
    (1, 2010, '0', 1, 1, 'active'),
    (2, 2009, '0', 1, 1, 'active'),
    (3, 2008, '0', 1, 1, 'active'),
    (4, 2007, '0', 1, 1, 'active'),
    (5, 2006, '0', 1, 1, 'active'),
    (6, 2005, '0', 1, 1, 'active'),
    (7, 2004, '0', 1, 1, 'active'),
    (8, 2003, '0', 1, 1, 'active'),
    (9, 2002, '0', 1, 1, 'active'),
    (10, 2001, '432000', 1, 1, 'active'),
    (11, 2000, '374000', 1, 1, 'active'),
    (12, 1999, '315000', 1, 1, 'active'),
    (13, 1998, '288000', 1, 1, 'active'),
    (14, 2010, '0', 1, 2, 'active'),
    (15, 2009, '0', 1, 2, 'active'),
    (16, 2008, '0', 1, 2, 'active'),
    (17, 2007, '0', 1, 2, 'active'),
    (18, 2006, '0', 1, 2, 'active'),
    (19, 2005, '0', 1, 2, 'active'),
    (20, 2004, '0', 1, 2, 'active'),
    (21, 2003, '0', 1, 2, 'active'),
    (22, 2002, '0', 1, 2, 'active'),
    (23, 2001, '0', 1, 2, 'active'),
    (24, 2000, '0', 1, 2, 'active'),
    (25, 1999, '621000', 1, 2, 'active'),
    (26, 1998, '522000', 1, 2, 'active'),
    (27, 2010, '0', 1, 3, 'active'),
    (28, 2009, '0', 1, 3, 'active'),
    (29, 2008, '0', 1, 3, 'active'),
    (30, 2007, '0', 1, 3, 'active'),
    (31, 2006, '0', 1, 3, 'active'),
    (32, 2005, '0', 1, 3, 'active'),
    (33, 2004, '0', 1, 3, 'active'),
    (34, 2003, '0', 1, 3, 'active'),
    (35, 2002, '0', 1, 3, 'active'),
    (36, 2001, '0', 1, 3, 'active'),
    (37, 2000, '0', 1, 3, 'active'),
    (38, 1999, '0', 1, 3, 'active'),
    (39, 1998, '0', 1, 3, 'active'),
    Last edited by corbeeresearch; 08-19-10 at 21:23.

  10. #10
    Join Date
    Aug 2010
    Posts
    17
    2nd to 3rd brand ID(continued next thread)
    Code:
    (40, 2010, '0', 2, 4, 'active'),
    (41, 2009, '0', 2, 4, 'active'),
    (42, 2008, '0', 2, 4, 'active'),
    (43, 2007, '0', 2, 4, 'active'),
    (44, 2006, '0', 2, 4, 'active'),
    (45, 2005, '0', 2, 4, 'active'),
    (46, 2004, '270000', 2, 4, 'active'),
    (47, 2003, '216000', 2, 4, 'active'),
    (48, 2002, '185000', 2, 4, 'active'),
    (49, 2001, '0', 2, 4, 'active'),
    (50, 2000, '0', 2, 4, 'active'),
    (51, 1999, '0', 2, 4, 'active'),
    (52, 1998, '0', 2, 4, 'active'),
    (53, 2010, '0', 2, 5, 'active'),
    (54, 2009, '0', 2, 5, 'active'),
    (55, 2008, '0', 2, 5, 'active'),
    (56, 2007, '0', 2, 5, 'active'),
    (57, 2006, '0', 2, 5, 'active'),
    (58, 2005, '0', 2, 5, 'active'),
    (59, 2004, '0', 2, 5, 'active'),
    (60, 2003, '0', 2, 5, 'active'),
    (61, 2002, '0', 2, 5, 'active'),
    (62, 2001, '90000', 2, 5, 'active'),
    (63, 2000, '77000', 2, 5, 'active'),
    (64, 1999, '72000', 2, 5, 'active'),
    (65, 1998, '68000', 2, 5, 'active'),
    (66, 2010, '0', 2, 6, 'active'),
    (67, 2009, '0', 2, 6, 'active'),
    (68, 2008, '0', 2, 6, 'active'),
    (69, 2007, '0', 2, 6, 'active'),
    (70, 2006, '0', 2, 6, 'active'),
    (71, 2005, '0', 2, 6, 'active'),
    (72, 2004, '0', 2, 6, 'active'),
    (73, 2003, '0', 2, 6, 'active'),
    (74, 2002, '0', 2, 6, 'active'),
    (75, 2001, '122000', 2, 6, 'active'),
    (76, 2000, '104000', 2, 6, 'active'),
    (77, 1999, '86000', 2, 6, 'active'),
    (78, 1998, '68000', 2, 6, 'active'),
    (79, 2010, '0', 2, 7, 'active'),
    (80, 2009, '0', 2, 7, 'active'),
    (81, 2008, '0', 2, 7, 'active'),
    (82, 2007, '0', 2, 7, 'active'),
    (83, 2006, '0', 2, 7, 'active'),
    (84, 2005, '0', 2, 7, 'active'),
    (85, 2004, '0', 2, 7, 'active'),
    (86, 2003, '135000', 2, 7, 'active'),
    (87, 2002, '99000', 2, 7, 'active'),
    (88, 2001, '86000', 2, 7, 'active'),
    (89, 2000, '81000', 2, 7, 'active'),
    (90, 1999, '77000', 2, 7, 'active'),
    (91, 1998, '0', 2, 7, 'active'),
    (92, 2010, '0', 2, 8, 'active'),
    (93, 2009, '0', 2, 8, 'active'),
    (94, 2008, '0', 2, 8, 'active'),
    (95, 2007, '0', 2, 8, 'active'),
    (96, 2006, '0', 2, 8, 'active'),
    (97, 2005, '0', 2, 8, 'active'),
    (98, 2004, '0', 2, 8, 'active'),
    (99, 2003, '156000', 2, 8, 'active'),
    (100, 2002, '131000', 2, 8, 'active'),
    (101, 2001, '113000', 2, 8, 'active'),
    (102, 2000, '104000', 2, 8, 'active'),
    (103, 1999, '86000', 2, 8, 'active'),
    (104, 1998, '0', 2, 8, 'active'),
    (105, 2010, '0', 3, 9, 'active'),
    (106, 2009, '0', 3, 9, 'active'),
    (107, 2008, '2178000', 3, 9, 'active'),
    (108, 2007, '1904000', 3, 9, 'active'),
    (109, 2006, '1731000', 3, 9, 'active'),
    (110, 2005, '0', 3, 9, 'active'),
    (111, 2004, '0', 3, 9, 'active'),
    (112, 2003, '0', 3, 9, 'active'),
    (113, 2002, '0', 3, 9, 'active'),
    (114, 2001, '0', 3, 9, 'active'),
    (115, 2000, '0', 3, 9, 'active'),
    (116, 1999, '0', 3, 9, 'active'),
    (117, 1998, '0', 3, 9, 'active'),
    (118, 2010, '0', 3, 10, 'active'),
    (119, 2009, '2025000', 3, 10, 'active'),
    (120, 2008, '1791000', 3, 10, 'active'),
    (121, 2007, '1593000', 3, 10, 'active'),
    (122, 2006, '0', 3, 10, 'active'),
    (123, 2005, '0', 3, 10, 'active'),
    (124, 2004, '0', 3, 10, 'active'),
    (125, 2003, '0', 3, 10, 'active'),
    (126, 2002, '0', 3, 10, 'active'),
    (127, 2001, '0', 3, 10, 'active'),
    (128, 2000, '0', 3, 10, 'active'),
    (129, 1999, '0', 3, 10, 'active'),
    (130, 1998, '0', 3, 10, 'active'),
    (131, 2010, '0', 3, 11, 'active'),
    (132, 2009, '2385000', 3, 11, 'active'),
    (133, 2008, '2025000', 3, 11, 'active'),
    (134, 2007, '1845000', 3, 11, 'active'),
    (135, 2006, '0', 3, 11, 'active'),
    (136, 2005, '0', 3, 11, 'active'),
    (137, 2004, '0', 3, 11, 'active'),
    (138, 2003, '0', 3, 11, 'active'),
    (139, 2002, '0', 3, 11, 'active'),
    (140, 2001, '0', 3, 11, 'active'),
    (141, 2000, '0', 3, 11, 'active'),
    (142, 1999, '0', 3, 11, 'active'),
    (143, 1998, '0', 3, 11, 'active'),
    (144, 2010, '0', 3, 12, 'active'),
    (145, 2009, '3465000', 3, 12, 'active'),
    (146, 2008, '2633000', 3, 12, 'active'),
    (147, 2007, '2340000', 3, 12, 'active'),
    (148, 2006, '0', 3, 12, 'active'),
    (149, 2005, '0', 3, 12, 'active'),
    (150, 2004, '0', 3, 12, 'active'),
    (151, 2003, '0', 3, 12, 'active'),
    (152, 2002, '0', 3, 12, 'active'),
    (153, 2001, '0', 3, 12, 'active'),
    (154, 2000, '0', 3, 12, 'active'),
    (155, 1999, '0', 3, 12, 'active'),
    (156, 1998, '0', 3, 12, 'active'),
    (157, 2010, '0', 3, 13, 'active'),
    (158, 2009, '0', 3, 13, 'active'),
    (159, 2008, '0', 3, 13, 'active'),
    (160, 2007, '0', 3, 13, 'active'),
    (161, 2006, '0', 3, 13, 'active'),
    (162, 2005, '0', 3, 13, 'active'),
    (163, 2004, '765000', 3, 13, 'active'),
    (164, 2003, '617000', 3, 13, 'active'),
    (165, 2002, '513000', 3, 13, 'active'),
    (166, 2001, '446000', 3, 13, 'active'),
    (167, 2000, '387000', 3, 13, 'active'),
    (168, 1999, '338000', 3, 13, 'active'),
    (169, 1998, '320000', 3, 13, 'active'),
    (170, 2010, '0', 3, 14, 'active'),
    (171, 2009, '0', 3, 14, 'active'),
    (172, 2008, '2453000', 3, 14, 'active'),
    (173, 2007, '2196000', 3, 14, 'active'),
    (174, 2006, '1845000', 3, 14, 'active'),
    (175, 2005, '1386000', 3, 14, 'active'),
    (176, 2004, '1058000', 3, 14, 'active'),
    (177, 2003, '0', 3, 14, 'active'),
    (178, 2002, '0', 3, 14, 'active'),
    (179, 2001, '0', 3, 14, 'active'),
    (180, 2000, '0', 3, 14, 'active'),
    (181, 1999, '0', 3, 14, 'active'),
    (182, 1998, '0', 3, 14, 'active'),
    (183, 2010, '2880000', 3, 15, 'active'),
    (184, 2009, '2300000', 3, 15, 'active'),
    (185, 2008, '0', 3, 15, 'active'),
    (186, 2007, '0', 3, 15, 'active'),
    (187, 2006, '0', 3, 15, 'active'),
    (188, 2005, '0', 3, 15, 'active'),
    (189, 2004, '0', 3, 15, 'active'),
    (190, 2003, '0', 3, 15, 'active'),
    (191, 2002, '0', 3, 15, 'active'),
    (192, 2001, '0', 3, 15, 'active'),
    (193, 2000, '0', 3, 15, 'active'),
    (194, 1999, '0', 3, 15, 'active'),
    (195, 1998, '0', 3, 15, 'active'),
    (196, 2010, '0', 3, 16, 'active'),
    (197, 2009, '0', 3, 16, 'active'),
    (198, 2008, '2610000', 3, 16, 'active'),
    (199, 2007, '2201000', 3, 16, 'active'),
    (200, 2006, '1980000', 3, 16, 'active'),
    (201, 2005, '0', 3, 16, 'active'),
    (202, 2004, '0', 3, 16, 'active'),
    (203, 2003, '0', 3, 16, 'active'),
    (204, 2002, '0', 3, 16, 'active'),
    (205, 2001, '0', 3, 16, 'active'),
    (206, 2000, '0', 3, 16, 'active'),
    (207, 1999, '0', 3, 16, 'active'),
    (208, 1998, '0', 3, 16, 'active'),
    (209, 2010, '0', 3, 17, 'active'),
    (210, 2009, '0', 3, 17, 'active'),
    (211, 2008, '0', 3, 17, 'active'),
    (212, 2007, '0', 3, 17, 'active'),
    (213, 2006, '0', 3, 17, 'active'),
    (214, 2005, '1485000', 3, 17, 'active'),
    (215, 2004, '1341000', 3, 17, 'active'),
    (216, 2003, '851000', 3, 17, 'active'),
    (217, 2002, '648000', 3, 17, 'active'),
    (218, 2001, '540000', 3, 17, 'active'),
    (219, 2000, '459000', 3, 17, 'active'),
    (220, 1999, '437000', 3, 17, 'active'),
    (221, 1998, '414000', 3, 17, 'active'),
    (222, 2010, '0', 3, 18, 'active'),
    (223, 2009, '0', 3, 18, 'active'),
    (224, 2008, '0', 3, 18, 'active'),
    (225, 2007, '0', 3, 18, 'active'),
    (226, 2006, '0', 3, 18, 'active'),
    (227, 2005, '0', 3, 18, 'active'),
    (228, 2004, '0', 3, 18, 'active'),
    (229, 2003, '0', 3, 18, 'active'),
    (230, 2002, '0', 3, 18, 'active'),
    (231, 2001, '0', 3, 18, 'active'),
    (232, 2000, '0', 3, 18, 'active'),
    (233, 1999, '0', 3, 18, 'active'),
    (234, 1998, '0', 3, 18, 'active'),
    (235, 2010, '0', 3, 19, 'active'),
    (236, 2009, '0', 3, 19, 'active'),
    (237, 2008, '2330000', 3, 19, 'active'),
    (238, 2007, '2040000', 3, 19, 'active'),
    Last edited by corbeeresearch; 08-19-10 at 21:23.

  11. #11
    Join Date
    Aug 2010
    Posts
    17
    3rd (continued next thread)
    Code:
    (239, 2006, '0', 3, 19, 'active'),
    (240, 2005, '0', 3, 19, 'active'),
    (241, 2004, '0', 3, 19, 'active'),
    (242, 2003, '0', 3, 19, 'active'),
    (243, 2002, '0', 3, 19, 'active'),
    (244, 2001, '0', 3, 19, 'active'),
    (245, 2000, '0', 3, 19, 'active'),
    (246, 1999, '0', 3, 19, 'active'),
    (247, 1998, '0', 3, 19, 'active'),
    (248, 2010, '0', 3, 20, 'active'),
    (249, 2009, '2601000', 3, 20, 'active'),
    (250, 2008, '2210000', 3, 20, 'active'),
    (251, 2007, '1890000', 3, 20, 'active'),
    (252, 2006, '1634000', 3, 20, 'active'),
    (253, 2005, '1260000', 3, 20, 'active'),
    (254, 2004, '1080000', 3, 20, 'active'),
    (255, 2003, '0', 3, 20, 'active'),
    (256, 2002, '0', 3, 20, 'active'),
    (257, 2001, '0', 3, 20, 'active'),
    (258, 2000, '0', 3, 20, 'active'),
    (259, 1999, '0', 3, 20, 'active'),
    (260, 1998, '0', 3, 20, 'active'),
    (261, 2010, '2990000', 3, 21, 'active'),
    (262, 2009, '2412000', 3, 21, 'active'),
    (263, 2008, '2138000', 3, 21, 'active'),
    (264, 2007, '1917000', 3, 21, 'active'),
    (265, 2006, '0', 3, 21, 'active'),
    (266, 2005, '0', 3, 21, 'active'),
    (267, 2004, '0', 3, 21, 'active'),
    (268, 2003, '0', 3, 21, 'active'),
    (269, 2002, '0', 3, 21, 'active'),
    (270, 2001, '0', 3, 21, 'active'),
    (271, 2000, '0', 3, 21, 'active'),
    (272, 1999, '0', 3, 21, 'active'),
    (273, 1998, '0', 3, 21, 'active'),
    (274, 2010, '0', 3, 22, 'active'),
    (275, 2009, '2691000', 3, 22, 'active'),
    (276, 2008, '2286000', 3, 22, 'active'),
    (277, 2007, '2057000', 3, 22, 'active'),
    (278, 2006, '0', 3, 22, 'active'),
    (279, 2005, '0', 3, 22, 'active'),
    (280, 2004, '0', 3, 22, 'active'),
    (281, 2003, '0', 3, 22, 'active'),
    (282, 2002, '0', 3, 22, 'active'),
    (283, 2001, '0', 3, 22, 'active'),
    (284, 2000, '0', 3, 22, 'active'),
    (285, 1999, '0', 3, 22, 'active'),
    (286, 1998, '0', 3, 22, 'active'),
    (287, 2010, '3880000', 3, 23, 'active'),
    (288, 2009, '3465000', 3, 23, 'active'),
    (289, 2008, '0', 3, 23, 'active'),
    (290, 2007, '0', 3, 23, 'active'),
    (291, 2006, '0', 3, 23, 'active'),
    (292, 2005, '0', 3, 23, 'active'),
    (293, 2004, '0', 3, 23, 'active'),
    (294, 2003, '0', 3, 23, 'active'),
    (295, 2002, '0', 3, 23, 'active'),
    (296, 2001, '0', 3, 23, 'active'),
    (297, 2000, '0', 3, 23, 'active'),
    (298, 1999, '0', 3, 23, 'active'),
    (299, 1998, '0', 3, 23, 'active'),
    (300, 2010, '4700000', 3, 24, 'active'),
    (301, 2009, '0', 3, 24, 'active'),
    (302, 2008, '0', 3, 24, 'active'),
    (303, 2007, '0', 3, 24, 'active'),
    (304, 2006, '0', 3, 24, 'active'),
    (305, 2005, '0', 3, 24, 'active'),
    (306, 2004, '0', 3, 24, 'active'),
    (307, 2003, '0', 3, 24, 'active'),
    (308, 2002, '0', 3, 24, 'active'),
    (309, 2001, '0', 3, 24, 'active'),
    (310, 2000, '0', 3, 24, 'active'),
    (311, 1999, '0', 3, 24, 'active'),
    (312, 1998, '0', 3, 24, 'active'),
    (313, 2010, '0', 3, 25, 'active'),
    (314, 2009, '3632000', 3, 25, 'active'),
    (315, 2008, '3267000', 3, 25, 'active'),
    (316, 2007, '2844000', 3, 25, 'active'),
    (317, 2006, '2588000', 3, 25, 'active'),
    (318, 2005, '2331000', 3, 25, 'active'),
    (319, 2004, '1926000', 3, 25, 'active'),
    (320, 2003, '1139000', 3, 25, 'active'),
    (321, 2002, '977000', 3, 25, 'active'),
    (322, 2001, '765000', 3, 25, 'active'),
    (323, 2000, '716000', 3, 25, 'active'),
    (324, 1999, '671000', 3, 25, 'active'),
    (325, 1998, '630000', 3, 25, 'active'),
    (326, 2010, '0', 3, 26, 'active'),
    (327, 2009, '4064000', 3, 26, 'active'),
    (328, 2008, '3654000', 3, 26, 'active'),
    (329, 2007, '3204000', 3, 26, 'active'),
    (330, 2006, '2907000', 3, 26, 'active'),
    (331, 2005, '2556000', 3, 26, 'active'),
    (332, 2004, '2129000', 3, 26, 'active'),
    (333, 2003, '1179000', 3, 26, 'active'),
    (334, 2002, '0', 3, 26, 'active'),
    (335, 2001, '0', 3, 26, 'active'),
    (336, 2000, '0', 3, 26, 'active'),
    (337, 1999, '0', 3, 26, 'active'),
    (338, 1998, '0', 3, 26, 'active'),
    (339, 2010, '0', 3, 27, 'active'),
    (340, 2009, '4815000', 3, 27, 'active'),
    (341, 2008, '4334000', 3, 27, 'active'),
    (342, 2007, '3789000', 3, 27, 'active'),
    (343, 2006, '0', 3, 27, 'active'),
    (344, 2005, '0', 3, 27, 'active'),
    (345, 2004, '0', 3, 27, 'active'),
    (346, 2003, '0', 3, 27, 'active'),
    (347, 2002, '0', 3, 27, 'active'),
    (348, 2001, '0', 3, 27, 'active'),
    (349, 2000, '0', 3, 27, 'active'),
    (350, 1999, '0', 3, 27, 'active'),
    (351, 1998, '0', 3, 27, 'active'),
    (352, 2010, '0', 3, 28, 'active'),
    (353, 2009, '4496000', 3, 28, 'active'),
    (354, 2008, '4046000', 3, 28, 'active'),
    (355, 2007, '3537000', 3, 28, 'active'),
    (356, 2006, '3141000', 3, 28, 'active'),
    Last edited by corbeeresearch; 08-19-10 at 21:24.

  12. #12
    Join Date
    Aug 2010
    Posts
    17
    3rd to 4th (till 4th brandID and 43rd modelID)
    Code:
    (357, 2005, '0', 3, 28, 'active'),
    (358, 2004, '0', 3, 28, 'active'),
    (359, 2003, '0', 3, 28, 'active'),
    (360, 2002, '0', 3, 28, 'active'),
    (361, 2001, '0', 3, 28, 'active'),
    (362, 2000, '0', 3, 28, 'active'),
    (363, 1999, '0', 3, 28, 'active'),
    (364, 1998, '0', 3, 28, 'active'),
    (365, 2010, '0', 3, 29, 'active'),
    (366, 2009, '5396000', 3, 29, 'active'),
    (367, 2008, '4856000', 3, 29, 'active'),
    (368, 2007, '4248000', 3, 29, 'active'),
    (369, 2006, '0', 3, 29, 'active'),
    (370, 2005, '0', 3, 29, 'active'),
    (371, 2004, '0', 3, 29, 'active'),
    (372, 2003, '0', 3, 29, 'active'),
    (373, 2002, '0', 3, 29, 'active'),
    (374, 2001, '0', 3, 29, 'active'),
    (375, 2000, '0', 3, 29, 'active'),
    (376, 1999, '0', 3, 29, 'active'),
    (377, 1998, '0', 3, 29, 'active'),
    (378, 2010, '7800000', 3, 30, 'active'),
    (379, 2009, '7047000', 3, 30, 'active'),
    (380, 2008, '6341000', 3, 30, 'active'),
    (381, 2007, '5670000', 3, 30, 'active'),
    (382, 2006, '5130000', 3, 30, 'active'),
    (383, 2005, '4590000', 3, 30, 'active'),
    (384, 2004, '0', 3, 30, 'active'),
    (385, 2003, '0', 3, 30, 'active'),
    (386, 2002, '0', 3, 30, 'active'),
    (387, 2001, '0', 3, 30, 'active'),
    (388, 2000, '0', 3, 30, 'active'),
    (389, 1999, '0', 3, 30, 'active'),
    (390, 1998, '0', 3, 30, 'active'),
    (391, 2010, '0', 3, 31, 'active'),
    (392, 2009, '7497000', 3, 31, 'active'),
    (393, 2008, '6746000', 3, 31, 'active'),
    (394, 2007, '6075000', 3, 31, 'active'),
    (395, 2006, '0', 3, 31, 'active'),
    (396, 2005, '0', 3, 31, 'active'),
    (397, 2004, '0', 3, 31, 'active'),
    (398, 2003, '0', 3, 31, 'active'),
    (399, 2002, '0', 3, 31, 'active'),
    (400, 2001, '0', 3, 31, 'active'),
    (401, 2000, '0', 3, 31, 'active'),
    (402, 1999, '0', 3, 31, 'active'),
    (403, 1998, '0', 3, 31, 'active'),
    (404, 2010, '0', 3, 32, 'active'),
    (405, 2009, '8982000', 3, 32, 'active'),
    (406, 2008, '8082000', 3, 32, 'active'),
    (407, 2007, '7272000', 3, 32, 'active'),
    (408, 2006, '0', 3, 32, 'active'),
    (409, 2005, '0', 3, 32, 'active'),
    (410, 2004, '0', 3, 32, 'active'),
    (411, 2003, '0', 3, 32, 'active'),
    (412, 2002, '0', 3, 32, 'active'),
    (413, 2001, '0', 3, 32, 'active'),
    (414, 2000, '0', 3, 32, 'active'),
    (415, 1999, '0', 3, 32, 'active'),
    (416, 1998, '0', 3, 32, 'active'),
    (418, 2009, '10800000', 3, 33, 'active'),
    (420, 2007, '8829000', 3, 33, 'active'),
    (421, 2006, '8010000', 3, 33, 'active'),
    (422, 2005, '0', 3, 33, 'active'),
    (423, 2004, '0', 3, 33, 'active'),
    (424, 2003, '0', 3, 33, 'active'),
    (425, 2002, '0', 3, 33, 'active'),
    (426, 2001, '0', 3, 33, 'active'),
    (427, 2000, '0', 3, 33, 'active'),
    (428, 1999, '0', 3, 33, 'active'),
    (429, 1998, '0', 3, 33, 'active'),
    (430, 2010, '6450000', 3, 33, 'active'),
    (431, 2009, '5625000', 3, 33, 'active'),
    (432, 2008, '4761000', 3, 33, 'active'),
    (433, 2007, '4050000', 3, 33, 'active'),
    (434, 2006, '0', 3, 33, 'active'),
    (435, 2005, '0', 3, 33, 'active'),
    (436, 2004, '0', 3, 33, 'active'),
    (437, 2003, '0', 3, 33, 'active'),
    (438, 2002, '0', 3, 33, 'active'),
    (439, 2001, '0', 3, 33, 'active'),
    (440, 2000, '0', 3, 33, 'active'),
    (441, 1999, '0', 3, 33, 'active'),
    (442, 1998, '0', 3, 33, 'active'),
    (443, 2010, '0', 3, 34, 'active'),
    (444, 2009, '6750000', 3, 34, 'active'),
    (445, 2008, '6309000', 3, 34, 'active'),
    (446, 2007, '5580000', 3, 34, 'active'),
    (447, 2006, '0', 3, 34, 'active'),
    (448, 2005, '0', 3, 34, 'active'),
    (449, 2004, '0', 3, 34, 'active'),
    (450, 2003, '0', 3, 34, 'active'),
    (451, 2002, '0', 3, 34, 'active'),
    (452, 2001, '0', 3, 34, 'active'),
    (453, 2000, '0', 3, 34, 'active'),
    (454, 1999, '0', 3, 34, 'active'),
    (455, 1998, '0', 3, 34, 'active'),
    (456, 2010, '0', 3, 35, 'active'),
    (457, 2009, '5850000', 3, 35, 'active'),
    (458, 2008, '5265000', 3, 35, 'active'),
    (459, 2007, '0', 3, 35, 'active'),
    (460, 2006, '0', 3, 35, 'active'),
    (461, 2005, '0', 3, 35, 'active'),
    (462, 2004, '0', 3, 35, 'active'),
    (463, 2003, '0', 3, 35, 'active'),
    (464, 2002, '0', 3, 35, 'active'),
    (465, 2001, '0', 3, 35, 'active'),
    (466, 2000, '0', 3, 35, 'active'),
    (467, 1999, '0', 3, 35, 'active'),
    (468, 1998, '0', 3, 35, 'active'),
    (469, 2010, '0', 3, 36, 'active'),
    (470, 2009, '4950000', 3, 36, 'active'),
    (471, 2008, '4455000', 3, 36, 'active'),
    (472, 2007, '0', 3, 36, 'active'),
    (473, 2006, '0', 3, 36, 'active'),
    (474, 2005, '0', 3, 36, 'active'),
    (475, 2004, '0', 3, 36, 'active'),
    (476, 2003, '0', 3, 36, 'active'),
    (477, 2002, '0', 3, 36, 'active'),
    (478, 2001, '0', 3, 36, 'active'),
    (479, 2000, '0', 3, 36, 'active'),
    (480, 1999, '0', 3, 36, 'active'),
    (481, 1998, '0', 3, 36, 'active'),
    (482, 2010, '0', 3, 37, 'active'),
    (483, 2009, '0', 3, 37, 'active'),
    (484, 2008, '0', 3, 37, 'active'),
    (485, 2007, '0', 3, 37, 'active'),
    (486, 2006, '0', 3, 37, 'active'),
    (487, 2005, '0', 3, 37, 'active'),
    (488, 2004, '0', 3, 37, 'active'),
    (489, 2003, '0', 3, 37, 'active'),
    (490, 2002, '0', 3, 37, 'active'),
    (491, 2001, '0', 3, 37, 'active'),
    (492, 2000, '0', 3, 37, 'active'),
    (493, 1999, '0', 3, 37, 'active'),
    (494, 1998, '0', 3, 37, 'active'),
    (495, 2010, '0', 3, 38, 'active'),
    (496, 2009, '0', 3, 38, 'active'),
    (497, 2008, '0', 3, 38, 'active'),
    (498, 2007, '0', 3, 38, 'active'),
    (499, 2006, '0', 3, 38, 'active'),
    (500, 2005, '1944000', 3, 38, 'active'),
    (501, 2004, '1692000', 3, 38, 'active'),
    (502, 2003, '1251000', 3, 38, 'active'),
    (503, 2002, '1125000', 3, 38, 'active'),
    (504, 2001, '1035000', 3, 38, 'active'),
    (505, 2000, '900000', 3, 38, 'active'),
    (506, 1999, '0', 3, 38, 'active'),
    (507, 1998, '0', 3, 38, 'active'),
    (508, 2010, '0', 3, 39, 'active'),
    (509, 2009, '0', 3, 39, 'active'),
    (510, 2008, '0', 3, 39, 'active'),
    (511, 2007, '0', 3, 39, 'active'),
    (512, 2006, '0', 3, 39, 'active'),
    (513, 2005, '2228000', 3, 39, 'active'),
    (514, 2004, '2025000', 3, 39, 'active'),
    (515, 2003, '1796000', 3, 39, 'active'),
    (516, 2002, '1409000', 3, 39, 'active'),
    (517, 2001, '1242000', 3, 39, 'active'),
    (518, 2000, '1170000', 3, 39, 'active'),
    (519, 1999, '0', 3, 39, 'active'),
    (520, 1998, '0', 3, 39, 'active'),
    (521, 2010, '0', 3, 40, 'active'),
    (522, 2009, '3825000', 3, 40, 'active'),
    (523, 2008, '3443000', 3, 40, 'active'),
    (524, 2007, '3096000', 3, 40, 'active'),
    (525, 2006, '0', 3, 40, 'active'),
    (526, 2005, '0', 3, 40, 'active'),
    (527, 2004, '0', 3, 40, 'active'),
    (528, 2003, '0', 3, 40, 'active'),
    (529, 2002, '0', 3, 40, 'active'),
    (530, 2001, '0', 3, 40, 'active'),
    (531, 2000, '0', 3, 40, 'active'),
    (532, 1999, '0', 3, 40, 'active'),
    (533, 1998, '0', 3, 40, 'active'),
    (534, 2010, '0', 3, 41, 'active'),
    (535, 2009, '4680000', 3, 41, 'active'),
    (536, 2008, '4010000', 3, 41, 'active'),
    (537, 2007, '3609000', 3, 41, 'active'),
    (538, 2006, '0', 3, 41, 'active'),
    (539, 2005, '0', 3, 41, 'active'),
    (540, 2004, '0', 3, 41, 'active'),
    (541, 2003, '0', 3, 41, 'active'),
    (542, 2002, '0', 3, 41, 'active'),
    (543, 2001, '0', 3, 41, 'active'),
    (544, 2000, '0', 3, 41, 'active'),
    (545, 1999, '0', 3, 41, 'active'),
    (546, 1998, '0', 3, 41, 'active'),
    (547, 2010, '1990000', 4, 42, 'active'),
    (548, 2009, '1660000', 4, 42, 'active'),
    (549, 2008, '1290000', 4, 42, 'active'),
    (550, 2007, '0', 4, 42, 'active'),
    (551, 2006, '0', 4, 42, 'active'),
    (552, 2005, '0', 4, 42, 'active'),
    (553, 2004, '0', 4, 42, 'active'),
    (554, 2003, '0', 4, 42, 'active'),
    (555, 2002, '0', 4, 42, 'active'),
    (556, 2001, '0', 4, 42, 'active'),
    (557, 2000, '0', 4, 42, 'active'),
    (558, 1999, '0', 4, 42, 'active'),
    (559, 1998, '0', 4, 42, 'active'),
    (560, 2010, '0', 4, 43, 'active'),
    (561, 2009, '0', 4, 43, 'active'),
    (562, 2008, '1350000', 4, 43, 'active'),
    (563, 2007, '1110000', 4, 43, 'active'),
    (564, 2006, '1070000', 4, 43, 'active'),
    (565, 2005, '1030000', 4, 43, 'active'),
    (566, 2004, '0', 4, 43, 'active'),
    (567, 2003, '0', 4, 43, 'active'),
    (568, 2002, '0', 4, 43, 'active'),
    (569, 2001, '0', 4, 43, 'active'),
    (570, 2000, '0', 4, 43, 'active'),
    (571, 1999, '0', 4, 43, 'active'),
    (572, 1998, '0', 4, 43, 'active'),
    Last edited by corbeeresearch; 08-19-10 at 21:25.

  13. #13
    Join Date
    Aug 2010
    Posts
    17
    This is the query I used with joins, having inaccuracy

    Code:
    SELECT `carbrand`.`brandID` , `carbrand`.`brandName` , `carmodel`.`brandID` , `carmodel`.`carType` , `caryear`.`caryearID` , `caryear`.`price` , `caryear`.`brandID` , `bestmatch`.`carBrand` , `carmodel`.`modelID` , caryear.year
    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` = '1998'
    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`
    But if you query the original caryear table and looked at the brandID=4 modelID=42 and year=1998, it has no price

    Code:
    SELECT * FROM `caryear` 
    WHERE brandID=4 and modelID=42 and year=1998
    Last edited by corbeeresearch; 08-19-10 at 21:25.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by corbeeresearch View Post
    This is the query I used with joins, having inaccuracy
    sorry, that query doens't work, you did not supply any data for bestmatch

    however, i can see why the price might be wrong, it's your GROUP BY clause

    i thought you were getting inaccurate prices running ~my~ query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Aug 2010
    Posts
    17
    Oh, sorry,

    Code:
    INSERT INTO `bestmatch` (`bestid`, `carBrand`, `insurance`, `match`) VALUES
    (1, 3, 2, 'Better'),
    (2, 3, 1, 'Best'),
    (3, 3, 5, 'Not Covered'),
    (4, 3, 4, 'Not Covered'),
    (5, 3, 7, 'Not Covered'),
    (6, 3, 8, 'Not Covered'),
    (7, 4, 2, 'Best'),
    (8, 4, 1, 'Better'),
    (9, 4, 5, 'Not Covered'),
    (10, 4, 4, 'Not Covered'),
    (11, 4, 7, 'Better'),
    (12, 4, 8, 'Better'),
    (13, 5, 2, 'Best'),
    (14, 5, 1, 'Not Covered'),
    (15, 5, 5, 'Better'),
    (16, 5, 4, 'Not Covered'),
    (17, 5, 7, 'Better'),
    (18, 5, 8, 'Not Covered'),
    (19, 6, 2, 'Not Covered'),
    (20, 6, 1, 'Not Covered'),
    (21, 6, 5, 'Not Covered'),
    (22, 6, 4, 'Not Covered'),
    (23, 6, 7, 'Best'),
    (24, 6, 8, 'Not Covered'),
    (25, 7, 2, 'Best'),
    (26, 7, 1, 'Better'),
    (27, 7, 5, 'Better'),
    (28, 7, 4, 'Not Covered'),
    (29, 7, 7, 'Better'),
    (30, 7, 8, 'Better'),
    (31, 8, 2, 'Best'),
    (32, 8, 1, 'Not Covered'),
    (33, 8, 5, 'Not Covered'),
    (34, 8, 4, 'Not Covered'),
    (35, 8, 7, 'Better'),
    (36, 8, 8, 'Better'),
    (37, 11, 2, 'Best'),
    (38, 11, 1, 'Better'),
    (39, 11, 5, 'Not Covered'),
    (40, 11, 4, 'Not Covered'),
    (41, 11, 7, 'Not Covered'),
    (42, 11, 8, 'Not Covered'),
    (43, 45, 2, 'Best'),
    (44, 45, 1, 'Not Covered'),
    (45, 45, 5, 'Better'),
    (46, 45, 4, 'Not Covered'),
    (47, 45, 7, 'Better'),
    (48, 45, 8, 'Better'),
    (49, 46, 2, 'Not Covered'),
    (50, 46, 1, 'Not Covered'),
    (51, 46, 5, 'Not Covered'),
    (52, 46, 4, 'Not Covered'),
    (53, 46, 7, 'Best'),
    (54, 46, 8, 'Not Covered'),
    (55, 13, 2, 'Best'),
    (56, 13, 1, 'Better'),
    (57, 13, 5, 'Not Covered'),
    (58, 13, 4, 'Better'),
    (59, 13, 7, 'Better'),
    (60, 13, 8, 'Better'),
    (61, 14, 2, 'Best'),
    (62, 14, 1, 'Not Covered'),
    (63, 14, 5, 'Not Covered'),
    (64, 14, 4, 'Not Covered'),
    (65, 14, 7, 'Not Covered'),
    (66, 14, 8, 'Not Covered'),
    (67, 47, 2, 'Best'),
    (68, 47, 1, 'Not Covered'),
    (69, 47, 5, 'Not Covered'),
    (70, 47, 4, 'Not Covered'),
    (71, 47, 7, 'Not Covered'),
    (72, 47, 8, 'Not Covered'),
    (73, 16, 2, 'Better'),
    (74, 16, 1, 'Not Covered'),
    (75, 16, 5, 'Better'),
    (76, 16, 4, 'Not Covered'),
    (77, 16, 7, 'Best'),
    (78, 16, 8, 'Better'),
    (79, 17, 2, 'Best'),
    (80, 17, 1, 'Better'),
    (81, 17, 5, 'Better'),
    (82, 17, 4, 'Not Covered'),
    (83, 17, 7, 'Better'),
    (84, 17, 8, 'Better'),
    (85, 15, 2, 'Best'),
    (86, 15, 1, 'Better'),
    (87, 15, 5, 'Better'),
    (88, 15, 4, 'Not Covered'),
    (89, 15, 7, 'Better'),
    (90, 15, 8, 'Better'),
    (91, 18, 2, 'Better'),
    (92, 18, 1, 'Better'),
    (93, 18, 5, 'Better'),
    (94, 18, 4, 'Best'),
    (95, 18, 7, 'Better'),
    (96, 18, 8, 'Better'),
    (97, 19, 2, 'Best'),
    (98, 19, 1, 'Better'),
    (99, 19, 5, 'Better'),
    (100, 19, 4, 'Not Covered'),
    (101, 19, 7, 'Better'),
    (102, 19, 8, 'Better'),
    (103, 20, 2, 'Best'),
    (104, 20, 1, 'Not Covered'),
    (105, 20, 5, 'Not Covered'),
    (106, 20, 4, 'Not Covered'),
    (107, 20, 7, 'Best'),
    (108, 20, 8, 'Better'),
    (109, 21, 2, 'Best'),
    (110, 21, 1, 'Not Covered'),
    (111, 21, 5, 'Not Covered'),
    (112, 21, 4, 'Not Covered'),
    (113, 21, 7, 'Better'),
    (114, 21, 8, 'Better'),
    (115, 22, 2, 'Best'),
    (116, 22, 1, 'Better'),
    (117, 22, 5, 'Better'),
    (118, 22, 4, 'Not Covered'),
    (119, 22, 7, 'Better'),
    (120, 22, 8, 'Better'),
    (121, 23, 2, 'Better'),
    (122, 23, 1, 'Not Covered'),
    (123, 23, 5, 'Not Covered'),
    (124, 23, 4, 'Not Covered'),
    (125, 23, 7, 'Best'),
    (126, 23, 8, 'Better'),
    (127, 24, 2, 'Better'),
    (128, 24, 1, 'Not Covered'),
    (129, 24, 5, 'Not Covered'),
    (130, 24, 4, 'Not Covered'),
    (131, 24, 7, 'Better'),
    (132, 24, 8, 'Best'),
    (133, 25, 2, 'Better'),
    (134, 25, 1, 'Better'),
    (135, 25, 5, 'Better'),
    (136, 25, 4, 'Not Covered'),
    (137, 25, 7, 'Better'),
    (138, 25, 8, 'Best'),
    (139, 26, 2, 'Best'),
    (140, 26, 1, 'Not Covered'),
    (141, 26, 5, 'Better'),
    (142, 26, 4, 'Not Covered'),
    (143, 26, 7, 'Better'),
    (144, 26, 8, 'Better'),
    (145, 27, 2, 'Best'),
    (146, 27, 1, 'Better'),
    (147, 27, 5, 'Better'),
    (148, 27, 4, 'Better'),
    (149, 27, 7, 'Better'),
    (150, 27, 8, 'Better'),
    (151, 28, 2, 'Better'),
    (152, 28, 1, 'Best'),
    (153, 28, 5, 'Better'),
    (154, 28, 4, 'Better'),
    (155, 28, 7, 'Better'),
    (156, 28, 8, 'Better'),
    (157, 29, 2, 'Best'),
    (158, 29, 1, 'Best'),
    (159, 29, 5, 'Better'),
    (160, 29, 4, 'Better'),
    (161, 29, 7, 'Better'),
    (162, 29, 8, 'Better'),
    (163, 31, 2, 'Not Covered'),
    (164, 31, 1, 'Best'),
    (165, 31, 5, 'Not Covered'),
    (166, 31, 4, 'Not Covered'),
    (167, 31, 7, 'Not Covered'),
    (168, 31, 8, 'Better'),
    (169, 32, 2, 'Better'),
    (170, 32, 1, 'Not Covered'),
    (171, 32, 5, 'Better'),
    (172, 32, 4, 'Best'),
    (173, 32, 7, 'Better'),
    (174, 32, 8, 'Better'),
    (175, 33, 2, 'Best'),
    (176, 33, 1, 'Better'),
    (177, 33, 5, 'Better'),
    (178, 33, 4, 'Not Covered'),
    (179, 33, 7, 'Better'),
    (180, 33, 8, 'Better'),
    (181, 34, 2, 'Best'),
    (182, 34, 1, 'Better'),
    (183, 34, 5, 'Better'),
    (184, 34, 4, 'Better'),
    (185, 34, 7, 'Better'),
    (186, 34, 8, 'Better'),
    (187, 35, 2, 'Better'),
    (188, 35, 1, 'Better'),
    (189, 35, 5, 'Better'),
    (190, 35, 4, 'Best'),
    (191, 35, 7, 'Better'),
    (192, 35, 8, 'Better'),
    (193, 37, 2, 'Better'),
    (194, 37, 1, 'Not Covered'),
    (195, 37, 5, 'Better'),
    (196, 37, 4, 'Not Covered'),
    (197, 37, 7, 'Best'),
    (198, 37, 8, 'Better'),
    (199, 48, 2, 'Better'),
    (200, 48, 1, 'Not Covered'),
    (201, 48, 5, 'Better'),
    (202, 48, 4, 'Not Covered'),
    (203, 48, 7, 'Best'),
    (204, 48, 8, 'Better'),
    (205, 1, 2, 'Best'),
    (206, 1, 1, 'Not Covered'),
    (207, 1, 5, 'Not Covered'),
    (208, 1, 4, 'Not Covered'),
    (209, 1, 7, 'Not Covered'),
    (210, 1, 8, 'Not Covered'),
    (211, 2, 2, 'Best'),
    (212, 2, 1, 'Not Covered'),
    (213, 2, 5, 'Not Covered'),
    (214, 2, 4, 'Not Covered'),
    (215, 2, 7, 'Not Covered'),
    (216, 2, 8, 'Not Covered'),
    (217, 9, 2, 'Best'),
    (218, 9, 1, 'Not Covered'),
    (219, 9, 5, 'Not Covered'),
    (220, 9, 4, 'Not Covered'),
    (221, 9, 7, 'Not Covered'),
    (222, 9, 8, 'Not Covered'),
    (223, 49, 2, 'Best'),
    (224, 49, 1, 'Not Covered'),
    (225, 49, 5, 'Not Covered'),
    (226, 49, 4, 'Not Covered'),
    (227, 49, 7, 'Not Covered'),
    (228, 49, 8, 'Not Covered'),
    (229, 30, 2, 'Best'),
    (230, 30, 1, 'Not Covered'),
    (231, 30, 5, 'Not Covered'),
    (232, 30, 4, 'Not Covered'),
    (233, 30, 7, 'Not Covered'),
    (234, 30, 8, 'Not Covered');

Posting Permissions

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