Need to find returns where a given columns multiple returns do not contain a specific value in a different column of the result set.

Question

Need to find returns where a given columns multiple returns do not contain a specific value in a different column of the result set.

cmcfarland 51322

SSC Veteran

Points: 292
More actions
January 31, 2014 at 1:19 pm

#283108

Hello!
Here's a simplified version of my table:
Table Name - CompanyInfo
Company Name Transaction Type
HazCo A
HazCo B
HazCo C
HazCo D
ShimCo A
ShimCo C
ShimCo D
BlintCo A
BlintCo C
BlintCo D
What I'm after is a return that lists all Company Names that do not have any Transaction Type 'B's.
A proper result set would look like this:
Company Name
ShimCo
BlintCo
What I've done so far filters my returns to not show any B transaction types, but doesn't mean that the Company has none of the excluded Transaction Types. Only eliminates them from the result set.
Here's a simplified version what I've written so far that matches the little sample data, but the final product probably won't resemble it very much, or at all:
select company name, transaction type from CompanyInfo as A
where
not exists
(select * from CompanyInfo where a.transaction type = 'B')
and customer name is not null
group by customer name, transaction type
What do you think?
Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Doug J. SSC Eights! Points: 832 More actions · Answer 1

Hello and welcome to the forums.

It's considered best practice around here to post sample data and table structure as DDL when asking questions. This helps other people get right to work on your questions without having to script out your table and manually insert data.

You can get more info on this here:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Since this is your first post, I've done this for you:

create table CompanyInfo([Company Name] varchar(32) not null, [Transaction Type] char(1) not null)

INSERT INTO CompanyInfo

SELECT 'HazCo', 'A'

UNION

SELECT 'HazCo', 'B'

UNION

SELECT 'HazCo', 'C'

UNION

SELECT 'HazCo', 'D'

UNION

SELECT 'ShimCo', 'A'

UNION

SELECT 'ShimCo', 'C'

UNION

SELECT 'ShimCo', 'D'

UNION

SELECT 'BlintCo', 'A'

UNION

SELECT 'BlintCo', 'C'

UNION

SELECT 'BlintCo', 'D'

With that out of the way, we'll start looking at your question.

-Doug

http://xkcd.com/327/

cmcfarland 51322 SSC Veteran Points: 292 More actions · Answer 2

Thank you so much!

I'll refer back on my next post, too.

I work very hard to work these things out on my own, and am very appreciative and respectful of the valuable time that the responders spend offering an opinion on my issues.

Doug J. SSC Eights! Points: 832 More actions · Answer 3

Would something along these lines do the trick for you? It seems to match the desired output given in your original post.

select distinct [company name] from CompanyInfo as A

where

[Company name] not in

(select [Company name] from CompanyInfo where [transaction type] = 'B')

-Doug

http://xkcd.com/327/

cmcfarland 51322 SSC Veteran Points: 292 More actions · Answer 4

Thanks for taking a look at this!

I get no errors, but no returns, either.

Doug J. SSC Eights! Points: 832 More actions · Answer 5

That's odd. Can you confirm whether the sample data I used above is correct for your situation? Because if it is, then I have no idea why you aren't seeing any results...

-Doug

http://xkcd.com/327/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

cmcfarland 51322 (1/31/2014)
Thanks for taking a look at this!
I get no errors, but no returns, either.

That's probably because you might have NULLS in [Company Name].

Here are the most common options:

--Using EXCEPT

SELECT [Company Name]

FROM #CompanyInfo

EXCEPT

SELECT [Company Name]

FROM #CompanyInfo

WHERE [Transaction Type] = 'B'

--Using NOT EXISTS

SELECT [Company Name]

FROM #CompanyInfo c

WHERE NOT EXISTS(

SELECT 1

FROM #CompanyInfo x

WHERE x.[Transaction Type] = 'B'

AND x.[Company Name] = c.[Company Name])

GROUP BY [Company Name]

--Using NOT IN ... WHERE <column> IS NOT NULL

SELECT [Company Name]

FROM #CompanyInfo c

WHERE [Company Name] NOT IN(

SELECT [Company Name]

FROM #CompanyInfo x

WHERE x.[Transaction Type] = 'B'

AND [Company Name] IS NOT NULL)

GROUP BY [Company Name]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Doug J. SSC Eights! Points: 832 More actions · Answer 7

Luis Cazares (1/31/2014)
That's probably because you might have NULLS in [Company Name].
Here are the most common options:

D'oh! Luis' explanation is very likely correct. Even if it's not, it's a major problem with the solution I proposed earlier and reason enough to avoid it. Thanks Luis!

+1

-Doug

http://xkcd.com/327/

cmcfarland 51322 SSC Veteran Points: 292 More actions · Answer 8

Yes, there are NULLs in there.

Thanks, too.

I'll bring them over for testing shortly.

Putting out quick fire...

cmcfarland 51322 SSC Veteran Points: 292 More actions · Answer 9

Hey, that was great!

I used the NOT EXISTS version, and got just what I needed!

Funny that I'd gotten fairly close!

Thanks again to everyone.

ScottPletcher SSC Guru Points: 100952 More actions · Answer 10

If you want just the company name returned, this should (will?) be more efficient:

SELECT [Company Name]

FROM dbo.CompanyInfo --#CompanyInfo

GROUP BY

[Company Name]

HAVING

MAX(CASE WHEN [Transaction Type] = 'B' THEN 1 ELSE 0 END) = 0

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

cmcfarland 51322 SSC Veteran Points: 292 More actions · Answer 11

Sure, that is cleaner, for sure.

The table was small, though, and I was able to enjoy acceptable speed using the NOT EXISTS route.

I've saved this for future use, though, and appreciate you angle.

Thanks!