my query is returning the worng results

[andydeans]

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

[pritaeas]

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

[andydeans]

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

do i just remove the group by?

thank you

[andydeans]

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

any help please?

thanks

[pritaeas]

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

[andydeans]

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