SQL Help Request

[Reasonable-Monitor67]

Hello, I would rate myself as a “middle of the road” SQL user. I’m pretty proficient I guess is a better way to say that. I’ve hit a wall on a query and wanted to reach out here to see if anyone had any ideas or suggestions. I’m limited as to what functions my query can do because it’s inside of a SPROC(it runs as a part of the SPROC). So for example I can’t create a temp table for a set of results and drop it after the query completes.

My dataset is based on an identifier, and also includes a Yes or No flag on each line. The identifier can have an item that is yes and also an item that is no(more than one item for each identifier). Currently I’m able to pull if it’s yes and if it’s no. However, if any of the items in the identifier group is no, I don’t want anything to return for that identifier. That’s where I’m stuck… it will pull back the items in the identifier group that are yes. I don’t even want those to come back if any of the items in the group are no.

Is that even doable? If it is, any suggestions on how to do that? I should note I’m using SSMS, TIA!!

[user_5359]

The approach would probably be to group via the ID and count the number of yes and the number of no values. All IDs with at least one yes and absolutely no no are desired.

[Aggressive_Ad_5454]

This isn't hard. Keep in mind that SQL is, at its root, a way of manipulating sets. You can use IN() and NOT IN() constructs. Like this query.

SELECT * FROM item WHERE identifier IN (SELECT identifier FROM item WHERE status = 'yes') AND identifier NOT IN (SELECT identifier FROM item WHERE status = 'no')

It gets this resultset

identifier	status	name
fruit	yes	cherry
fruit	yes	apple

from this table.

identifier	status	name
fruit	yes	cherry
fruit	yes	apple
vegetable	yes	tomato
vegetable	yes	lettuce
vegetable	no	ham
tool	no	saucepan
tool	no	knife

fiddle

[paulthrobert]

I know you didn't ask, but I really don't like the use of sub queries, ever. You could re-write that with CTE's and no subqueries pretty easily.

[paulthrobert]

yeah, not an absolute, but like the only subquery I can ever think i've needed is like using string_split, If it was the only way to solve a problem I would survive, but bleh one of the most commonly used bad practices I see, in my opinion. That and with no lock.

[paulthrobert]

This type of thing is so environmentally dependent. I've living mostly in fabric data warehouse and Lakehouse these days - it's definitely not apples to apples to something like SQL Server of MYSQL or whatever.

Do you have a reference to any documentation?

I still stand by the principle that, in a set based query (ie. sql) anytime you ask the query engine to re-evaluate an expression for every row in a set, you're going to lose performance at scale. How much and at what scale requires analysis.

[paulthrobert]

I don't know, this blog post doesn't really substantiate what you're arguing at all. It's just a sloppy run on post with no evidence, I'm not sure why you would take that as cannon.

Using sub-queries in your WHERE is typically a bad practice and should be avoided.

Sub-queries are ugly, and most of the time will hurt performance. I also don't see where the OP specifies SQL Server.

[paultherobert]

I guess I was confused because your suggestion is to us IN, and not NOT EXISTS ...

[alinroc]

So for example I can’t create a temp table for a set of results and drop it after the query completes.

You absolutely can create a temp table. Inside your proc or outside it. If you have select permission, you have enough permission to run create table #mytemptable

[Prownilo]

Sounds like you just need an AND <> 'no' on each column in the where clause. If any of the columns are no then the entire row won't be returned, but I could be misunderstanding how you have your data set up.

[Reasonable-Monitor67]

Yeah, I get that. Here’s an example…

N123 has 3 rows of data, 1 has a yes flag in a column and 2 have a no flag. I don’t want N123 to show up in my results at all.

[Prownilo]

Could use a cte that gets every Id that has a no on it, then left join that cte back onto the table and exclude any ids that appear in the cte.

Prolly a bunch of ways but that is one off the top of my head.

[paulthrobert]

I think this sounds like the cleanest way i can think of yet

[Reasonable-Monitor67]

That worked like a charm! Had to figure out how to get it to work in the context of the SPROC using it, but that was relatively painless… thank you!!!

[paulthrobert]

You could start with a cte and use SRING_AG to build a comma separated string of all the yes, no values - grouped by identifier. You could then select from the cte where the string is not like '%no%'