cvele |
17. 10. 2008. 13:24 |
[REŠENO] Pomoc oko select mysql (valjda join na samog sebe...)
U jednoj tabeli se nalaze stavke koje moraju biti u statusu COMPLETE da bi narudzbina (orderid) bila izvrsena.
Potrebno je u jednom upitu izvuci sve narudzbine (one koje imaju isti orderid), gde je status svake stavke COMPLETE.
Huh... valjda je nesto jasno :/
Kôd:
CREATE TABLE IF NOT EXISTS `costumers_products` (
`cp_id` int(50) unsigned NOT NULL auto_increment,
`costumer_id` int(20) NOT NULL,
`product_id` int(20) NOT NULL,
`storehouse_id` int(5) NOT NULL,
`date` timestamp NULL default CURRENT_TIMESTAMP,
`price` varchar(20) collate utf8_unicode_ci NOT NULL,
`status` varchar(100) collate utf8_unicode_ci NOT NULL default 'PENDING',
`user_id` int(10) NOT NULL default '-1',
`orderid` varchar(50) collate utf8_unicode_ci default '0',
PRIMARY KEY (`cp_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;
INSERT INTO `costumers_products` (`cp_id`, `costumer_id`, `product_id`, `storehouse_id`, `date`, `price`, `status`, `user_id`, `orderid`) VALUES
(1, 13, 30, 6, '2008-10-17 12:23:10', '454', 'COMPLETED', 4, 'ptbs18wq/13'),
(2, 13, 29, 3, '2008-10-17 12:23:10', '454', 'COMPLETED', 4, 'ptbs18wq/13'),
(3, 14, 27, 5, '2008-10-17 13:16:11', '223', 'PENDING', 4, 'p6n4cvrt/14'),
(4, 14, 16, 2, '2008-10-17 13:16:12', '223', 'PENDING', 4, 'p6n4cvrt/14'),
(5, 14, 31, 7, '2008-10-17 13:16:12', '223', 'COMPLETED', 4, 'p6n4cvrt/14'),
(6, 14, 31, 7, '2008-10-17 13:16:41', '223', 'PENDING', 4, 'hjs1pktd/14'),
(7, 14, 31, 7, '2008-10-17 13:16:41', '223', 'PENDING', 4, 'hjs1pktd/14');
|