Hi,

got some help from tesu on another thread but i am struggling to get this working.

my individual query returns 2 results which is correct from the database.

however when i try it with a view joining the multiple table with same query it returns 2 results but only 1 from the table that returns 2 results.

my query:

CREATE OR REPLACE VIEW renewals( Product, ClientID, ClientFirstName, ClientLastName, ReviewDate) AS  SELECT 'generalinsurance', c.ClientID, c.App1FirstName, c.App1LastName, ReviewDateGI  
FROM clients c, generalinsurance p      WHERE  c.ClientID = p.Clients_ClientID     AND ReviewDateGI >= CURRENT_DATE() AND ReviewDateGI <= DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY) OR  ReviewDateGI <= CURRENT_DATE()      
GROUP BY c.ClientID, c.App1FirstName, c.App1LastName UNION  SELECT 'protection', c.ClientID, c.App1FirstName, c.App1LastName, ReviewDate    
FROM clients c, protection p      WHERE c.ClientID = p.Clients_ClientID     AND ReviewDate >= CURRENT_DATE() AND ReviewDate <= DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY) OR  ReviewDate <= CURRENT_DATE()     
GROUP BY c.ClientID, c.App1FirstName, c.App1LastName UNION  SELECT 'buytolet', c.ClientID, c.App1FirstName, c.App1LastName, ReviewDate   
FROM clients c, buytolet p      WHERE  c.ClientID = p.Clients_ClientID     AND ReviewDate >= CURRENT_DATE() AND ReviewDate <= DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY) OR  ReviewDate <= CURRENT_DATE()     
GROUP BY c.ClientID, c.App1FirstName, c.App1LastName UNION  SELECT 'mortgage', c.ClientID, c.App1FirstName, c.App1LastName, ReviewDate    
FROM clients c, mortgage p      WHERE c.ClientID = p.Clients_ClientID     AND ReviewDate >= CURRENT_DATE() AND ReviewDate <= DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY) OR  ReviewDate <= CURRENT_DATE()  
GROUP BY c.ClientID, c.App1FirstName, c.App1LastName UNION  SELECT 'pensions_investments', c.ClientID, c.App1FirstName, c.App1LastName, ReviewDate   
FROM clients c, pensions_investments p      WHERE c.ClientID = p.Clients_ClientID     AND ReviewDate >= CURRENT_DATE() AND ReviewDate <= DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY) OR  ReviewDate <= CURRENT_DATE()    
GROUP BY c.ClientID, c.App1FirstName, c.App1LastName;

individual query:

SELECT clients.*, protection.*
FROM (clients JOIN protection ON protection.clients_ClientID=clients.ClientID)
WHERE ReviewDate >= CURRENT_DATE() AND ReviewDate <= DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY) OR  ReviewDate <= CURRENT_DATE()

can anyone help me with why it is returning different results?

many thanks

Looks like the 'group by' is merging your results.

Hi thanks for that, how do i get round that then?

do i just remove the group by?

thank you

Tried removing the group by and it just produces even more records than excpected and looks like duplicates.

any help please?

thanks

You removed only the group by for the protection table, and it got more result than the query by itself ?

Hi pritaesas,

managed to solve it, i changed my query to this:

FROM clients c JOIN buytolet p  on  c.ClientID = p.Clients_ClientID     WHERE ReviewDate >= CURRENT_DATE() AND ReviewDate <= DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY) OR  ReviewDate <= CURRENT_DATE()

and that solved it.

thanks anyway