Looking for elegant solution! Finding match over multiple rows

Question

Looking for elegant solution! Finding match over multiple rows

jeff.born

Ten Centuries

Points: 1136
More actions
April 16, 2014 at 7:00 am

#287685

Hi everyone,
I have a new requirement that I'm not seeing an elegant way to capture this data. Everything right now is multiple SQL statements for me and then comparing the results.
Sample table data:
Column A Column B Column C
1 10 Pig
1 10 Cow
1 10 Horse
1 15 Pig
1 15 Pig
2 20 Cow
2 20 Horse
2 25 Pig
3 30 Cow
3 30 Pig
3 30 Pig
3 30 Horse
4 40 Pig
4 45 Cow
4 50 Horse
4 55 Pig
I am matching on column A which in my specific example matches to 5 rows. Then grouping by column B which shows that I have a count of three, but a row on each of the three in column C needs to have the Pig, Cow, and Horse.
I'd like to know for the A grouping, what that count is
I'd like to know for the A,B grouping, how many total are in that grouping, and if all three distinct values of C are present.
How would you accomplish this?

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

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

before we begin, would please repost your input data in a code block that includes the DDL and DML needed to populate the data. Then, show the query you have built so far and a sample of the desired output of the final solution.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Sorry... post withdrawn. I really misread the question.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

your test data.....

USE [tempdb]

SET NOCOUNT ON

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SampleTable]') AND type in (N'U'))

DROP TABLE [dbo].[SampleTable]

GO

CREATE TABLE [dbo].[SampleTable](

[ColA] [int] NULL,

[ColB] [int] NULL,

[ColC] [varchar](50) NULL

) ON [PRIMARY]

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (1, 10, N'Pig')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (1, 10, N'Cow')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (1, 10, N'Horse')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (1, 15, N'Pig')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (2, 20, N'Cow')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (2, 20, N'Horse')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (2, 25, N'Pig')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (3, 30, N'Cow')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (3, 30, N'Pig')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (3, 30, N'Horse')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (4, 40, N'Pig')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (4, 45, N'Cow')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (4, 50, N'Horse')

INSERT [dbo].[SampleTable] ([ColA], [ColB], [ColC]) VALUES (4, 55, N'Pig')

SET NOCOUNT OFF

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

jeff.born Ten Centuries Points: 1136 More actions · Answer 4

Hi all, I've added some code below. I had everything working until I found a case that returns more than complete data. I have to let the caller know via a flag in my return table (or stored procedure output value, I can figure that out later) that either Partial, Complete, more than complete data was found. Unfortunately that data is not consistent, which is why I'm asking for better ideas. Right now on line 120, matching to 885257 will bring back more than complete data which my logic will not trap for. Also on line 120 matching to 327772 will falsely tell me I have a complete set when in fact the Pig, Cow, and Horse are in two different "Code" areas.

USE [tempdb];

GO

SET NOCOUNT ON;

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Farm]') AND type in (N'U'))

DROP TABLE [dbo].[Farm];

GO

CREATE TABLE [dbo].[Farm]

(

[FarmID_pk]INT NOT NULL CONSTRAINT PK_FarmID_pk PRIMARY KEY,

[FarmName] VARCHAR(40) NOT NULL CONSTRAINT DF_Farm_FarmName DEFAULT(''),

[ST] VARCHAR(2) NOT NULL CONSTRAINT DF_Farm_ST DEFAULT(''),

[FarmIP] VARCHAR(5) NOT NULL CONSTRAINT DF_Farm_FarmIP DEFAULT(''),

[ClassCode] VARCHAR(2) NOT NULL CONSTRAINT DF_Farm_ClassCode DEFAULT(''),

[ConsolidatedFarmFlag]INT NOT NULL CONSTRAINT DF_Farm_ConsolidatedFarmFlag DEFAULT(0),

[CodeToMatchTo]INT NULL

);

TRUNCATE TABLE [dbo].[Farm];

INSERT INTO [dbo].[Farm] ([FarmID_pk], [FarmName], [ST], [FarmIP], [ClassCode], [ConsolidatedFarmFlag], [CodeToMatchTo]) VALUES (10, 'Sweet Acres', 'NJ', '32250', 'C5', 0, 885257);

INSERT INTO [dbo].[Farm] ([FarmID_pk], [FarmName], [ST], [FarmIP], [ClassCode], [ConsolidatedFarmFlag], [CodeToMatchTo]) VALUES (20, 'Happy Hills', 'AL', '88990', 'C4', 0, 112334);

INSERT INTO [dbo].[Farm] ([FarmID_pk], [FarmName], [ST], [FarmIP], [ClassCode], [ConsolidatedFarmFlag], [CodeToMatchTo]) VALUES (25, 'Tomatoes', 'GA', '27732', 'C3', 0, 999777);

INSERT INTO [dbo].[Farm] ([FarmID_pk], [FarmName], [ST], [FarmIP], [ClassCode], [ConsolidatedFarmFlag], [CodeToMatchTo]) VALUES (30, 'Hays', 'NY', '98333', 'C2', 0, 654789);

INSERT INTO [dbo].[Farm] ([FarmID_pk], [FarmName], [ST], [FarmIP], [ClassCode], [ConsolidatedFarmFlag], [CodeToMatchTo]) VALUES (35, 'Old Red', 'NJ', '60606', 'D2', 0, 343434);

INSERT INTO [dbo].[Farm] ([FarmID_pk], [FarmName], [ST], [FarmIP], [ClassCode], [ConsolidatedFarmFlag], [CodeToMatchTo]) VALUES (40, 'Seven Barns', 'TN', '44004', 'DD', 0, 676767);

INSERT INTO [dbo].[Farm] ([FarmID_pk], [FarmName], [ST], [FarmIP], [ClassCode], [ConsolidatedFarmFlag], [CodeToMatchTo]) VALUES (45, 'Water Saver', 'TN', '44004', 'D3', 0, 811123);

INSERT INTO [dbo].[Farm] ([FarmID_pk], [FarmName], [ST], [FarmIP], [ClassCode], [ConsolidatedFarmFlag], [CodeToMatchTo]) VALUES (50, 'Weather Vains', 'TN', '44004', 'B1', 0, 766773);

INSERT INTO [dbo].[Farm] ([FarmID_pk], [FarmName], [ST], [FarmIP], [ClassCode], [ConsolidatedFarmFlag], [CodeToMatchTo]) VALUES (55, 'Bales', 'TN', '44004', 'CC', 0, 909011);

INSERT INTO [dbo].[Farm] ([FarmID_pk], [FarmName], [ST], [FarmIP], [ClassCode], [ConsolidatedFarmFlag], [CodeToMatchTo]) VALUES (60, 'Hooves', 'IL', '60089', 'BC', 0, 327772);

-- SELECT * FROM [dbo].[Farm];

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Animal]') AND type in (N'U'))

DROP TABLE [dbo].[Animal];

GO

CREATE TABLE [dbo].[Animal]

(

[ID]INTNOT NULL CONSTRAINT PK_ID_pk PRIMARY KEY CLUSTERED,

[FeatureID]INTNOT NULL,

[Code]VARCHAR(5)NOT NULL,

[Name]VARCHAR(128)NOT NULL,

[TypeName]VARCHAR(40)NOT NULL,

[PhoneNumber]VARCHAR(10)NOT NULL,

[ST]VARCHAR(2)NOT NULL,

[DistrictIPCode]VARCHAR(5)NOT NULL,

[LastUpdateDate]DATETIMENOT NULL

);

GO

TRUNCATE TABLE [dbo].[Animal];

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (1, 10, 'NJ133', 'George', 'Pig', '4404044402', 'NJ', '120', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (2, 10, 'NJ133', 'George', 'Cow', '4404044403', 'NJ', '120', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (3, 10, 'NJ133', 'Henry', 'Horse', '4404044404', 'NJ', '120', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (4, 10, 'NJ027', 'Luke', 'Pig', '4404044405', 'NJ', '120', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (5, 10, 'NJ027', 'Luke', 'Pig', '4404044406', 'NJ', '120', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (6, 20, 'AL000', 'Betsy', 'Cow', '4404044407', 'AL', '330', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (7, 20, 'AL000', 'Betsy', 'Horse', '4404044408', 'AL', '330', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (8, 25, 'GA018', 'Dan', 'Pig', '4404044409', 'GA', '800', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (9, 30, 'NY111', 'Charle', 'Cow', '4404044410', 'NY', '909', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (10, 30, 'NY111', 'Charle', 'Pig', '4404044411', 'NY', '909', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (11, 30, 'NY111', 'Charle', 'Pig', '4404044412', 'NY', '909', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (12, 30, 'NY111', 'Charle', 'Horse', '4404044413', 'NY', '909', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (13, 40, 'TN002', 'Ethel', 'Pig', '4404044414', 'TN', '506', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (14, 45, 'TN003', 'Ethan', 'Cow', '4404044415', 'TN', '508', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (15, 50, 'TN004', 'Margret', 'Horse', '4404044418', 'TN', '640', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (16, 55, 'TN005', 'Leo', 'Pig', '4404044418', 'TN', '708', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (17, 60, 'IL001', 'Zep', 'Pig', '4404044418', 'IL', '312', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (18, 60, 'IL002', 'Harvey', 'Cow', '4404044418', 'IL', '311', GETDATE());

INSERT INTO [dbo].[Animal] (ID, FeatureID, Code, Name, TypeName, PhoneNumber, ST, [DistrictIPCode], LastUpdateDate) VALUES (19, 60, 'IL002', 'Drago', 'Horse', '4404044418', 'IL', '310', GETDATE());

-- SELECT * FROM [dbo].[Animal];

DECLARE @rc INT

,@PigFound BIT = 0

,@CowFound BIT = 0

,@HorseFound BIT = 0

,@AnimalsFound BIT = 0

,@MoreThanCompleteSetFound BIT = 0

,@CompleteSetFound BIT = 0

,@PartialSetFound BIT = 0;

DECLARE @FarmMatches TABLE ([OverallID]INT PRIMARY KEY

,[FarmName]VARCHAR(40)

,[ST]VARCHAR(2)

,[FarmIP]VARCHAR(5)

,[ZIPCode]VARCHAR(5)

,[AdministrativeLevelOfData]VARCHAR(40));

DECLARE @ReturnInformation TABLE ([ReturnInformation]INT IDENTITY(1,1) PRIMARY KEY

,[OverallID]INT

,[FarmName]VARCHAR(40)

,[FarmIP]VARCHAR(5)

,[Code]VARCHAR(5)

,[Name]VARCHAR(128)

,[TypeName]VARCHAR(40)

,[PhoneNumber]VARCHAR(10)

,[ST]VARCHAR(2)

,[DistrictIPCode]VARCHAR(5)

,[DistrictName]VARCHAR(60)

,[ZIPCode]VARCHAR(5)

,[AdministrativeLevelOfData]VARCHAR(40)

,[PartialSetFound]BIT

,[CompleteSetFound]BIT

,[MoreThanCompleteSetFound]BIT);

INSERT INTO @FarmMatches ([OverallID], [FarmName], [ST], [FarmIP], [ZIPCode], [AdministrativeLevelOfData])

SELECT [FarmID_pk]AS [OverallID]

,[FarmName]AS [FarmName]

,[ST]AS [State]

,[FarmIP]AS [FarmIP]

,''AS [ZIPCode] -- Not known at this level

,'Small Farm'AS [AdministrativeLevelOfData]

FROM [dbo].[Farm] f

WHERE f.[CodeToMatchTo] = 327772; -- 885257;

-- SELECT * FROM @FarmMatches;

SET @AnimalsFound = (SELECT TOP 1 1 FROM @FarmMatches ri

INNER JOIN [dbo].[Animal] ani ON ani.[FeatureID] = ri.[OverallID]);

SET @AnimalsFound = ISNULL(@AnimalsFound,0);

IF (@AnimalsFound = 1)

BEGIN

SET @PigFound = (SELECT TOP 1 1 FROM @FarmMatches ri

INNER JOIN [dbo].[Animal] ani ON ani.[FeatureID] = ri.[OverallID]

WHERE [TypeName] = 'Pig');

SET @PigFound = ISNULL(@PigFound, 0);

SET @CowFound = (SELECT TOP 1 1 FROM @FarmMatches ri

INNER JOIN [dbo].[Animal] ani ON ani.[FeatureID] = ri.[OverallID]

WHERE [TypeName] = 'Cow');

SET @CowFound = ISNULL(@CowFound, 0);

SET @HorseFound = (SELECT TOP 1 1 FROM @FarmMatches ri

INNER JOIN [dbo].[Animal] ani ON ani.[FeatureID] = ri.[OverallID]

WHERE [TypeName] = 'Horse');

SET @HorseFound = ISNULL(@HorseFound, 0);

IF (@PigFound = 1 AND @CowFound = 1 AND @HorseFound = 1)

BEGIN

SET @CompleteSetFound = 1; -- This can give me a false match since I could have a different code for each Animal.TypeName.

END

ELSE

BEGIN

SET @PartialSetFound = 1;

END

-- ELSE find Complete Set plus something else?

INSERT INTO @ReturnInformation ([OverallID], [FarmName], [FarmIP], [Code], [Name], [TypeName], [PhoneNumber], [ST], [DistrictIPCode], [DistrictName], [ZIPCode], [AdministrativeLevelOfData], [PartialSetFound], [CompleteSetFound],[MoreThanCompleteSetFound])

SELECT [OverallID]AS [OverallID]

,ISNULL([FarmName],'')AS [FarmName]

,ISNULL([FarmIP],'')AS [FarmIP]

,ISNULL([Code], '')AS [Code]

,ISNULL([Name], '')AS [Name]

,ISNULL([TypeName], '')AS [TypeName]

,ISNULL([PhoneNumber], '')AS [PhoneNumber]

,ISNULL(ani.[ST], '')AS [ST]

,ISNULL([DistrictIPCode], '')AS [DistrictIPCode]

,ISNULL('District Name','')AS [DistrictName]

,ISNULL(ri.[ZIPCode],'')AS [ZIPCode]

,ISNULL([AdministrativeLevelOfData],'')AS [AdministrativeLevelOfData]

,@PartialSetFoundAS [PartialSetFound]

,@CompleteSetFoundAS [CompleteSetFound]

,@MoreThanCompleteSetFoundAS [MoreThanCompleteSetFound]

FROM @FarmMatches ri

LEFT OUTER JOIN [dbo].[Animal] ani ON ani.[FeatureID] = ri.[OverallID];

SET @rc = @@RowCount;

-- Add a exit proc is Complete or more than Complete Set Found

END

-- After Farm level checks I would then check Zoos, then Parks (Basically another level of checks to see if key to match to is in another table and if it is, see if there is a complete set there)

IF (@CompleteSetFound = 1 OR @MoreThanCompleteSetFound = 1)

SELECT * FROM @ReturnInformation;

DROP TABLE [dbo].[Farm];

DROP TABLE [dbo].[Animal];

ScottPletcher SSC Guru Points: 101103 More actions · Answer 5

I've coded checks for just Animal table groupings. Please check and see if it produces the results you want.

Edit: This is doing only full matches but partial matches could be determined as well.

I'm sorry but I don't fully understand the Farm requirements part of this. If the code below looks good for Animals, could you explain in words how you want Farms to be matched?

SELECT

FeatureID, Code

FROM [dbo].[Animal] a

GROUP BY

FeatureID, Code

HAVING

MAX(CASE WHEN TypeName = 'Cow' THEN 1 ELSE 0 END) = 1 AND

MAX(CASE WHEN TypeName = 'Pig' THEN 1 ELSE 0 END) = 1 AND

MAX(CASE WHEN TypeName = 'Horse' THEN 1 ELSE 0 END) = 1

SELECT

FeatureID

FROM [dbo].[Animal] a

GROUP BY

FeatureID

HAVING

MAX(CASE WHEN TypeName = 'Cow' THEN 1 ELSE 0 END) = 1 AND

MAX(CASE WHEN TypeName = 'Pig' THEN 1 ELSE 0 END) = 1 AND

MAX(CASE WHEN TypeName = 'Horse' THEN 1 ELSE 0 END) = 1

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".

jeff.born Ten Centuries Points: 1136 More actions · Answer 6

SSCommitted,

Thanks for the quick solution! I've been able to take what you gave me and tie it into the limit to the Farm.CodeToMatchTo that I'm currently searching on. On line 119 (now) I set up three searches. As I make each search active I can see the results being either a partial, complete, or MoreThanComplete set found. Would you take a look at lines 130 - 161 and see if that is the best way to set the flags? I've been doing it that way for years, but want to make sure that is the best way! Other than that double check everything works!