Hi!
to join Transactions with Countries with nulls, just use LEFT JOIN or RIGHT JOIN. Something like following:
select t.ddate, c.code, count(*) as tcount from Countries c LEFT JOIN Transactions t on c.code=t.code group by c.code
Just change the order of tables - select all countries, than join other records to is with LEFT JOIN.
HTH.
>I have a blind spot today. Can't seem to see the forest for the trees and can't come up with the proper SELECT-SQL. I want to create a denormalized temp table to be the basis of a report.
>
>Transactions table has a summary of transactions per day,per countery, per type:
>FileDate D
>Country C(25)
>TCode C(3)
>TranCount N(6,0)
>
>I also have a table of all available countries where this product is sold (6 countries only) and a table of possible TCodes (7 records like "DES", "LDS", "PUR", "ANU", "XLD"...)
>
>Not all countries have sales in all Transaction codes (TCode) in all dates. I want to get a join of Transactions with Countries and Tcodes to get NULL entries for all unused Tcodes and unused Countries in the Transactions list for each date.
>
>e.g.:
>06/01/01 COLOMBIA DES 4
>06/01/01 COLOMBIA LDS 6
>06/01/01 PERU DES 6
>06/02/01 BRAZIL PUR 6
>06/02/01 PERU PUR 3
>
>
>I want to fill in the missing records to complete all missing codes in each country and all missing countries in each day.
>
>Is there a query or trwo to do this? Or am I better off doin procedural code and SCAN..ENDSCAN to INSERT missing records?
>
>I need the report table denormalized, as opposed to doing a loop when creating the report, and adding missing data, as I will not do the reporting myself but export data to an XLS file for somebody else to deal with.
Vlad Grynchyshyn, Project Manager, MCP
vgryn@yahoo.comICQ #10709245
The professional level of programmer could be determined by level of stupidity of his/her bugs
It is not appropriate to say that question is "foolish". There could be only foolish answers. Everybody passed period of time when knows nothing about something.