shatireazam (1) [Avatar] Offline
#1
I’ve two tables namely deliverydetails and supplybilldetails with following SQL create statements

DROP TABLE IF EXISTS `labinv4`.`deliverydetails`;
CREATE TABLE `labinv4`.`deliverydetails` (
`id` int(10) unsigned NOT NULL auto_increment,
`deliveryid` int(10) unsigned NOT NULL,
`qty` decimal(10,2) NOT NULL,
`LotNo` varchar(20) default NULL,
`Expiry` date default NULL,
`ManDate` date default NULL,
`billdetailid` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `FK_delivery_details` (`deliveryid`),
KEY `FK_BillDetailid` (`billdetailid`),
CONSTRAINT `FK_BillDetailid` FOREIGN KEY (`billdetailid`) REFERENCES `supplybilldetail` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_delivery` FOREIGN KEY (`deliveryid`) REFERENCES `delivery` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `labinv4`.`supplybilldetail`;
CREATE TABLE `labinv4`.`supplybilldetail` (
`id` int(10) unsigned NOT NULL auto_increment,
`billid` int(10) unsigned NOT NULL,
`Qty` decimal(10,2) NOT NULL,
`LotNo` varchar(20) default NULL,
`ManDate` date default NULL,
`Expiry` date default NULL,
`itemdetailid` int(10) unsigned NOT NULL,
`podetailid` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `FK_supplybilldetail_bill` (`billid`),
KEY `FK_bill_item` (`itemdetailid`),
KEY `FK_podetail_sbdetail` (`podetailid`),
CONSTRAINT `FK_bill_item` FOREIGN KEY (`itemdetailid`) REFERENCES `itemdetails` (`iditemDetails`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_podetail_sbdetail` FOREIGN KEY (`podetailid`) REFERENCES `purchaseorderdetails` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_supplybilldetail_bill` FOREIGN KEY (`billid`) REFERENCES `supplierbill` (`idinvVendorBill`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



Their respective classes are
public class Deliverydetails implements java.io.Serializable {
private Integer id;
private Supplybilldetail supplybilldetail;
private Delivery delivery;
private BigDecimal qty;
private String lotNo;
private Date expiry;
private Date manDate;

getters and setters are omitted……..

public class Supplybilldetail implements java.io.Serializable {
private Integer id;
private Itemdetails itemdetails;
private Purchaseorderdetails purchaseorderdetails;
private Supplierbill supplierbill;
private BigDecimal qty;
private String lotNo;
private Date manDate;
private Date expiry;
private Set deliverydetailses = new HashSet(0);

getters and setters are omitted……..



Tables are connected with supplybilldetail.id  deliverydetails.billdetailid

Problem is that I need to find the HQL query for the group sum of deliverydetails.qty on deliverydetails.billdetailid but I simply can’t. Following query works
“from Supplybilldetail as s left outer join s.deliverydetailses as d group by s.id “

I want to add something like
“ where s.qty > sum(d.qty) group by s.id “ can somebody help me out????