January 31, 2014 at 1:19 pm
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!
January 31, 2014 at 1:32 pm
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.
January 31, 2014 at 1:37 pm
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.
January 31, 2014 at 1:42 pm
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')
January 31, 2014 at 1:51 pm
Thanks for taking a look at this!
I get no errors, but no returns, either.
January 31, 2014 at 2:13 pm
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...
January 31, 2014 at 2:26 pm
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]
January 31, 2014 at 2:38 pm
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
January 31, 2014 at 2:39 pm
Yes, there are NULLs in there.
Thanks, too.
I'll bring them over for testing shortly.
Putting out quick fire...
January 31, 2014 at 3:59 pm
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.
February 3, 2014 at 12:10 pm
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) "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".
February 3, 2014 at 12:58 pm
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!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply