Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2015
    Posts
    3

    Unhappy Unanswered: Trying to include results without part number

    Hi everyone.

    I hope someone can help me here. I have the Sql statement below. It works ok but it will not return result where there is no part number. I am told I need an outer join to do that but I do not know how to go about it. I am hoping someone here can show me how this might be done.

    Thank you
    Triump

    Code:
    SELECT   DISTINCT SHOP_VIEW.RESOURCE_ID,
                      WORK_ORDER.USER_5,
                      WORK_ORDER.BASE_ID,
                      WORK_ORDER.PART_ID,
                      WORK_ORDER.DRAWING_ID,
                      PART.DESCRIPTION,
                      OPERATION.COMPLETED_QTY,
                      OPERATION.RUN_HRS,
                      OPERATION.ACT_RUN_HRS,
                      '*%2' + WORK_ORDER.BASE_ID + '$' + CONVERT (NVARCHAR (MAX), WORK_ORDER.LOT_ID) + '$' + CONVERT (NVARCHAR (MAX), WORK_ORDER.SUB_ID) + '$' + CONVERT (NVARCHAR (MAX), WORK_ORDER.SPLIT_ID) + '$' + CONVERT (NVARCHAR (MAX), OPERATION.SEQUENCE_NO) + '%*' AS 'BarCode',
                      WORK_ORDER.DESIRED_QTY
    FROM     BFE.dbo.OPERATION AS OPERATION, BFE.dbo.PART AS PART, BFE.dbo.SHOP_VIEW AS SHOP_VIEW, BFE.dbo.WORK_ORDER AS WORK_ORDER
    WHERE    WORK_ORDER.PART_ID = PART.ID
             AND OPERATION.RESOURCE_ID = SHOP_VIEW.RESOURCE_ID
             AND OPERATION.WORKORDER_BASE_ID = WORK_ORDER.BASE_ID
             AND ((WORK_ORDER.DESIRED_WANT_DATE IS NOT NULL)
                  AND (SHOP_VIEW.VIEW_ID = 'MSHOP'))
    GROUP BY SHOP_VIEW.RESOURCE_ID, WORK_ORDER.USER_5, WORK_ORDER.BASE_ID, WORK_ORDER.PART_ID, WORK_ORDER.DRAWING_ID, PART.DESCRIPTION, OPERATION.COMPLETED_QTY, OPERATION.RUN_HRS, OPERATION.ACT_RUN_HRS, '*%2' + WORK_ORDER.BASE_ID + '$' + CONVERT (NVARCHAR (MAX), WORK_ORDER.LOT_ID) + '$' + CONVERT (NVARCHAR (MAX), WORK_ORDER.SUB_ID) + '$' + CONVERT (NVARCHAR (MAX), WORK_ORDER.SPLIT_ID) + '$' + CONVERT (NVARCHAR (MAX), OPERATION.SEQUENCE_NO) + '%*', WORK_ORDER.DESIRED_QTY, WORK_ORDER.STATUS, OPERATION.STATUS
    HAVING   (WORK_ORDER.STATUS = 'R')
             AND (OPERATION.STATUS = 'R')
    ORDER BY WORK_ORDER.USER_5;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the JOIN syntax as opposed to the WHERE syntax to form a join.

    you will want to use a LEFT JOIN as opposed to an outer join
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    sorry no time at the moment to parse through your SQL above. Here's a quick example that might help:

    Code:
    select some columns
       from orders   ord
    -- this table will always have an entry for an order
    inner join orders_status ord_st
         on ord.ord_nbr = ord_st.ord_nbr
    
    --this table may not have an entry for an order
    LEFT OUTER JOIN order_special_instructions    ord_SI
         on ord.ord_nbr = ord_si.ord_nbr
    Dave

  4. #4
    Join Date
    Dec 2015
    Posts
    3
    Quote Originally Posted by dav1mo View Post
    sorry no time at the moment to parse through your SQL above. Here's a quick example that might help:

    Code:
    select some columns
       from orders   ord
    -- this table will always have an entry for an order
    inner join orders_status ord_st
         on ord.ord_nbr = ord_st.ord_nbr
    
    --this table may not have an entry for an order
    LEFT OUTER JOIN order_special_instructions    ord_SI
         on ord.ord_nbr = ord_si.ord_nbr
    Thank you very much.

    I have been doing a lot of reading from the links above. I thought I should try to convert the comma syntax to join syntax. I thought it was good until I ran it and my excel crashed. Here is what I did. Do anything jump out that at you that is wrong?

    Code:
    SELECT DISTINCT SHOP_VIEW.RESOURCE_ID,
                    WORK_ORDER.USER_5,
                    WORK_ORDER.BASE_ID,
                    WORK_ORDER.PART_ID,
                    WORK_ORDER.DRAWING_ID,
                    PART.DESCRIPTION,
                    OPERATION.COMPLETED_QTY,
                    OPERATION.RUN_HRS,
                    OPERATION.ACT_RUN_HRS,
                    '*%2'+WORK_ORDER.BASE_ID+'$'+CONVERT(nvarchar(MAX),WORK_ORDER.LOT_ID)+'$'+CONVERT(nvarchar(MAX),WORK_ORDER.SUB_ID)+'$'+CONVERT(nvarchar(MAX),WORK_ORDER.SPLIT_ID)+'$'+CONVERT(nvarchar(MAX),OPERATION.SEQUENCE_NO)+'%*' AS 'BarCode',
                          WORK_ORDER.DESIRED_QTY
    
    FROM BFE.dbo.PART PART INNER JOIN BFE.dbo.WORK_ORDER ON WORK_ORDER.PART_ID=PART.ID,
    
         BFE.dbo.OPERATION OPERATION INNER JOIN BFE.dbo.SHOP_VIEW SHOP_VIEW ON OPERATION.RESOURCE_ID = SHOP_VIEW.RESOURCE_ID,
    
         BFE.dbo.OPERATION INNER JOIN BFE.dbo.WORK_ORDER WORK_ORDER ON OPERATION.WORKORDER_BASE_ID = WORK_ORDER.BASE_ID
    
         
    WHERE ((WORK_ORDER.DESIRED_WANT_DATE IS NOT NULL) AND (SHOP_VIEW.VIEW_ID='MSHOP'))
    
    GROUP BY SHOP_VIEW.RESOURCE_ID,
             WORK_ORDER.USER_5,
             WORK_ORDER.BASE_ID,
             WORK_ORDER.PART_ID,
             WORK_ORDER.DRAWING_ID,
             PART.DESCRIPTION,
             OPERATION.COMPLETED_QTY,
                       OPERATION.RUN_HRS,
    			'*%2'+WORK_ORDER.BASE_ID+'$'+CONVERT(nvarchar(MAX),WORK_ORDER.LOT_ID)+'$'+CONVERT(nvarchar(MAX),WORK_ORDER.SUB_ID)+'$'+CONVERT(nvarchar(MAX),WORK_ORDER.SPLIT_ID)+'$'+CONVERT(nvarchar(MAX),OPERATION.SEQUENCE_NO)+'%*',
                                 OPERATION.ACT_RUN_HRS,
                                                 WORK_ORDER.DESIRED_QTY,
                                                 WORK_ORDER.STATUS,
                                                 OPERATION.STATUS
    HAVING (WORK_ORDER.STATUS='R')
    AND (OPERATION.STATUS='R')
    ORDER BY WORK_ORDER.USER_5
    Thanks
    Triump

  5. #5
    Join Date
    Dec 2015
    Posts
    3
    After looking at your example I changed a few thing around. I think I had the place I was joining on backwards. Not sure if it matters. Cant try it till tomorrow at work. Anyway... This is what I am going to try tomorrow.

    Code:
    SELECT DISTINCT SHOP_VIEW.RESOURCE_ID,
                    WORK_ORDER.USER_5,
                    WORK_ORDER.BASE_ID,
                    WORK_ORDER.PART_ID,
                    WORK_ORDER.DRAWING_ID,
                    PART.DESCRIPTION,
                    OPERATION.COMPLETED_QTY,
                    OPERATION.RUN_HRS,
                    OPERATION.ACT_RUN_HRS,
                    '*%2'+WORK_ORDER.BASE_ID+'$'+CONVERT(nvarchar(MAX),WORK_ORDER.LOT_ID)+'$'+CONVERT(nvarchar
    
    (MAX),WORK_ORDER.SUB_ID)+'$'+CONVERT(nvarchar(MAX),WORK_ORDER.SPLIT_ID)+'$'+CONVERT(nvarchar(MAX),OPERATION.SEQUENCE_NO)+'%*' AS 'BarCode',
                          WORK_ORDER.DESIRED_QTY
    
    FROM BFE.dbo.OPERATION OPERATION INNER JOIN BFE.dbo.SHOP_VIEW SHOP_VIEW ON OPERATION.RESOURCE_ID = SHOP_VIEW.RESOURCE_ID,
    
         BFE.dbo.OPERATION INNER JOIN BFE.dbo.WORK_ORDER WORK_ORDER ON OPERATION.WORKORDER_BASE_ID = WORK_ORDER.BASE_ID,
         
         BFE.dbo.WORK_ORDER LEFT OUTER JOIN BFE.dbo.PART PART ON WORK_ORDER.PART_ID = PART.ID
    
         
    WHERE ((WORK_ORDER.DESIRED_WANT_DATE IS NOT NULL) AND (SHOP_VIEW.VIEW_ID='MSHOP'))
    
    GROUP BY SHOP_VIEW.RESOURCE_ID,
             WORK_ORDER.USER_5,
             WORK_ORDER.BASE_ID,
             WORK_ORDER.PART_ID,
             WORK_ORDER.DRAWING_ID,
             PART.DESCRIPTION,
             OPERATION.COMPLETED_QTY,
                       OPERATION.RUN_HRS,
    			'*%2'+WORK_ORDER.BASE_ID+'$'+CONVERT(nvarchar(MAX),WORK_ORDER.LOT_ID)+'$'+CONVERT(nvarchar
    
    (MAX),WORK_ORDER.SUB_ID)+'$'+CONVERT(nvarchar(MAX),WORK_ORDER.SPLIT_ID)+'$'+CONVERT(nvarchar(MAX),OPERATION.SEQUENCE_NO)+'%*',
                                 OPERATION.ACT_RUN_HRS,
                                                 WORK_ORDER.DESIRED_QTY,
                                                 WORK_ORDER.STATUS,
                                                 OPERATION.STATUS
    HAVING (WORK_ORDER.STATUS='R')
    AND (OPERATION.STATUS='R')
    ORDER BY WORK_ORDER.USER_5
    Thanks
    Triump

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    yes, the commas after your ON clauses, what are they for? Look back at the example I gave you, no comma afterwards. Also, I would suggest when using explicit JOIN syntax you put all predicates for a table into that table's ON clause. Couple of reasons for this. First would be maintenance, makes it much quicker and easier for anyone coming along to make changes to the SQL when all conditions for that table are in that one place, also, makes it easier for someone after you to read and understand it. Next is a performance item, if it were a LEFT OUTER JOIN on the SHOP_VIEW table and you put that condition into the WHERE clause as you did, not only would the performance suffer greatly, you would not be getting the correct results. As that would change the OUTER join back into an INNER after it had been OUTER joined.
    Dave

Tags for this Thread

Posting Permissions

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