May 30, 2007 at 3:46 pm
Hello, first post here, thanks for any help.
Here is what I am having difficulting deciding:
I have an order table that contains many orders: name = table1 and it contains a load id for which that order is contained.
I have another table called loads that contains load information(load id is the primary key) name = table2.
One load in the load\table2 table may have 1 or many orders, these orders would have the same load id.
Our system may cancel an order on a load, but not all the orders.
In my exisiting query, I want to only return loads where at least one of the orders on that load has a status <> CANCEl (So if just one order is in a load and it isn't canceled it applies) but if all the loads orders are Cancel I don't want to return it.
Any suggestions for the most efficient sql for this in the where clause or in the having clause?
May 30, 2007 at 4:08 pm
DECLARE @Load TABLE (LoadID int IDENTITY(1,1), LoadDesc varchar(10))
INSERT INTO @Load (LoadDesc)
SELECT 'Load 1' UNION ALL
SELECT 'Load 2' UNION ALL
SELECT 'Load 3' UNION ALL
SELECT 'Load 4' UNION ALL
SELECT 'Load 5'
DECLARE @Orders TABLE (OrderID int IDENTITY(1,1), LoadID int, Status varchar(10))
INSERT INTO @Orders (LoadID, Status)
SELECT 1, 'Pending' UNION ALL
SELECT 1, 'Pending' UNION ALL
SELECT 2, 'Cancel' UNION ALL
SELECT 2, 'Cancel' UNION ALL
SELECT 3, 'Cancel' UNION ALL
SELECT 3, 'Pending' UNION ALL
SELECT 4, 'Cancel' UNION ALL
SELECT 4, 'Cancel' UNION ALL
SELECT 5, 'Pending' UNION ALL
SELECT 5, 'Cancel'
SELECT DISTINCT L.LoadID
FROM @Load L
INNER JOIN @Orders O
ON L.LoadID = O.LoadID
WHERE O.Status <> 'Cancel'
May 31, 2007 at 6:42 am
Thanks John,
I don't think I was very clear, what I having trouble determining, is where in the existing query can I put the logic(in the where clause or create a new having clause?) so it does return the loads that have at least one order <> CANCEL.
May 31, 2007 at 9:42 am
I think John's example is right on the money. If you need specific help changing an "existing" query then you should post the actual query. If possible you should also post the structures of all relevant tables along with some test data (as John did). An example of the output desired is also helpful so folks can validate any solution ideas they want to post.
James.
May 31, 2007 at 10:04 am
May 31, 2007 at 11:04 am
I was trying to spare anyone the agony of looking at this nasty long query.
To simplify: I was just trying to determine the best way to add to the screening to not bring back any data if any of the orders on the loads do not have status of CANCEL
I think my two options are this:
add some code in the where clause or add a having clause correct?
May 31, 2007 at 11:14 am
You can use the "Having" clause only if you are doing a group by clause and then only with columns included in the select statement.
Sounds like the where clause is the most appropriate place based on the information provided.
James.
May 31, 2007 at 11:56 am
Alright, so I will put it in the where clause. Now, what sql should I use in the where clause so it basically returns a yes if one or more of the order.status <> Cancel?
May 31, 2007 at 12:07 pm
SELECT DISTINCT L.LoadID
FROM @Load L
INNER JOIN @Orders O
ON L.LoadID = O.LoadID
WHERE O.Status <> 'Cancel'
Is there some reason the example John provided doesn't work for you? You should be able to use the exact same logic in his where clause, check the status column of a referenced table for "<> 'Cancel'"
If you are absolutely determined to use a sub-query then add something similiar to the following:
where .....
and exists(select 1 from order table where order.loadid = LoadTable.loadid and order.status <> 'Cancel')
The "exists" command is basically the yes/no (it's boolean) you are looking for. NOTE: It doesn't matter what you put as a column in the sub-select (but it can only be one column) in this case if it doesn't return a NULL then the exists command evaluates to true.
James.
May 31, 2007 at 12:16 pm
That exists did the trick (I was halfway their before your response).
thanks for all the help guys.
May 31, 2007 at 12:21 pm
Remember you did ask for the most EFFICIENT sql, and using exists() is a lot less efficient then using a join and simple where clause (i.e. something <> 'Cancel'). The exists() will execute the sub-query once for every row returned in the initial result set.
Just something to think about, especially if speed is important.
James.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply