I need an SQL expert

[Guest]

Hi

I've struggled with this but my SQL is not up to it. So any help would be
appreciated. Yes I can do it in a number of steps but that's not very
elegant; I have a feeling it could be done in one complex(?) SQL.

There are two related tables. The first is an Item table and the second is a
Transaction table that contains the Item PK as a foreign key as well as a
transaction date. For any item there are zero to many transactions. It's all
standard stuff so far.

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.

If you're wondering about the business logic then let me say that there can
be only one transaction per day for each item, the system takes care of this.
Thus what I want is a list of all items together with the transaction data
for the specified day or no data if there is no transaction for that day.

Can this be done in one step?

Rod

 

[jarl]

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

 

[Guest]

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

 

[Gary Walter]

I've struggled with this but my SQL is not up to it. So any help would be
appreciated. Yes I can do it in a number of steps but that's not very
elegant; I have a feeling it could be done in one complex(?) SQL.

There are two related tables. The first is an Item table and the second is
a
Transaction table that contains the Item PK as a foreign key as well as a
transaction date. For any item there are zero to many transactions. It's
all
standard stuff so far.

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.

If you're wondering about the business logic then let me say that there
can
be only one transaction per day for each item, the system takes care of
this.
Thus what I want is a list of all items together with the transaction data
for the specified day or no data if there is no transaction for that day.

Can this be done in one step?

Water from the moon...

Your filter on the inner table will destroy the outer join...

Filter transaction table in prequery,
then outer join item table to this prequery.

Then sleep well at night...

Only alternative is to outer join item table
to subquery that filters transactions, but
*brackets* within subquery of FROM clause
may (usually do) cause Access to choke.

You can write

FROM
tblFranchise As F
LEFT JOIN
(SELECT
t.*
FROM
tblTx As t
WHERE
t.TxDate=#1/29/2007#) As Q
ON
F.FranchiseId = Q.FranchiseId

but, when save query, Access will
enclose subquery in brackets and an
ending period.

FROM
tblFranchise As F
LEFT JOIN
[SELECT
t.*
FROM
tblTx As t
WHERE
t.TxDate=#1/29/2007#]. As Q
ON
F.FranchiseId = Q.FranchiseId

that might work, but any bracketing
within the subquery will cause query
parser to choke (in my experience).

 

[giorgio rancati]

Hi,
try tis query
----
SELECT
tblFranchise.FranchiseId, tblTx.TxId
FROM
tblFranchise
LEFT OUTER JOIN
tblTx
ON tblFranchise.FranchiseId = tblTx.FranchiseId
AND (tblTx.TxDate)=#1/29/2007#

 

[Guest]

Gary, A postscript.

Just tried the following with an ADODB recordset .Open method

SELECT F.*, Q.* FROM tblFranchise AS F LEFT JOIN (SELECT * FROM tblTx WHERE
TxDate = #1/29/07#) AS Q ON F.FranchiseId = Q.FranchiseID

and as far as I can tell from the VBA Locals and Immediate windows it worked
beautifully. Note I have used parentheses instead of brackets. This
promises to be the elegant one-step solution. There is however another WHERE
clause to tack on the end that filters the franchise records.

There is water on the moon and sleep beckons tonight.

Thanks,

Rod

I've struggled with this but my SQL is not up to it. So any help would be
appreciated. Yes I can do it in a number of steps but that's not very
elegant; I have a feeling it could be done in one complex(?) SQL.

There are two related tables. The first is an Item table and the second is
a
Transaction table that contains the Item PK as a foreign key as well as a
transaction date. For any item there are zero to many transactions. It's
all
standard stuff so far.

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.

If you're wondering about the business logic then let me say that there
can
be only one transaction per day for each item, the system takes care of
this.
Thus what I want is a list of all items together with the transaction data
for the specified day or no data if there is no transaction for that day.

Can this be done in one step?

Water from the moon...

Your filter on the inner table will destroy the outer join...

Filter transaction table in prequery,
then outer join item table to this prequery.

Then sleep well at night...

Only alternative is to outer join item table
to subquery that filters transactions, but
*brackets* within subquery of FROM clause
may (usually do) cause Access to choke.

You can write

FROM
tblFranchise As F
LEFT JOIN
(SELECT
t.*
FROM
tblTx As t
WHERE
t.TxDate=#1/29/2007#) As Q
ON
F.FranchiseId = Q.FranchiseId

but, when save query, Access will
enclose subquery in brackets and an
ending period.

FROM
tblFranchise As F
LEFT JOIN
[SELECT
t.*
FROM
tblTx As t
WHERE
t.TxDate=#1/29/2007#]. As Q
ON
F.FranchiseId = Q.FranchiseId

that might work, but any bracketing
within the subquery will cause query
parser to choke (in my experience).