SQL query with a Left outer Join

[Ad Bec]

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

[The ScareCrow]

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

[Adam]

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" <webforumsuser@macromedia.com> wrote in message
news:d3himv$6v1$1@forums.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
>