Urgent join query help

[andydeans]

Hi guys,

Need some help here. I have created an individual query for each product which works absolutely fine.

However i want to be able to join it so i can display on my page who is due for a review.

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

This works great but i do not want to show on my page 8 queries that are the same is this except the protection is maybe mortgage.

could anyone help me make this into one query for all product tables?

They all have same reviewdate column.

No idea how to join multiple queries.

thanks again

[tesuji]

Hi andydeans

Still about solving your multi-select problem? I am sure that it can be solved in ways that I already ilustrated in which you should create a view consisting of 6 or 7 selects on your various products and aggregated them by UNIONs.

Once the view exists then totals depending on due dates and other grouping criteria can easily be generated by means of simple selects. You should really take into consideration my example given in: http://www.daniweb.com/forums/thread288573-2.html, it functions in this way. (I am doing such design tasks day in, day out on a truly sophisticated database having quite a few thousands tables.)

-- tesu

[andydeans]

Hi tesu,

you have been a life saver with this, first time ever using a view and union and i have managed to modify your query for to make the payments view. so now i have the pipeline view and payments view thanks to you.

not sure how to do this one though as it has a where by current date in it?

thanks again

[andydeans]

hi tesu,

i managed to create it using your example after most of the day trialing and error, but i did it.

thanks for all your help again