I have two tables. One contains articles, the other one price quotations. I'm trying to select [EAN]
from the articles table and price
from the quotation table if dimensions in both tables are matching.
Here is the example I'm playing around with:
DROP TEMPORARY TABLE IF EXISTS haha;
CREATE TEMPORARY TABLE haha (
tmp_kto VARCHAR(7) DEFAULT ''
, tmp_kz VARCHAR(3) DEFAULT ''
, tmp_preisliste VARCHAR(1) DEFAULT ''
, tmp_eg VARCHAR(3) DEFAULT ''
, tmp_length VARCHAR(3) DEFAULT ''
, tmp_width VARCHAR(3) DEFAULT ''
, tmp_price DECIMAL(10,2) DEFAULT 0.00
) ENGINE=MEMORY;
INSERT INTO haha (
tmp_kz, tmp_kto, tmp_preisliste, tmp_eg, tmp_length, tmp_width, tmp_price
) VALUES (
"000","4316134","I","520","150","200","14.95"
);
INSERT INTO haha (
tmp_kz, tmp_kto, tmp_preisliste, tmp_eg, tmp_length, tmp_width, tmp_price
) VALUES (
"001","112233","L","520","150","200","17.95"
);
Matching criteria should be [EG]
and [size]
. For size
I need to substring the relevant size out of the field (no other way, I think - format is e.g. 050x250cm).
SELECT ean, preis
FROM (
SELECT a.ean AS ean
, h.tmp_price AS preis
FROM articles AS a
LEFT JOIN haha AS h
ON h.tmp_eg = a.eg
AND h.tmp_kz = "000"
AND h.tmp_kto = "0000000"
AND h.tmp_preisliste = "I"
AND h.tmp_length = SUBSTRING(a.size,1,3)
AND h.tmp_width = SUBSTRING(a.size,4,3)
) AS temp;
Which does not work.
Question:
What do I need to change to make it work?
EDIT
2nd table looks like this:
CREATE TABLE `articles` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`ean` VARCHAR(35) NULL DEFAULT NULL,
`size` VARCHAR(35) NULL DEFAULT NULL,
`eg` VARCHAR(35) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;
INSERT INTO articles (
ean, size, eg
) VALUES (
"1111111111111","150x200cm","520"
);
INSERT INTO articles (
ean, size, eg
) VALUES (
"2222222222222","150x220cm","520"
);
Thanks for help!
why dont you use simply like that ?
SELECT a.ean, h.tmp_price AS preis
FROM articles AS a
LEFT JOIN haha AS h
ON h.tmp_eg = a.eg
WHERE h.tmp_kz = "000"
AND h.tmp_kto = "0000000"
AND h.tmp_preisliste = "I"
AND h.tmp_length = SUBSTRING(a.size,1,3)
AND h.tmp_width = SUBSTRING(a.size,4,3)