Assid

select ig.iid as itineraryid , ig.id as groupid, gf.id, gf.fname, sum(fi.amt), sum(fr.amt)from family_receipt_dtls as fr, family_invoice as fi, group_families as gf, itinerary_groups as igwhere ig.active=1 and gf.gid=ig.id and fr.fid=gf.id and fi.fid=gf.idgroup by gf.id;CREATE TABLE `family_invoice` (  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID',  `fid` int(10) unsigned NOT NULL COMMENT 'Family ID',  `invoiceto` text NOT NULL COMMENT 'Invoice To',  `selfinvoice` varchar(15) NOT NULL COMMENT 'Managed invoice',  `dt` date NOT NULL COMMENT 'Date of Invoice',  `amt` int(10) unsigned NOT NULL COMMENT 'Amount',  `amtwords` text NOT NULL COMMENT 'Amount in words',  `comments` text COMMENT 'Comments',  `agent` varchar(25) NOT NULL COMMENT 'Agent',  PRIMARY KEY  (`id`),  KEY `fid` (`fid`,`selfinvoice`,`dt`),  KEY `agent` (`agent`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Invoice generation' AUTO_INCREMENT=3 ;-- ---------------------------------------------------------- -- Table structure for table `family_receipt_dtls`-- CREATE TABLE `family_receipt_dtls` (  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID',  `dttm` datetime NOT NULL COMMENT 'Date time of entry',  `fid` int(10) unsigned NOT NULL COMMENT 'Family ID',  `receivedfrom` text NOT NULL COMMENT 'Received From',  `pmethod` tinyint(3) unsigned NOT NULL COMMENT 'Payment Method',  `docno` varchar(50) NOT NULL COMMENT 'Referance number of payment method  such as cheque draft number',  `amt` int(10) unsigned NOT NULL COMMENT 'Amount paid',  `amtwords` text NOT NULL COMMENT 'Amount in words',  `comments` text NOT NULL COMMENT 'Comments',  `agent` varchar(25) NOT NULL COMMENT 'Agent',  PRIMARY KEY  (`id`),  KEY `dttm` (`dttm`,`fid`,`pmethod`),  KEY `agent` (`agent`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Payments made by family/inidividuals' AUTO_INCREMENT=2 ;-- ---------------------------------------------------------- -- Table structure for table `group_families`-- CREATE TABLE `group_families` (  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique Family ID',  `gid` int(10) unsigned NOT NULL COMMENT 'Group ID',  `fname` varchar(75) NOT NULL COMMENT 'Family Name',  `familyhead` bigint(20) unsigned default '0' COMMENT 'Head of the family',  PRIMARY KEY  (`id`),  KEY `gid` (`gid`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Group Families' AUTO_INCREMENT=6 ;-- ---------------------------------------------------------- -- Table structure for table `itinerary_groups`-- CREATE TABLE `itinerary_groups` (  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique Group ID',  `iid` int(10) unsigned NOT NULL COMMENT 'Itinerary ID',  `grpname` varchar(150) NOT NULL COMMENT 'Group name',  `fromloc` varchar(50) NOT NULL COMMENT 'From Location',  `toloc` varchar(50) NOT NULL COMMENT 'To Location',  `fromdt` date NOT NULL COMMENT 'From Date',  `todt` date NOT NULL COMMENT 'Till Date',  `grouplimit` mediumint(8) unsigned NOT NULL COMMENT 'Group Limit',  `comments` text NOT NULL COMMENT 'Comments',  `active` tinyint(1) NOT NULL default '1' COMMENT 'is this group active or no',  `agent` varchar(25) NOT NULL COMMENT 'Agent',  PRIMARY KEY  (`id`),  KEY `iid` (`iid`),  KEY `active` (`active`),  KEY `agent` (`agent`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Groups for this particular itinerary' AUTO_INCREMENT=5 ;