Results 1 to 8 of 8

Thread: Join confusion

  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Unanswered: Join confusion

    I have the following code:


    SELECT dbo_SOP10200.SOPTYPE, dbo_SOP10200.SOPNUMBE, dbo_SOP10200.ITEMNMBR, [Item Master 00101].ITEMDESC, [Item Master 00101].USCATVLS_5, [Sales Order Master 10100].DOCDATE, [Sales Order Master 10100].CUSTNMBR, [Sales Order Master 10100].CUSTNAME, [Sales Order Master 10100].CSTPONBR, dbo_SOP10200.QUANTITY, dbo_SOP10200.CMPNTSEQ, dbo_POP10110.PONUMBER, dbo_POP10110.POLNESTA, dbo_POP10110.VENDORID
    FROM ((dbo_SOP10200 INNER JOIN [Sales Order Master 10100] ON dbo_SOP10200.SOPNUMBE = [Sales Order Master 10100].SOPNUMBE) INNER JOIN [Item Master 00101] ON dbo_SOP10200.ITEMNMBR = [Item Master 00101].ITEMNMBR) LEFT JOIN dbo_POP10110 ON ([Item Master 00101].ITEMNMBR = dbo_POP10110.ITEMNMBR) OR ([Item Master 00101].USCATVLS_6 = dbo_POP10110.ITEMNMBR)
    WHERE (((dbo_SOP10200.SOPTYPE)=5) AND ((dbo_POP10110.POLNESTA)=2 Or (dbo_POP10110.POLNESTA)=3));

    Records in SOP10200 that do not have a corresponding record in POP10110 are not showing up, and I can't figure out why. I'm thinking there is something wrong with my joins?
    Any enlightenment would be appreciated....Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my eyes are bleedink... ze goggles, zey do nossink



    please, format your query

    yeah, i know, microsoft access sql always gets combobulated when you save it, but really, if you want people to read it, you have to discombobulate it for us --
    Code:
    SELECT dbo_SOP10200.SOPTYPE
         , dbo_SOP10200.SOPNUMBE
         , dbo_SOP10200.ITEMNMBR
         , [Item Master 00101].ITEMDESC
         , [Item Master 00101].USCATVLS_5
         , [Sales Order Master 10100].DOCDATE
         , [Sales Order Master 10100].CUSTNMBR
         , [Sales Order Master 10100].CUSTNAME
         , [Sales Order Master 10100].CSTPONBR
         , dbo_SOP10200.QUANTITY
         , dbo_SOP10200.CMPNTSEQ
         , dbo_POP10110.PONUMBER
         , dbo_POP10110.POLNESTA
         , dbo_POP10110.VENDORID
      FROM ((
           dbo_SOP10200 
    INNER 
      JOIN [Sales Order Master 10100] 
        ON dbo_SOP10200.SOPNUMBE = [Sales Order Master 10100].SOPNUMBE
           ) 
    INNER 
      JOIN [Item Master 00101] 
        ON dbo_SOP10200.ITEMNMBR = [Item Master 00101].ITEMNMBR
           ) 
    LEFT 
      JOIN dbo_POP10110 
        ON (
           [Item Master 00101].ITEMNMBR = dbo_POP10110.ITEMNMBR
           ) 
        OR (
           [Item Master 00101].USCATVLS_6 = dbo_POP10110.ITEMNMBR
           )
     WHERE ((
           (dbo_SOP10200.SOPTYPE)=5
           ) 
       AND (
           (dbo_POP10110.POLNESTA)=2 
        Or (dbo_POP10110.POLNESTA)=3)
           );
    now the source of your probelm becomes easier to find

    "Records in SOP10200 that do not have a corresponding record in POP10110 are not showing up"

    for any rows in SOP10200 that do not have a matching row in POP10110, all of the columns from POP10110 are set to NULL -- this is not news, this is how left outer joins work

    however, in your WHERE clause you are requiring that POLNESTA must be equal to 2 or 3, and therefore, any unmatched rows, where the columns have been set to NULL, will be thrown away, since NULL is not equal to anything

    the fix is to move this condition out of the WHERE clause and into the ON clause of the join

    try this instead --
    Code:
    SELECT ...
      FROM ((
           dbo_SOP10200 
    INNER 
      JOIN [Sales Order Master 10100] 
        ON dbo_SOP10200.SOPNUMBE = [Sales Order Master 10100].SOPNUMBE
           ) 
    INNER 
      JOIN [Item Master 00101] 
        ON dbo_SOP10200.ITEMNMBR = [Item Master 00101].ITEMNMBR
           ) 
    LEFT 
      JOIN dbo_POP10110 
        ON dbo_POP10110.ITEMNMBR IN ( [Item Master 00101].ITEMNMBR
                                    , [Item Master 00101].USCATVLS_6 )
       AND dbo_POP10110.POLNESTA IN ( 2
                                    , 3 )
     WHERE dbo_SOP10200.SOPTYPE = 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2012
    Posts
    5
    Thanks for the quick reply! I have no experience in SQL, so did not know about the formatting. I will do that for any future inquiries. I am trying to do a project for work in Access, and my limited experience in Access consists of using the design view to do very simple queries. So I'm a little over my head, here.
    I tried the code you included in your reply, but I get a message saying, "Syntax error (missing operator) in query expression "."
    I don't know if this means I am missing quote marks somewhere, since I don't see any others in the code. In looking at the code, nothing leaps out to my befuddled mind.
    Any ideas?
    Thanks!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by neuwirth View Post
    "Syntax error (missing operator) in query expression "."
    possibly stupid microsoft access needs the compound join conditions parenthisized...
    Code:
    LEFT 
      JOIN dbo_POP10110 
        ON (
           dbo_POP10110.ITEMNMBR IN ( [Item Master 00101].ITEMNMBR
                                    , [Item Master 00101].USCATVLS_6 )
       AND dbo_POP10110.POLNESTA IN ( 2
                                    , 3 )
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2012
    Posts
    5
    OK, my current code is:

    SELECT dbo_SOP10200.SOPTYPE
    , dbo_SOP10200.SOPNUMBE
    , dbo_SOP10200.ITEMNMBR
    , [Item Master 00101].ITEMDESC
    , [Item Master 00101].USCATVLS_5
    , [Sales Order Master 10100].DOCDATE
    , [Sales Order Master 10100].CUSTNMBR
    , [Sales Order Master 10100].CUSTNAME
    , [Sales Order Master 10100].CSTPONBR
    , dbo_SOP10200.QUANTITY
    , dbo_SOP10200.CMPNTSEQ
    , dbo_POP10110.PONUMBER
    , dbo_POP10110.POLNESTA
    , dbo_POP10110.VENDORID

    FROM ((
    dbo_SOP10200
    INNER
    JOIN [Sales Order Master 10100]
    ON dbo_SOP10200.SOPNUMBE = [Sales Order Master 10100].SOPNUMBE
    )
    INNER
    JOIN [Item Master 00101]
    ON dbo_SOP10200.ITEMNMBR = [Item Master 00101].ITEMNMBR
    )

    LEFT
    JOIN dbo_POP10110
    ON (
    dbo_POP10110.ITEMNMBR IN ( [Item Master 00101].ITEMNMBR
    , [Item Master 00101].USCATVLS_6 )
    AND dbo_POP10110.POLNESTA IN ( 2
    , 3 )
    )
    WHERE dbo_SOP10200.SOPTYPE = 5


    Access allows me to save it this way, which I think means that it is not seeing any syntax or other errors in the code. However, when I run the query, I get no results. Usually if there are no records that match, Access shows a datasheet view with one empty row - I don't even get that. It's like the query goes into space and never comes back.

  6. #6
    Join Date
    Sep 2012
    Posts
    5
    Another observation - when I run a query from this database, the system asks me for authentication for the ODBC link. When I run the code above, I don't get asked for that, which leads me to believe that the query is never hitting the data at all. Any idea what could cause it to just disappear like that?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, sorry, i have no idea...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2012
    Posts
    5
    Thanks, r937, I appreciate the time you spent on me.

Posting Permissions

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