Re: LEFTJOIN ???

[Tony Mole]

Hi Peter

OK, here is SHOW CREATE TABLE for stock_price;

CREATE TABLE `stock_price` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Man_Pt_Nr` varchar(50) COLLATE latin1_general_ci NOT NULL,
`Sup_Pt_Nr` varchar(50) COLLATE latin1_general_ci NOT NULL,
`Manufacturer` varchar(50) COLLATE latin1_general_ci NOT NULL,
`Description` varchar(100) COLLATE latin1_general_ci NOT NULL,
`Qty` int(4) NOT NULL,
`Cost` decimal(6,2) DEFAULT NULL,
`RRP` decimal(6,2) DEFAULT NULL,
`Weight` decimal(6,2) DEFAULT NULL COMMENT 'Weight (Kg)',
`ETA` varchar(10) COLLATE latin1_general_ci DEFAULT NULL,
`Disti` varchar(50) COLLATE latin1_general_ci NOT NULL,
`Condition` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=765773 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Check4'



Below is SHOW CREATE TABLE for stock_total;

CREATE TABLE `stock_total` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Sup_Pt_Nr` varchar(50) COLLATE latin1_general_ci NOT NULL,
`Qty` int(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=814241 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Check4'



Examples of rows of data from stock_price;

id, Man_Pt_Nr, Sup_Pt_Nr, Manufacturer, Description, Qty, Cost, RRP, Weight, ETA, Disti, Condition
656377, N074930A, A151016, FUJIFILM, 2GB COMPACTFLASH CARD, 0, 7.13, 8.99, 0.01, NULL, IM, NULL
656378, 5500-051, B090363, AXIS - SPARE PART CS MOUNTVARIFOCAL 3-8MM, 0, 83.45, 0, 0, NULL, IM, NULL
656379, 5500-061, B090462, AXIS - SPARE PART CS MOUNT VARIFOCAL 3-8MM, 0, 104.61, 0, 0, NULL, IM, NULL
656380, 5500-071, B090466, AXIS - SPARE PART CS MOUNT VARIFOCAL, 0, 14.71, 0, 0, NULL, IM, NULL

Examples of rows of data from stock_total;

id, Sup_Pt_Nr, Qty
697921, A151016, 5
697922, B090363, 87
697923, B090462, 2
697924, B090464, 17




The output I would expect (or trying to get) from joining these two sets of data should be;

id, Man_Pt_Nr, Sup_Pt_Nr, Manufacturer, Description, Qty, Cost, RRP, Weight, ETA, Disti, Condition
656377, N074930A, A151016, FUJIFILM, 2GB COMPACTFLASH CARD, 5, 7.13, 8.99, 0.01, NULL, IM, NULL
656378, 5500-051, B090363, AXIS - SPARE PART CS MOUNTVARIFOCAL 3-8MM, 87, 83.45, 0, 0, NULL, IM, NULL
656379, 5500-061, B090462, AXIS - SPARE PART CS MOUNT VARIFOCAL 3-8MM, 2, 104.61, 0, 0, NULL, IM, NULL
656380, 5500-071, B090466, AXIS - SPARE PART CS MOUNT VARIFOCAL, 0, 14.71, 0, 0, NULL, IM, NULL

What I would expect to happen is that the Qty should be copied from stock_total into the Qty column in stock_price where Sup_Pt_Nr is identical. On the last line (656380) as there is no match in Sup_Pt_Nr the value in Qty remains at 0.

In practise what seems to happen is some kind of loop (speculating) which means that if I run the script in PHPMyAdmin it just says "loading" and has to be rebooted to get back into the server. I have also tried running it from a PHP script and it just sits there with a spinning hour-glass... again needing a reboot to get it back online!

Thanks and I appreciate any help.