April 30, 2010 at 10:53 am
ok so i have 3 columns. chips, salt, date in the [SNACK] table.
here's a quick example:
[chips] [salt] [date]
________________________________
chip1 yes monday
chip2 yes tuesday
chip3 no tuesday
chip2 yes friday
chip2 no monday
chip1 no friday
chip4 no tuesday
chip4 yes monday
chip5 no thursday
chip5 no friday
how would the logic go in a select statement if i wanted
just chips & salt, but only the chips that have 'no' salt.
so i can have repeating chip names such as 'chip2' which
has never been salted, but not sure how the query would go.
any samples out there?
i know it's not as simple as this...
select chips, salt from [SNACK] where salt = 'no'
that wouldn't work because it would only return chips that
had no salt on one day or another, but what i'm looking for
are chips that have never been salted on any date. so only
return the chips that have never had a 'yes' for salt.
something with a GROUP BY perhaps?
thoughts?
April 30, 2010 at 11:03 am
I can think of two ways to do this;
first, lets think it thru: you can find a group of chips that have been salted, right?
--chips that had salt
select distinct chips from [SNACK] where salt = 'yes'
so you should be able to use a subselect or a NOT IN statement to find items that are not in that group:
--probably the most intuitive
select chips, salt
from [SNACK]
where [SNACK] .salt = 'no' --this is redundant, since the next part would exclude the other possibility
AND chips NOT IN(select distinct chips from [SNACK] where salt = 'yes')
Lowell
April 30, 2010 at 12:15 pm
Wow... You guys are really great!
I used your example, and it worked perfectly!
select distinct chips, salt --, date
from SNACKS
where SNACKS .salt= 'No'
AND chips NOT IN(select distinct chips from SNACKS where salt= 'No')
order by chips asc
Many Thanks 🙂
April 30, 2010 at 12:39 pm
99posts (4/30/2010)
select distinct chips, salt --, datefrom SNACKS
where SNACKS .salt= 'No'
AND chips NOT IN(select distinct chips from SNACKS where salt= 'No')
order by chips asc
If what you want are never-been-salted chips query would be much simpler...
select distinct s.chips, s.salt
from SNACKS as S
where s.chips NOT IN(select b.chips from SNACKS as B where b.chips = s.chips and b.salt= 'Yes')
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 30, 2010 at 1:45 pm
Just for fun I took the two queries from above and added antoher method using the ROW_NUMBER() fucntion along with sample data incase the OP wants to compare them.
DECLARE @snack TABLE([chips] VARCHAR(10), [salt] VARCHAR(3), [date] VARCHAR(10))
INSERT @snack
SELECT 'chip1', 'yes', 'monday'
UNION ALL SELECT 'chip2', 'yes', 'tuesday'
UNION ALL SELECT 'chip3', 'no', 'tuesday'
UNION ALL SELECT 'chip2', 'yes', 'friday'
UNION ALL SELECT 'chip2', 'no', 'monday'
UNION ALL SELECT 'chip1', 'no', 'friday'
UNION ALL SELECT 'chip4', 'no', 'tuesday'
UNION ALL SELECT 'chip4', 'yes', 'monday'
UNION ALL SELECT 'chip5', 'no', 'thursday'
UNION ALL SELECT 'chip5', 'no', 'friday'
select chips, salt
from @snack AS SNACK
where [SNACK] .salt = 'no'
AND chips NOT IN(select distinct chips from @snack where salt = 'yes')
SELECT
Chips,
Salt
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Chips ORDER BY Salt DESC) AS RowNum
FROM @snack
) AS T
WHERE
RowNum = 1
AND Salt = 'no'
select distinct s.chips, s.salt
from @snack as S
where s.chips NOT IN(select b.chips from @snack as B where b.chips = s.chips and b.salt= 'Yes')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply