query multiple tables for amount due

[andydeans]

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

[urtrivedi]

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
) a

[andydeans]

Thanks 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', '[e-mail]', 'tom', 'password', 1), 
(2, 'Mark', 'Test', '[e-mail]', '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;

[andydeans]

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

[andydeans]

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

[andydeans]

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

[urtrivedi]

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?

[andydeans]

its fine i think i have resolved this now anyway.

[andydeans]

thanks urtrivedi your query worked fine.

thanks for your help with it.