hi i need to create a query that will query my 6 tables that have an amount column & date paid column.
i need to total up all the amounts due, so therefore the datepaid column would be null.
can anyone help how i would be able to view how much is due from 6 tables?
tables:
product1
product2
product3
product4
product5
product6
each have amount column (decimal) and date paid column(date)
many thanks
select sum(sub_amt) total_due from (
select sum(amount) sub_amt from product1 where date is null
union
select sum(amount) sub_amt from product2 where date is null
union
select sum(amount) sub_amt from product3 where date is null
union
select sum(amount) sub_amt from product4 where date is null
union
select sum(amount) sub_amt from product5 where date is null
union
select sum(amount) sub_amt from product6 where date is null
) aThanks for that, below is my database setup if you could help me implement that into my setup with the tables and colums i would appreciate :)
--
-- Table structure for table `clients`
--
CREATE TABLE `clients` (
`ClientID` int(11) NOT NULL auto_increment,
`Title` varchar(5) default NULL,
`UserFirstName` varchar(50) default NULL,
`UserLastName` varchar(50) default NULL,
`DateOfBirth` date default NULL,
`UserEmail` varchar(500) default NULL,
`UserAddress` varchar(100) default NULL,
`UserAddress2` varchar(50) default NULL,
`UserState` varchar(45) default NULL,
`UserCity` varchar(90) default NULL,
`UserZip` varchar(12) default NULL,
`UserCountry` varchar(20) default NULL,
`UserPhone` varchar(20) default NULL,
`UserMob` varchar(20) default NULL,
`ReferredBy` varchar(50) default NULL,
`InitialEnquiry` varchar(100) default NULL,
`Conversion` tinyint(1) default '0',
`Status` varchar(75) default 'Lead',
`ClientUserID` int(11) default NULL,
`referredby_RefID` int(11) default NULL,
`SecondTitle` varchar(5) default NULL,
`SecFirstName` varchar(50) default NULL,
`SecLastName` varchar(50) default NULL,
`SecDOB` date default NULL,
`SecEmail` varchar(100) default NULL,
`SecTel` varchar(15) default NULL,
`SecMob` varchar(12) default NULL,
`SecAddressOne` varchar(50) default NULL,
`SecAddressTwo` varchar(75) default NULL,
`SecCity` varchar(90) default NULL,
`SecPostCode` varchar(12) default NULL,
`SecCounty` varchar(45) default NULL,
`SecCountry` varchar(20) default NULL,
PRIMARY KEY (`ClientID`),
KEY `fk_clients_users` (`ClientUserID`),
KEY `fk_clients_referredby` (`referredby_RefID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `clients`
--
INSERT INTO `clients` (`ClientID`, `Title`, `UserFirstName`, `UserLastName`, `DateOfBirth`, `UserEmail`, `UserAddress`, `UserAddress2`, `UserState`, `UserCity`, `UserZip`, `UserCountry`, `UserPhone`, `UserMob`, `ReferredBy`, `InitialEnquiry`, `Conversion`, `Status`, `ClientUserID`, `referredby_RefID`, `SecondTitle`, `SecFirstName`, `SecLastName`, `SecDOB`, `SecEmail`, `SecTel`, `SecMob`, `SecAddressOne`, `SecAddressTwo`, `SecCity`, `SecPostCode`, `SecCounty`, `SecCountry`) VALUES
(1, 'Mr', 'Homer', 'simpson', '1986-08-18', 'homer@simpson', '247', 'Evergreen Terrace', NULL, 'Springfield', 'SP1 1FS', 'Scotland', '0123456789', NULL, NULL, 'Protection', 0, 'Lead', 2, NULL, NULL, NULL, NULL, '1986-03-25', NULL, NULL, NULL, NULL, NULL, NULL, 'SP1 1FS', NULL, NULL);
-- --------------------------------------------------------
--
-- Table structure for table `generalinsurance`
--
CREATE TABLE `generalinsurance` (
`PolicyNumber` varchar(25) NOT NULL,
`GITypeName` varchar(45) default NULL,
`GIProviderName` varchar(45) default NULL,
`Premium` decimal(14,2) default NULL,
`StartDateGI` date default NULL,
`ReviewDateGI` date default NULL,
`DatePaidGI` date default NULL,
`IssueDateGI` date default NULL,
`AmountGI` decimal(14,2) default NULL,
`clients_ClientID` int(11) default NULL,
PRIMARY KEY (`PolicyNumber`),
KEY `gi_clientid` (`clients_ClientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `generalinsurance`
--
INSERT INTO `generalinsurance` (`PolicyNumber`, `GITypeName`, `GIProviderName`, `Premium`, `StartDateGI`, `ReviewDateGI`, `DatePaidGI`, `IssueDateGI`, `AmountGI`, `clients_ClientID`) VALUES
('2587975', 'Buildings', 'Aviva', 15.00, '2010-03-16', '2012-03-15', '2010-03-26', '2010-03-20', 350.00, 1),
('5456464', 'Buildings & Contents', 'Aviva', 30.00, '2010-02-19', '2012-02-18', NULL, '2010-02-25', 300.00, 1);
-- --------------------------------------------------------
--
-- Table structure for table `protection`
--
CREATE TABLE `protection` (
`ProviderName` varchar(45) default NULL,
`Premium` decimal(14,2) default NULL,
`StartDate` date default NULL,
`ReviewDate` date default NULL,
`DatePaid` date default NULL,
`Amount` decimal(14,2) default NULL,
`PolicyNumber` varchar(25) NOT NULL,
`clients_ClientID` int(11) default NULL,
`IssueDateProtection` date default NULL,
`ProtectionTypeName` varchar(45) default NULL,
`ArchivedProtection` tinyint(1) NOT NULL,
PRIMARY KEY (`PolicyNumber`),
KEY `protection_clients` (`clients_ClientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `protection`
--
INSERT INTO `protection` (`ProviderName`, `Premium`, `StartDate`, `ReviewDate`, `DatePaid`, `Amount`, `PolicyNumber`, `clients_ClientID`, `IssueDateProtection`, `ProtectionTypeName`, `ArchivedProtection`) VALUES
('Aviva', 25.00, '2010-03-10', '2012-03-10', '2010-03-20', 500.00, '659461313', 1, '2010-03-25', 'Life', 0),
('Aviva', 20.00, '2010-02-09', '2012-02-08', NULL, 200.00, 'AV546563434', 1, '2010-02-15', 'Life and Critical Illness', 0),
('Bright Grey', 15.00, '2008-03-24', '2010-03-24', '2008-03-31', 350.00, 'BG789357', 1, '2008-04-01', 'Life', 0);
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`UserID` int(11) NOT NULL auto_increment,
`FirstName` varchar(50) default NULL,
`LastName` varchar(50) default NULL,
`Email` varchar(500) default NULL,
`Username` varchar(20) default NULL,
`Password` varchar(20) default NULL,
`AccessLevel` int(11) default NULL,
PRIMARY KEY (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`UserID`, `FirstName`, `LastName`, `Email`, `Username`, `Password`, `AccessLevel`) VALUES
(1, 'Tom', 'Cruise', 'tom@example.com', 'tom', 'password', 1),
(2, 'Mark', 'Test', 'mark@example.com', 'mark', 'password', 1);
--
-- Constraints for dumped tables
--
--
-- Constraints for table `clients`
--
ALTER TABLE `clients`
ADD CONSTRAINT `clients_ibfk_1` FOREIGN KEY (`ClientUserID`) REFERENCES `users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--
-- Constraints for table `generalinsurance`
--
ALTER TABLE `generalinsurance`
ADD CONSTRAINT `gi_clientid` FOREIGN KEY (`clients_ClientID`) REFERENCES `clients` (`ClientID`) ON DELETE CASCADE ON UPDATE NO ACTION;
--
-- Constraints for table `protection`
--
ALTER TABLE `protection`
ADD CONSTRAINT `protection_clients` FOREIGN KEY (`clients_ClientID`) REFERENCES `clients` (`ClientID`) ON DELETE CASCADE ON UPDATE NO ACTION;using your query i have tested and it works however i want to display the clients firstname and lastname and the product name in which it is due.
thanks again
what i was thinking was having a total table on my page, which tallys them all up.
and then have the others in section?
Many thanks
How can i join them to show:
How much is due for that user who has logged it, and also show amount with the provider name also.
many thanks
1) You type here the expected output of query instead of giving only problem statementl.
2) Where are your product1.... product6 table structures?
3) what difference/similarities/relation between user and client table?
its fine i think i have resolved this now anyway.
thanks urtrivedi your query worked fine.
thanks for your help with it.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.