Hi!

Well, after accurate reading of your post I did figured out that even the latest improvement of the query will not help for unused codes in transactions table. Yes, you seems require UNION in such case:

select t.ddate, t.code, count(*) as tcount from Countries c LEFT JOIN Transactions t on (c.code=t.code or c.country=t.country) group by t.code
UNION ALL
select t.ddate, t.code, count(*) as tcount from Transactions t where t.code NOT IN (select c.code from Countries c) AND t.country NOT IN (select cc.country from countries cc) group by t.code

First query will get all countries, include unused countries in the transactions table. Second query will get all transaction records that have no record in the countries table.

HTH.

>Hi!
>
>Why not try something like following:
>
>... Countries c LEFT JOIN Transactions t on (c.code=t.code or c.country=t.country)
>or something like that. Join condition in VFP allows quite a free syntax - it is just a logical expression. So write a logical expression to match the records in the transactions table to records in the countries table and the query will be correct.
>
>HTH.
>
>>That is exactly what I've done to get to the current point (e.g. prepare the table that joined all relevant data into one).
>>
>>But this doesn't work from this point on as I need to find all unused codes for each country and allunused countries (with their unujsed codes) for each date as denormalization is the object here. I'm thinking of somwhow breaking them up into pieces then rejoin (maybe a UNION?)
>>
>>>>>>>>>>>>>>>>>>>
>>>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.
>>>>>>>>>>>>>>>>>>>