November 2, 2012 at 12:00 pm
Hi,
I need help excluding some rows from one table, but based on two columns.
Example:
CARS YEAR
Volvo 2001
Volvo 2006
Volvo 2009
Buick 2002
Buick 2005
Buick 2010
So let's say I want to exclude the Volvo 2006 and the Buick 2005 rows. How do I do this?
November 2, 2012 at 12:10 pm
There are a few ways this could be done. Here is one example. Notice how I posted ddl and sample data to make setting up your problem easy.
;with cte as
(
select 'Volvo' as CARS, 2001 as Year union all
select 'Volvo', 2006 union all
select 'Volvo', 2009 union all
select 'Buick', 2002 union all
select 'Buick', 2005 union all
select 'Buick', 2010
)
select *, cars + str(YEAR, 4) from cte
where cars + str(YEAR, 4) not in ('Volvo2006', 'Buick2005')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 2, 2012 at 12:24 pm
Another method is with an OR in the where clause and use parenthesis to group your and conditions.
;with cte as
(
select 'Volvo' as CARS, 2001 as Year union all
select 'Volvo', 2006 union all
select 'Volvo', 2009 union all
select 'Buick', 2002 union all
select 'Buick', 2005 union all
select 'Buick', 2010
)
SELECT * FROM cte
WHERE (CARS = 'Volvo' AND Year <> 2006)
OR (CARS = 'Buick' AND Year <> 2005)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 2, 2012 at 12:26 pm
I would probably go with capnhector's code. It will probably be better for performance. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 2, 2012 at 1:38 pm
Was thinking about this during a meeting and one challenge with capnhectors code is that it could get unwieldy pretty quickly.
If the list contained more values for manufacturer this could could ugly. You would have to continuously update your query.
Consider what happens just by adding two new CARS to the list.
;with cte as
(
select 'Volvo' as CARS, 2001 as Year union all
select 'Volvo', 2006 union all
select 'Volvo', 2009 union all
select 'Buick', 2002 union all
select 'Buick', 2005 union all
select 'Buick', 2010 union all
select 'Honda', 2012 union all
select 'Hyundai', 2013
)
The somewhat oddball way I put the query together would still work unchanged. It would return everything not on the original exclusion list. With capnhectors version you would have to add a condition to the where clause to return the new values.
--This will return both Honda and Hyundai
select *, cars + str(YEAR, 4) from cte
where cars + str(YEAR, 4) not in ('Volvo2006', 'Buick2005')
--This will not return the new values
SELECT * FROM cte
WHERE (CARS = 'Volvo' AND Year <> 2006)
OR (CARS = 'Buick' AND Year <> 2005)
--To make this work you would have to add something like below to return Honda and Hyundai
or CARS not in ('Buick', 'Volvo')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 2, 2012 at 2:21 pm
Sean Lange (11/2/2012)
Was thinking about this during a meeting and one challenge with capnhectors code is that it could get unwieldy pretty quickly.If the list contained more values for manufacturer this could could ugly. You would have to continuously update your query.
Consider what happens just by adding two new CARS to the list.
;with cte as
(
select 'Volvo' as CARS, 2001 as Year union all
select 'Volvo', 2006 union all
select 'Volvo', 2009 union all
select 'Buick', 2002 union all
select 'Buick', 2005 union all
select 'Buick', 2010 union all
select 'Honda', 2012 union all
select 'Hyundai', 2013
)
The somewhat oddball way I put the query together would still work unchanged. It would return everything not on the original exclusion list. With capnhectors version you would have to add a condition to the where clause to return the new values.
...
to answer your what about more cars i have the following:.
;with cte as
(
select 'Volvo' as CARS, 2001 as Year union all
select 'Volvo', 2006 union all
select 'Volvo', 2009 union all
select 'Buick', 2002 union all
select 'Buick', 2005 union all
select 'Buick', 2010 union all
select 'Honda', 2012 union all
select 'Hyundai', 2013
),
DontWant as (SELECT 'Volvo' AS make, 2006 AS year UNION ALL
SELECT 'Buick', 2005)
SELECT *
FROM cte c
WHERE NOT EXISTS (SELECT 1 FROM DontWant d WHERE d.make = c.CARS AND d.year = c.Year)
use a second table to hold the makes and years you do not want and then use a correlated subquery with a not exists.
and your right about the challenge with my code as the data gets more complex my initial answer breaks down fast from a maintainability stand point.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 2, 2012 at 4:39 pm
You could also use WHERE with NOT:
...
WHERE NOT (
(CARS = 'Volvo' AND Year = 2006) OR
(CARS = 'Buick' AND Year = 2005) --OR ...
)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 2, 2012 at 5:54 pm
Similar to capnhector last option presented, here's another one that might have disadvantages but works for the example.
;with cte as
(
select 'Volvo' as CARS, 2001 as Year union all
select 'Volvo', 2006 union all
select 'Volvo', 2009 union all
select 'Buick', 2002 union all
select 'Buick', 2005 union all
select 'Buick', 2010
),
excluded as
(
select 'Volvo' as CARS, 2006 as Year union all
select 'Buick', 2005
)
SELECT * FROM cte
EXCEPT
SELECT * FROM excluded
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply