Professional Web Applications Themes

Outer join problem? - Microsoft SQL / MS SQL Server

I have this query it is supposed to "not" show the record with order_num 8901ADB32700 because item_recieved is greater than item_ordered, however the query does show this record. What am I doing wrong? SELECT order_detail_org.order_num, order_detail_org.order_line_num, order_detail_org.order_line_qty, master_receiving.item_recieved FROM order_detail_org LEFT OUTER JOIN master_receiving ON order_detail_org.order_line_qty > master_receiving.item_recieved AND order_detail_org.order_line_num = master_receiving.order_line_num AND order_detail_org.order_num = master_receiving.order_num WHERE (order_detail_org.order_num LIKE '8901ADB32700')...

  1. #1

    Default Outer join problem?

    I have this query it is supposed to "not" show the record
    with order_num 8901ADB32700 because item_recieved is
    greater than item_ordered, however the query does show
    this record. What am I doing wrong?


    SELECT order_detail_org.order_num,
    order_detail_org.order_line_num,
    order_detail_org.order_line_qty,
    master_receiving.item_recieved
    FROM order_detail_org LEFT OUTER JOIN
    master_receiving ON
    order_detail_org.order_line_qty >
    master_receiving.item_recieved AND
    order_detail_org.order_line_num =
    master_receiving.order_line_num AND
    order_detail_org.order_num = master_receiving.order_num
    WHERE (order_detail_org.order_num LIKE '8901ADB32700')

    Yaheya Guest

  2. #2

    Default Re: Outer join problem?

    It should show that order because you specify "order_detail_org LEFT OUTER
    JOIN master_receiving". Simply changing it to Inner join should do it -
    assuming you still want the Where clause.

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Yaheya Quazi" <edu> wrote in message
    news:05b501c35617$ba1ec0c0$gbl... 


    oj Guest

  3. #3

    Default Re: Outer join problem?

    When I change the query to inner join I get no record.

    Here is more what of my app logic...

    master_recieving table should hold transactional data. For example order
    ABC will have item_ordered but when the order is not recieved,
    master_recieving should be blank (implying order_recieved is empty)...

    I hope my email was clear. I know I am making some major mistakes in my
    sql but can't figure out what though :(



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Yaheya Guest

Similar Threads

  1. Oracle 8i Outer Join
    By kodemonki in forum Coldfusion Database Access
    Replies: 10
    Last Post: February 16th, 06:04 PM
  2. left outer join problem
    By softie in forum MySQL
    Replies: 5
    Last Post: August 31st, 09:27 PM
  3. Outer join?
    By Ignoramus23298 in forum MySQL
    Replies: 5
    Last Post: May 22nd, 09:06 AM
  4. OUTER JOIN
    By jorgepino in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 23rd, 12:59 PM
  5. Replies: 2
    Last Post: September 18th, 09:59 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139