Ambiguous outer joins

Thanks John. I will try that and let you know.

"John Spencer" wrote:

TRY the following

SELECT OutJ.STK_NO
, OutJ.LOC_CODE
, SIR.REQUESTOR
, SIR.EXTERNAL_SIR_ID
FROM SIR INNER JOIN (
SELECT STK_LOC.STK_NO
, STK_LOC.LOC_CODE
, SIR_D.EXTERNAL_SIR_ID
, SIR_D.ISSUE_PNT
FROM STK_LOC LEFT JOIN SIR_D
ON STK_LOC.STK_NO = SIR_D.STK_NO
AND STK_LOC.LOC_CODE = SIR_D.LOC_CODE) as OutJ
ON SIR.EXTERNAL_SIR_ID = OutJ.EXTERNAL_SIR_ID;


Although, with only one left join in that whole thing, I think there should
be a way to join the three table together without a conflict on the joins.
If I get time I will go back and study this for a simpler solution.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"gmore" wrote in message
...
Sorry. I had misunderstood. Here they are :

Outer Join query named OutJ:
----------------------------------
SELECT STK_LOC.STK_NO
, STK_LOC.LOC_CODE
, SIR_D.EXTERNAL_SIR_ID
, SIR_D.ISSUE_PNT
FROM STK_LOC LEFT JOIN SIR_D
ON (STK_LOC.STK_NO = SIR_D.STK_NO) AND (STK_LOC.LOC_CODE =
SIR_D.LOC_CODE);

Query SinglePart
--------------------
SELECT OutJ.STK_NO
, OutJ.LOC_CODE, SIR.REQUESTOR
, SIR.EXTERNAL_SIR_ID
FROM SIR INNER JOIN OutJ
ON SIR.EXTERNAL_SIR_ID = OutJ.EXTERNAL_SIR_ID;





"John Spencer" wrote:

That is not what I asked for. I asked for the two queries that you
would have constructed already. In response to Douglas Steele's
suggestion.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


gmore wrote:
Here's my queries:

The one I want everything at the same place:

SELECT SIR_D.STK_NO, SIR_D.LOC_CODE
, Max(SIR.ENTRY_DATE) AS LAST_DATE
, Last(TSW_PERSONNEL!LAST_NAME & ", " & TSW_PERSONNEL!FIRST_NAME) AS
REQUESTOR
, Last(SIR.EXTERNAL_SIR_ID) AS LastOfEXTERNAL_SIR_ID

FROM TSW_PERSONNEL INNER JOIN (SIR INNER JOIN SIR_D ON
SIR.EXTERNAL_SIR_ID =
SIR_D.EXTERNAL_SIR_ID) ON TSW_PERSONNEL.EMP_NO = SIR.REQUESTOR
WHERE (((SIR_D.ISSUED_QTY)0))
GROUP BY SIR_D.STK_NO, SIR_D.LOC_CODE));

The one I want every record from :
SELECT STK_LOC.STK_NO
, STK_LOC.LOC_CODE
, STK_LOC.ON_REQST_ONLY_FLAG
, STK_LOC.REORD_POINT
, STK_LOC.REORD_QTY

FROM STK_LOC;

I hope this is not too mixing up...

Thanks,
gmore


"John Spencer" wrote:

It is possible as long as your field and table names consist of only
letters and numbers and underscores. If you will post the two
queries,
I will take a look and post a solution.

EXAMPLE SQL follows.
Outer Join query save as qOne

SELECT A.FieldA, B.FieldB
FROM A LEFT JOIN B
ON A.PK = B.FK

Nested query
SELECT C.*, qOne.*
FROM C INNER JOIN qOne
ON C.SomeField = qOne.FieldA

Single query.
SELECT C.*, qOne.*
FROM C INNER JOIN
[
SELECT A.FieldA, B.FieldB
FROM A LEFT JOIN B
ON A.PK = B.FK
]. as qOne
ON C.SomeField = qOne.FieldA

Basically
using a copy of the nested query,
-- type " () as " before qOne in the join
-- copy the SQL of qOne
-- paste it between the parentheses "( )"
-- remove any square brackets from the pasted in SQL that have been
"helpfully" placed there by Access when the query was saved.

When this gets saved, Access will change the parenthese to sqaure
brackets with a period immediately following the last bracket.

In Access, this type of embedded query cannot have any other square
brackets within the outer brackets. This means no parameters or
references to form controls can be embedded in the embedded sub-query
and that no field or table that requires square brackets can be used.


'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


gmore wrote:
Thanks. I believe this will work fine, but do you have a way to
obtain the
same result having only one SQL query without creating another query?

"Douglas J. Steele" wrote:

One approach is to create (and save) a query that does the outer
join
between SIR_D and STK_LOC, and then use that query for the inner
join with
SIR.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"gmore" wrote in message
...
Hello folks!

I've got a problem. I guess it's why we always come in here when
that's
the
case

I have three tables as follows:

SIR (PK : id)
SIR_D (PK : id, stk_no, loc_code)
STK_LOC (PK : stk_no, loc_code)

There is an inner join between SIR and SIR_D that works fine, but I
also
want to outer join tables SIR_D and STK_LOC to get every single
field from
the STK_LOC table. When I do so, I get an ambiguous outer joins
message
and
won't allow me to go further. Do you folks have any ideas?

Thanks for your help!
gmore

On Feb 20, 6:16 am, gmore wrote:
SELECT OutJ.STK_NO
, OutJ.LOC_CODE
, SIR.REQUESTOR
, SIR.EXTERNAL_SIR_ID
FROM SIR INNER JOIN (
SELECT STK_LOC.STK_NO
, STK_LOC.LOC_CODE
, SIR_D.EXTERNAL_SIR_ID
, SIR_D.ISSUE_PNT
FROM STK_LOC LEFT JOIN SIR_D
ON STK_LOC.STK_NO = SIR_D.STK_NO
AND STK_LOC.LOC_CODE = SIR_D.LOC_CODE) as OutJ
ON SIR.EXTERNAL_SIR_ID = OutJ.EXTERNAL_SIR_ID;

Although, with only one left join in that whole thing, I think there should
be a way to join the three table together without a conflict on the joins.
If I get time I will go back and study this for a simpler solution.

Isn't that the same as:

SELECT STK_LOC.STK_NO
, STK_LOC.LOC_CODE
, SIR.REQUESTOR
, SIR.EXTERNAL_SIR_ID
FROM STK_LOC, SIR_D, SIR
WHERE STK_LOC.STK_NO = SIR_D.STK_NO
AND STK_LOC.LOC_CODE = SIR_D.LOC_CODE
AND SIR.EXTERNAL_SIR_ID = SIR_D.EXTERNAL_SIR_ID

i.e. the outer join acts as an inner join when the join condition uses
a column from the unpreserved table.

I believe it wouldn't give me every stock part I need from STK_LOC
table if I use what you typed... What do you think about it?

Here are my thoughts.

Consider the derived table OutJ

(SELECT STK_LOC.STK_NO
, STK_LOC.LOC_CODE
, SIR_D.EXTERNAL_SIR_ID
, SIR_D.ISSUE_PNT
FROM STK_LOC LEFT JOIN SIR_D
ON STK_LOC.STK_NO = SIR_D.STK_NO
AND STK_LOC.LOC_CODE = SIR_D.LOC_CODE) as OutJ

Any rows that are in STK_LOC.STK_NO but do not exist in SIR_D will
have the null value for column SIR_D.EXTERNAL_SIR_ID. Now consider the
INNER JOIN:

SELECT OutJ.STK_NO
, OutJ.LOC_CODE
, SIR.REQUESTOR
, SIR.EXTERNAL_SIR_ID
FROM SIR INNER JOIN (...) as OutJ
ON SIR.EXTERNAL_SIR_ID = OutJ.EXTERNAL_SIR_ID;

Remember OutJ.EXTERNAL_SIR_ID was SIR_D.EXTERNAL_SIR_ID and can
contain the null value. The inner join condition SIR.EXTERNAL_SIR_ID =
NULL will remove rows from the resultset, therefore you have gained
nothing by using an outer join in the derived table, where you could
use an inner join and get the same results.

Jamie.

--