Greetings folks,
This is a follow up to my initial message some time ago. Now I have
got all the details together.
I have two tables.
Table A - Which contains one row per entry
Table B - Which contains multiple rows per entry
Table B relates to Table A by a field say called ID. Table A in this
case containers all the order header information while Table
B contains the order lines.
What I want to do is do a query joining table A with B and sorting
firstly on a field in Table A then on several fields in Table B.
The issue is in doing the table A sort is simple enough but as soon as
you do as sort on table B fields all the data gets mixed up.
So while below gives me the required data is does not work on the
sorting front..
SELECT a.gdn_gdn, a.gdn_custref, a.gdn_date, a.gdn_address_name,
a.gdn_method, b.gdn_stockref, b.gdn_row, b.gdn_bay, b.gdn_shelf
FROM a
LEFT JOIN b
ON a.gdn_gdn = b.gdn_gdn
ORDER BY a.gdn_method,b.gdn_row, b.gdn_bay, b.gdn_shelf
Subsequent tries at using union querys, etc all failed with the same
problem when it comes to the sorting. What I want to do is to do a
sort
group the sorted data together and then sort each group again to keep
it all in order.
Simple solution would be to create a dynamic select using a crosstab
function, pivot table or something like this.
However after reading up on this it appears this is impossible as you
cannot have unknowns in a SELECT query.
I then read this post "Using cursors for generating cross tables" from
"Pavel Stehule's blog" which would seem to get the data in a form I
could use. However while I can get the examples to work trying to
apply it to my needs fails. Presumably because my data is more
complicated.
So has anyone any ideas how to achieve this. It seems so simple from a
human sorting perspective I
cannot believe it can not be done by a database, but after playing
around with it for a very long time
I am just getting nowhere.
Regards
Richard