If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Ambiguous outer joins
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. -- |
|
Thread Tools | |
Display Modes | |
|
|