Re: I need an SQL expert

Thanks for the suggestion Jarl but it does not work. For example, using much
simplified SQL

SELECT tblFranchise.FranchiseId, tblTx.TxId
FROM tblFranchise LEFT OUTER JOIN tblTx ON tblFranchise.FranchiseId =
tblTx.FranchiseId
WHERE tblTx.TxDate=#1/29/2007#

returns rows only where there is a matching transaction (Tx); it omits the
franchises where there is no transaction for that date.

I can do what I want quite elegantly with data shaping. Something like

SHAPE {SELECT * FROM tblFranchise}
APPEND ({SELECT * FROM tblTx WHERE TxDate = #29/01/07#} AS Tx
RELATE FranchiseId TO FranchiseId)

gets me exactly what I want but in a hierarchical structure whereas I want
it in a flat structure.

I could do it with a UNION and nested SQL, something like

SELECT tblFranchise.*, tblTx.*
FROM tblFranchise LEFT OUTER JOIN tblTx ON tblFranchise.FranchiseId =
tblTx.FranchiseId
WHERE tblTx.TxDate=#1/29/2007#
UNION
SELECT tblFranchise.*, <some padding>
FROM tblFranchise
WHERE FranchiseId Not In(<SQL very similar to the first part of this query>)

but I feel this is very 'clunky.' Bear in mind I have simplified the
expressions above. The real SQL expressions are quite long and involved.

I could do (and probably will end up doing) this in stages by defining
intermediate views. I just feel that there has to be a way of doing it from
'first principles.'

Regards,

Rod


"jarl@xxxxxxxxx" wrote:

On Jan 31, 9:01 am, Rod Plastow <RodPlas...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
Now I wish to retrieve the item data concatenated with the transaction data
for any given (parameter) date. It's still standard stuff but here's the
rub. If there are no transactions for the item for that day I still want the
SQL to return the item data with a blank/null/zero transaction data stub.

Try LEFT OUTER JOIN, instead of just JOIN.


HTH,
Jarl


.