Professional Web Applications Themes

SQL query with a Left outer Join - Coldfusion Database Access

I have a problem with this query. I have a left outer join with a table OrderStatus but, I don't want it to reterive those records where the field OrderStatus equals 'closed'. When I apply that filter no records are returned. When I remove it I have two. etc.. Any ideas on how I could construct the SQL expression? SELECT InboxItems.ItemID, Orders.OrderType, Orders.OrderStatus, InboxItems.*, * FROM InboxItems LEFT OUTER JOIN Orders ON InboxItems.ItemID = Orders.ItemID WHERE (InboxItems.CustomerID = 1) AND (InboxItems.Status NOT LIKE 'discarded') AND (InboxItems.Status NOT LIKE 'forwarded') AND (Orders.OrderStatus NOT LIKE 'closed') ORDER BY InboxItems.DateReceived DESC...

  1. #1

    Default SQL query with a Left outer Join

    I have a problem with this query. I have a left outer join with a table
    OrderStatus but, I don't want it to reterive those records where the field
    OrderStatus equals 'closed'. When I apply that filter no records are returned.
    When I remove it I have two. etc.. Any ideas on how I could construct the SQL
    expression? SELECT InboxItems.ItemID, Orders.OrderType,
    Orders.OrderStatus, InboxItems.*, * FROM
    InboxItems LEFT OUTER JOIN Orders ON InboxItems.ItemID =
    Orders.ItemID WHERE (InboxItems.CustomerID = 1) AND (InboxItems.Status NOT
    LIKE 'discarded') AND (InboxItems.Status NOT LIKE
    'forwarded') AND (Orders.OrderStatus NOT LIKE 'closed') ORDER BY
    InboxItems.DateReceived DESC

    Ad Bec Guest

  2. #2

    Default Re: SQL query with a Left outer Join

    Without knowing the db structure and the data, I would first suggest that you
    don't use like (no need to)
    I would also suggest that you get rid of InboxItems.* and * from the select
    clause and list the actual columns you want.
    This is because you will end up with columns with duplicate names

    Next I would suggest doing the left join to a sub query

    SELECT InboxItems.ItemID, Ord.OrderType, Ord.OrderStatus,
    InboxItems.*, *
    FROM InboxItems LEFT OUTER JOIN (Select * From Orders Where Orders.OrderStatus
    <> 'closed') Ord ON InboxItems.ItemID = Ord.ItemID
    WHERE (InboxItems.CustomerID = 1) AND (InboxItems.Status <> 'discarded') AND
    (InboxItems.Status <> 'forwarded')
    ORDER BY InboxItems.DateReceived DESC


    Ken

    The ScareCrow Guest

  3. #3

    Default Re: SQL query with a Left outer Join

    I know in TSQL the WHERE clause is treated like an INNER JOIN, you have to
    do the filtering in the FROM clause.

    I believe you would need to write it like this:

    Also, I would not go with the LIKE clause it is slower in your queries, use
    = or <>

    SELECT InboxItems.ItemID, Orders.OrderType, Orders.OrderStatus,
    InboxItems.*, *
    FROM InboxItems LEFT OUTER JOIN
    Orders ON InboxItems.ItemID = Orders.ItemID AND OrderStatus <>
    'closed'
    WHERE (InboxItems.CustomerID = 1) AND (InboxItems.Status NOT
    LIKE 'discarded') AND (InboxItems.Status NOT LIKE
    'forwarded')
    ORDER BY
    InboxItems.DateReceived DESC

    "Ad Bec" <webforumsusermacromedia.com> wrote in message
    news:d3himv$6v1$1forums.macromedia.com...
    >I have a problem with this query. I have a left outer join with a table
    > OrderStatus but, I don't want it to reterive those records where the field
    > OrderStatus equals 'closed'. When I apply that filter no records are
    > returned.
    > When I remove it I have two. etc.. Any ideas on how I could construct the
    > SQL
    > expression? SELECT InboxItems.ItemID, Orders.OrderType,
    > Orders.OrderStatus, InboxItems.*, * FROM
    > InboxItems LEFT OUTER JOIN Orders ON
    > InboxItems.ItemID =
    > Orders.ItemID WHERE (InboxItems.CustomerID = 1) AND (InboxItems.Status
    > NOT
    > LIKE 'discarded') AND (InboxItems.Status NOT LIKE
    > 'forwarded') AND (Orders.OrderStatus NOT LIKE 'closed') ORDER BY
    > InboxItems.DateReceived DESC
    >

    Adam Guest

Similar Threads

  1. left outer join problem
    By softie in forum MySQL
    Replies: 5
    Last Post: August 31st, 09:27 PM
  2. Outer join?
    By Ignoramus23298 in forum MySQL
    Replies: 5
    Last Post: May 22nd, 09:06 AM
  3. Left Outer Join
    By Jared in forum MySQL
    Replies: 0
    Last Post: May 3rd, 03:31 AM
  4. OUTER JOIN
    By jorgepino in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 23rd, 12:59 PM
  5. FULL OUTER JOIN
    By beckydub in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 13th, 02:54 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