CREATE DATABASE sex;
USE sex;

CREATE TABLE tovar (
  Kod CHAR( 4 ),
  Name VARCHAR( 50 )
);

CREATE TABLE Zena (
  Kod CHAR( 4 ),
  Data DATE,
  Price SMALLINT( 5 ) UNSIGNED
);

INSERT INTO tovar VALUES ('1077', 'Чайник Скарлет-1022');
INSERT INTO tovar VALUES ('5618', 'Утюг PHILIPS-78');
INSERT INTO tovar VALUES ('3388', 'Телевизор Sony E-25');

INSERT INTO Zena VALUES ('5618', '2002-12-30',  900 );
INSERT INTO Zena VALUES ('5618', '2003-01-25',  920 );
INSERT INTO Zena VALUES ('5618', '2003-03-02',  950 );

INSERT INTO Zena VALUES ('1077', '2002-12-30',  750 );
INSERT INTO Zena VALUES ('1077', '2003-02-18',  800 );

INSERT INTO Zena VALUES ('3388', '2002-12-30',  6580 );
INSERT INTO Zena VALUES ('3388', '2003-01-15',  7200 );
INSERT INTO Zena VALUES ('3388', '2003-03-02',  7000 );

INSERT INTO Zena VALUES ('5730', '2002-12-31',  11290 );
INSERT INTO Zena VALUES ('5730', '2003-01-06',  11790 );
INSERT INTO Zena VALUES ('5730', '2003-02-05',  12190 );

SELECT tovar.Name, zena.Price
FROM tovar,zena 
WHERE zena.Data <=  "2003 - 02 - 01 " AND (zena.Kod = tovar.Kod)
GROUP by tovar.Kod

SELECT tovar.Name,
MIN(CONCAT(ABS(to_days(zena.Data)-to_days( "2003 - 02 - 01 ")),  "0101010101 #", zena.Price)) as _diff
FROM tovar
LEFT JOIN zena ON tovar.Kod = zena.Kod AND zena.Data< "2003 - 02 - 01 "
group by tovar.Kod
HAVING _diff != ""
ORDER BY tovar.Name

SELECT INTO temptable UNIQUE(zena.Kod), zena.Data, zena.Price
FROM zena 
WHERE zena.Data <=  "2003 - 02 - 01 "
ORDER BY Kod, Data DESC

CREATE TEMPORARY TABLE pricetmp (Kod CHAR( 4 ) NOT NULL,
Price SMALLINT( 5 ) UNSIGNED NOT NULL,
FakeNum SMALLINT( 3 ) UNSIGNED NOT NULL,
PRIMARY KEY (Kod));

INSERT INTO pricetmp SELECT zena.Kod, zena.Price,
MIN(to_days(NOW())-to_days(zena.Data)) AS sex
FROM zena
WHERE zena.Data <= NOW()
GROUP BY Kod;

SELECT tovar.Name,
IFNULL(pricetmp.Price, 0 ) AS Price
FROM tovar
LEFT JOIN pricetmp ON pricetmp.Kod=tovar.Kod
WHERE tovar.Folder='0'
GROUP BY tovar.Kod;

CREATE TEMPORARY TABLE pricetmp (Kod CHAR( 4 ) NOT NULL,
Price SMALLINT( 5 ) UNSIGNED NOT NULL,
PRIMARY KEY (Kod));

INSERT INTO pricetmp SELECT Kod, Price
FROM zena WHERE Data <= NOW()
ORDER BY Kod, Data DESC;