I'm looking to convert a load of code into SQL, but I'm wondering if I'm
mad or hoping for too much.

Basically, I have a table with various linked tables. I'll try to keep it
simple, so let's assume I have a table with ID, NAME and AMOUNT values,
and a linked table that uses just LINKED_ID, VALUE. That linked table
might perhaps have a defined number of rows - known to my code anyway.

So my output will show a grid in the following format:

NAME(1)  AMOUNT(1)  VALUE(1/1)  VALUE(1/2)  VALUE (1/3)
NAME(2)  AMOUNT(2)  VALUE(2/1)  VALUE(2/2)  VALUE (2/3)
NAME(3)  AMOUNT(3)  VALUE(3/1)  VALUE(3/2)  VALUE (3/3)

Is this even possible in SQL? Or should I go back to my old code.

Thanks for your thoughts!

/Matthew Jones/

FWIW, I've been tackling this issue for another purpose, and someone
(thanks Neil!) pointed me at
http://www.kever.com/index.htm?strUrl=%2Fcontent%2FEzSpecials.htm which is
a proxy data set which I've used successfully with a certain grid to allow
me to display the results from multiple DBISAM queries in what appears to
the grid to be a single table with fields that I populate accordingly.
This may be a way around this "limitation" of SQL.

/Matthew Jones/

You can do something like this using multiple joins on a single parent table repeating
the link the the child table & using the WHERE clause to segregate the data that returns
from the child tables.

Imagine simple DB Structure:

Customer table
Order table

1 customer can have many orders.

This SQL:

--

SELECT C.Name, SUM(O1.OrderValue) as ThisMonthsOrders, SUM(O2.OrderValue) as
LastMonthsOrders, SUM(O3.OrderValue) as TwoMonthOldOrders

FROM Customer C
LEFT JOIN Orders O1 ON O1.CustomerID=C.ID
LEFT JOIN Orders O2 ON O2.CustomerID=C.ID
LEFT JOIN Orders O3 ON O3.CustomerID=C.ID

WHERE O1.DateOfOrder >= Current_Date - 30
AND O2.DateOfOrder < Current_Date - 30
AND O2.DateOfOrder >= Current_Date - 60
AND O3.DateOfOrder < Current_Date - 60
AND O3.DateOfOrder >= Current_Date - 60

GROUP BY C.Name

ORDER BY C.Name

--

This SQL gives a result which is something like what you seemed to want in your message
... note that with different data structures & thinking about the WHERE clause carefully
you can generate a lot of different result sets of this type, but note that the GROUP BY
clause effects how the numbers SUM, as it is a cartesian join.



***************************************************************



ANOTHER WAY: Select a set of tables into temporary tables & then rejoin them

SELECT INTO "C:\Temp.dat"
C.ID, SUM(O.OrderValue) as ThisMonthsOrders

FROM Customer C
LEFT JOIN Orders O1 ON O1.CustomerID=C.ID
WHERE O1.DateOfOrder >= Current_Date - 30

; /* note this semi-colon is required to separate the SQLScript.


SELECT INTO "C:\Temp1.dat"
C.ID, SUM(O.OrderValue) as LastMonthsOrders

FROM Customer C
LEFT JOIN Orders O1 ON O1.CustomerID=C.ID
WHERE O1.DateOfOrder < Current_Date - 30
AND O1.DateOfOrder >= Current_Date - 60

;

SELECT INTO "C:\Temp2.dat"
C.ID, SUM(O.OrderValue) as TwoMonthOld

FROM Customer C
LEFT JOIN Orders O1 ON O1.CustomerID=C.ID
WHERE O1.DateOfOrder < Current_Date - 60
AND O1.DateOfOrder >= Current_Date - 90

;

/* then we select all these temporary results into a final result we show the user.
*/

SELECT C.Name, T1.ThisMonthsOrders, T2,LastMonthsOrders, T3.TwoMonthOld

FROM Customer C
LEFT JOIN "C:\Temp1.dat" T1 ON T1.ID = C.ID
LEFT JOIN "C:\Temp2.dat" T2 ON T1.ID = C.ID
LEFT JOIN "C:\Temp3.dat" T3 ON T1.ID = C.ID

WHERE C.ID IN

(

SELECT CustomerID FROM ORDERS WHERE DateOrder >= Current_Date - 90

)

/* Note that you need the final WHERE clause to ensure that you get all the customers you
need for ALL THREE temp tables ... as some customers may not have made an order in every
month.
*/

----

I hope this is Useful!

Oooh - thanks for that. I'll have to print it and study, but it would be a
big advance for me.

/Matthew Jones/