Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2009
    Posts
    67

    Question Unanswered: Query help (outer join where it matches criteria)

    Attached is my query relationship image.

    http://picasaweb.google.com/lh/photo...eat=directlink

    I'm basically trying to attach the red square to the blue square if the CAS
    Number in the Blue matches a CAS Number of the Red.

    Here is what I have for a query thus far:
    Code:
    SELECT 
          [_temp_BOM_import].BOMDataID
        , [_temp_BOM_import].AuthorityID
        , [_temp_BOM_import].Timestamp
        , [_temp_BOM_import].Filename
        , [_temp_BOM_import].AgilePartNumber
        , [_temp_BOM_import].Qty
        , [_temp_BOM_import].UnitOfMeasure
        , MPN.VendorID
        , [_temp_BOM_import].ManufacturerName
        , MPN.MPNid
        , [_temp_BOM_import].ManufacturerPartNumber
        , [_temp_BOM_import].RefDes
        , MDF.MDFid
        , MDF.GramWeight
        , (
            SELECT StandardResults.Pass                     
            FROM StandardResults                            
            WHERE                                           
                StandardResults.StandardID = 1
                AND                                         
                StandardResults.MDFid = Series.MDFid        
           ) AS PPMPass
        , MDFName.MDFName AS CFName
        , MDFName_1.MDFName AS HMName
        , CASNumber.MaterialName
        , CASNumber.CASNumber
        , MDFData.SubstanceMass
        , MDFData.SubstancePPM
        , MDFData.Exemption
        , StandardMaterialList.StandardID
        , StandardCompoundMaterial.StandardCompoundMaterialName
        , StandardLimit.StandardLimitAmount
        , StandardMeasurementType.StandardMeasurementTypeName
    FROM ((((((((_temp_BOM_import 
    LEFT JOIN MPN 
    ON [_temp_BOM_import].ManufacturerPartNumber = MPN.MPN) 
    LEFT JOIN Series 
    ON MPN.SeriesID = Series.SeriesID) 
    LEFT JOIN MDF 
    ON Series.MDFid = MDF.MDFid) 
    LEFT JOIN MDFData 
    ON MDF.MDFid = MDFData.MDFid) 
    LEFT JOIN MDFName 
    ON MDFData.CompFragmentNameID = MDFName.MDFNameID) 
    LEFT JOIN MDFName AS MDFName_1 
    ON MDFData.HomogeneousNameID = MDFName_1.MDFNameID) 
    LEFT JOIN CASNumber 
    ON MDFData.CASNumberID = CASNumber.CASNumberID) 
    LEFT JOIN (((Standard 
    RIGHT JOIN StandardMaterialList 
    ON Standard.StandardID = StandardMaterialList.StandardID) 
    LEFT JOIN StandardLimit 
    ON StandardMaterialList.StandardCompoundMaterialID = StandardLimit.StandardCompoundMaterialID) 
    LEFT JOIN StandardCompoundMaterial 
    ON StandardLimit.StandardCompoundMaterialID = StandardCompoundMaterial.StandardCompoundMaterialID) 
    ON CASNumber.CASNumberID = StandardMaterialList.CASNumberID) 
    LEFT JOIN StandardMeasurementType 
    ON StandardLimit.StandardMeasurementTypeID = StandardMeasurementType.StandardMeasurementTypeID
    
    ORDER BY 
          [_temp_BOM_import].AgilePartNumber
        , [_temp_BOM_import].ManufacturerName
        , [_temp_BOM_import].ManufacturerPartNumber
        , MDFName.MDFName
        , MDFName_1.MDFName
        , CASNumber.MaterialName
    If I add a WHERE clause to it "WHERE Standard.StandardID = 1" that matches the subquery, then I *ONLY* get the material from the BLUE that is also in the RED, when I really want all of the BLUE and whatever matches in the RED.

    I know I'm probably missing something easy.
    Attached Thumbnails Attached Thumbnails dfe-mdf-std.gif  
    Last edited by LAYGO; 05-12-10 at 15:12. Reason: Fix img link

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you want to maybe left join to a "derived table". google it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2009
    Posts
    67
    Thank you. I will do just that . . .

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    or you maybe take this out of the WHERE CLAUSE and put it in the ON clause, WHERE Standard.StandardID = 1 to ON Standard.StandardID = 1 AND .......
    Dave

  5. #5
    Join Date
    Oct 2009
    Posts
    67
    Quote Originally Posted by dav1mo View Post
    or you maybe take this out of the WHERE CLAUSE and put it in the ON clause, WHERE Standard.StandardID = 1 to ON Standard.StandardID = 1 AND .......
    Dave
    I tried it as "ON Id = ID AND WHERE", it gave me a syntax error, but I never though to move it before the On Clause.

    Let me try that.

    I read up on derived tables, I haven't tried it yet, but I'm not sure where it solves my problem . . . yet.

    EDIT:
    "Join Expression Not Supported" when I do:
    ON Standard.StandardID = 1 AND Standard.StandardID = StandardMaterialList.StandardID

  6. #6
    Join Date
    Oct 2009
    Posts
    67
    (BlueTableData)
    Left Join On
    (RedTableData)


    That how I want to do it via derived?a

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by LAYGO View Post
    "Join Expression Not Supported"
    that's a microsoft access error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2009
    Posts
    67
    Quote Originally Posted by r937 View Post
    that's a microsoft access error message
    DOH! I just realized I'm in the wrong forum. Yes, it is Access.

  9. #9
    Join Date
    Oct 2009
    Posts
    67
    I GOT IT! Thanks for the derived table tip Thrasy!

    Using (BlueTable) Left Join (RedTable) ON basically worked. I didn't think it would, but I tried that very simply and it didn't work until I aliased it:

    SELECT * FROM
    (BlueTable)
    LEFT JOIN
    (RedTable)
    ON BlueTable.CASNumberID = RedTable.CASNumberID

    But, it was odd how it all sort of happened by accident when I finally aliased, but was joining on the real table names & tried to view it in Design view. Then suddenly I had 2 tables vs the 15. Once I left joined the two derived tables, it worked.

    Gigantic query:
    Code:
    SELECT 
          MDFStuff.BOMDataID
        , MDFStuff.AuthorityID
        , MDFStuff.Timestamp
        , MDFStuff.Filename
        , MDFStuff.AgilePartNumber
        , MDFStuff.Qty
        , MDFStuff.UnitOfMeasure
        , MDFStuff.VendorID
        , MDFStuff.ManufacturerName
        , MDFStuff.MPNid
        , MDFStuff.ManufacturerPartNumber
        , MDFStuff.RefDes
        , MDFStuff.MDFid
        , MDFStuff.GramWeight
        , MDFStuff.PPMPass
        , MDFStuff.CFName
        , MDFStuff.HMName
        , MDFStuff.CASNumberID
        , MDFStuff.MaterialName
        , MDFStuff.CASNumber
        , MDFStuff.SubstanceMass
        , MDFStuff.SubstancePPM
        , MDFStuff.Exemption
        , STDStuff.StandardID
        , STDStuff.StandardName
        , STDStuff.StandardCompoundMaterialName
        , STDStuff.CASNumberID
        , STDStuff.CASNumber
        , STDStuff.MaterialName
        , STDStuff.StandardLimitAmount
        , STDStuff.StandardMeasurementTypeName
        , *
    FROM [SELECT
              [_temp_BOM_import].BOMDataID
            , [_temp_BOM_import].AuthorityID
            , [_temp_BOM_import].Timestamp
            , [_temp_BOM_import].Filename
            , [_temp_BOM_import].AgilePartNumber
            , [_temp_BOM_import].Qty
            , [_temp_BOM_import].UnitOfMeasure
            , MPN.VendorID
            , [_temp_BOM_import].ManufacturerName
            , MPN.MPNid
            , [_temp_BOM_import].ManufacturerPartNumber
            , [_temp_BOM_import].RefDes
            , MDF.MDFid
            , MDF.GramWeight
            , (
                SELECT StandardResults.Pass
                FROM StandardResults
                WHERE
                    StandardResults.StandardID = 1
                    AND
                    StandardResults.MDFid = Series.MDFid
               ) AS PPMPass
            , MDFName.MDFName AS CFName
            , MDFName_1.MDFName AS HMName
            , MDFData.CASNumberID
            , CASNumber.MaterialName
            , CASNumber.CASNumber
            , MDFData.SubstanceMass
            , MDFData.SubstancePPM
            , MDFData.Exemption
        FROM (((((((_temp_BOM_import
        LEFT JOIN MPN
        ON [_temp_BOM_import].ManufacturerPartNumber = MPN.MPN)
        LEFT JOIN Series
        ON MPN.SeriesID = Series.SeriesID)
        LEFT JOIN MDF
        ON Series.MDFid = MDF.MDFid)
        LEFT JOIN MDFData
        ON MDF.MDFid = MDFData.MDFid)
        LEFT JOIN MDFName
        ON MDFData.CompFragmentNameID = MDFName.MDFNameID)
        LEFT JOIN MDFName AS MDFName_1
        ON MDFData.HomogeneousNameID = MDFName_1.MDFNameID)
        LEFT JOIN CASNumber
        ON MDFData.CASNumberID = CASNumber.CASNumberID)
        ORDER BY
              [_temp_BOM_import].AgilePartNumber
            , [_temp_BOM_import].ManufacturerName
            , [_temp_BOM_import].ManufacturerPartNumber
            , MDFName.MDFName
            , MDFName_1.MDFName
            , CASNumber.MaterialName
    ]. AS MDFStuff
    LEFT JOIN [SELECT 
              Standard.StandardID
            , Standard.StandardName
            , StandardCompoundMaterial.StandardCompoundMaterialName
            , CASNumber.CASNumberID
            , CASNumber.CASNumber
            , CASNumber.MaterialName
            , StandardLimit.StandardLimitAmount
            , StandardMeasurementType.StandardMeasurementTypeName
        FROM ((((Standard 
        RIGHT JOIN StandardLimit 
        ON Standard.StandardID = StandardLimit.StandardID) 
        LEFT JOIN StandardMeasurementType 
        ON StandardLimit.StandardMeasurementTypeID 
         = StandardMeasurementType.StandardMeasurementTypeID) 
        LEFT JOIN StandardMaterialList 
        ON StandardLimit.StandardCompoundMaterialID
         = StandardMaterialList.StandardCompoundMaterialID) 
        LEFT JOIN StandardCompoundMaterial 
        ON StandardMaterialList.StandardCompoundMaterialID
         = StandardCompoundMaterial.StandardCompoundMaterialID) 
        LEFT JOIN CASNumber 
        ON StandardMaterialList.CASNumberID = CASNumber.CASNumberID
    WHERE 
        Standard.StandardID = 1
    ]. AS STDStuff 
    ON MDFStuff.CASNumberID = STDStuff.CASNumberID

  10. #10
    Join Date
    Oct 2009
    Posts
    67
    Nested Sql - "Syntax Error in From Clause" - Microsoft Access / VBA answers

    Ok, I was running into that problem where Access was rewriting the query to make the derived table as "FROM [(subquery)].". Once I changed the "[" & "]." to "(" & ")", it works fine!

    Admins, feel free to move thread to correct forum, sorry!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •