How to check the columns within the table?

Question

Post reply

How to check the columns within the table?

Charmer

SSChampion

Points: 14903
More actions
May 7, 2012 at 7:45 am

#273588

Hi Friends,
I have column names called
ID Prefix StreetName StreetType IntPrefix IntStreetName IntStreetType
1 W Main St S 1st St
2 S 1st St W Main St
if you look at the two rows, the data in prefix, streetname,streettype in 1st row is same as intprefix,intstreetname,intstreettype in 2nd row...
and vice versa...
so i want to check how many rows are existed as like as above scenario...
i hope you guys can understand my need...if not please lamme know...
Any suggestions would be really appreciated...
/****** Object: Table [dbo].[Temptable] Script Date: 05/07/2012 19:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Temptable](
[RangeID] [bigint] NULL,
[Prefix] [char](2) NULL,
[StreetName] [varchar](30) NOT NULL,
[StreetType] [char](6) NULL,
[StreetSuffix] [char](2) NULL,
[IntPrefix] [char](2) NULL,
[IntStreetName] [varchar](30) NULL,
[IntStreetType] [char](6) NULL,
[IntStreetSuffix] [char](2) NULL,
[PremiseType] [char](2) NULL,
[Census] [char](6) NULL,
[PatrolArea] [char](2) NULL,
[SubZone] [char](2) NULL,
[Dist] [char](3) NULL,
[SubDist] [char](2) NULL,
[GCModifier] [char](2) NULL,
[VotingDist] [char](2) NULL,
[CivilDist] [char](4) NULL,
[CadRef] [varchar](255) NULL,
[RAIDRef] [char](8) NULL,
[NPAIDRef] [char](4) NULL,
[ESNL] [char](10) NULL,
[ESNR] [char](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'MARION', N'ST ', NULL, NULL, N'RAYMIR', N'PL ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'WU ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, NULL, N'HARLEY DAVIDSON', N'AVE ', NULL, N'N ', N'119TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'WU ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, NULL, N'KAVANAUGH', N'PL ', NULL, NULL, N'HARWOOD', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'WU ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'45TH', N'ST ', NULL, N'W ', N'ANTHONY', N'DR ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'44TH', N'ST ', NULL, N'W ', N'ANTHONY', N'DR ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'45TH', N'ST ', NULL, N'W ', N'OHIO', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'SCHAUER', N'AVE ', NULL, N'W ', N'MIDLAND', N'DR ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'ANTHONY', N'DR ', NULL, N'W ', N'MIDLAND', N'DR ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'SCHAUER', N'AVE ', NULL, N'W ', N'ANTHONY', N'DR ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'46TH', N'ST ', NULL, N'W ', N'HOLT', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'45TH', N'ST ', NULL, N'W ', N'FOREST HOME', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'MW ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'39TH', N'ST ', NULL, N'W ', N'HOWARD', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'HOWARD', N'AVE ', NULL, N'S ', N'40TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'72ND', N'ST ', NULL, N'W ', N'PLAINFIELD', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'WILDWOOD', N'LN ', NULL, N'S ', N'WOLLMER', N'RD ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'WA ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'WOLLMER', N'RD ', NULL, N'S ', N'108TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'WA ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'MORGAN', N'AVE ', NULL, N'S ', N'RIVER GLEN', N'LN ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'WA ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'45TH', N'ST ', NULL, N'W ', N'HOWARD', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'MW ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, NULL, N'TERRACE', N'CT ', NULL, N'N ', N'68TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'WU ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, NULL, N'HILLSIDE', N'LN ', NULL, N'N ', N'68TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'WU ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, NULL, N'AUBURN', N'AVE ', NULL, N'N ', N'68TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'WU ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, NULL, N'HONEY CREEK', N'PKWY ', NULL, N'N ', N'68TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'WU ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, NULL, N'ROMONA', N'AVE ', NULL, N'N ', N'66TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'WU ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'WEATHERLY', N'DR ', NULL, N'S ', N'WILDING', N'DR ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'OC ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'WEATHERLY', N'DR ', NULL, N'W ', N'WILDING', N'DR ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'OC ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'ABBOTT', N'AVE ', NULL, N'S ', N'49TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'CARPENTER', N'AVE ', NULL, N'S ', N'67TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'67TH', N'ST ', NULL, N'S ', N'67TH', N'CT ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'WILDING', N'DR ', NULL, N'W ', N'WILDING', N'DR ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'OC ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'JUNIPER', N'DR ', NULL, N'S ', N'ROLLING MEADOWS', N'CT ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'OC ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'JUNIPER', N'DR ', NULL, N'S ', N'JUNIPER', N'CT ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'OC ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'ROLLING MEADOWS', N'CT ', NULL, N'S ', N'JUNIPER', N'CT ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'OC ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'58TH', N'ST ', NULL, N'W ', N'UPHAM', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'58TH', N'ST ', NULL, N'W ', N'EDGERTON', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'65TH', N'ST ', NULL, N'W ', N'HOLMES', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'60TH', N'ST ', NULL, N'W ', N'ARMOUR', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'61ST', N'ST ', NULL, N'W ', N'ARMOUR', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'62ND', N'ST ', NULL, N'W ', N'ALLERTON', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'BARNARD', N'AVE ', NULL, N'W ', N'LAYTON', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'ENGLISH MEADOW', N'DR ', NULL, N'W ', N'LAYTON', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'62ND', N'ST ', NULL, N'W ', N'LAYTON', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'60TH', N'ST ', NULL, N'W ', N'LAYTON', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'S ', N'52ND', N'ST ', NULL, N'W ', N'LAYTON', N'AVE ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'ARMOUR', N'AVE ', NULL, N'S ', N'62ND', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'ARMOUR', N'AVE ', NULL, N'S ', N'64TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'VAN NORMAN', N'AVE ', NULL, N'S ', N'64TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'VAN NORMAN', N'AVE ', NULL, N'S ', N'62ND', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'HOLMES', N'AVE ', NULL, N'S ', N'76TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'BARNARD', N'AVE ', NULL, N'S ', N'78TH', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
INSERT [dbo].[Temptable] ([RangeID], [Prefix], [StreetName], [StreetType], [StreetSuffix], [IntPrefix], [IntStreetName], [IntStreetType], [IntStreetSuffix], [PremiseType], [Census], [PatrolArea], [SubZone], [Dist], [SubDist], [GCModifier], [VotingDist], [CivilDist], [CadRef], [RAIDRef], [NPAIDRef], [ESNL], [ESNR]) VALUES (NULL, N'W ', N'BARNARD', N'AVE ', NULL, N'S ', N'81ST', N'ST ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'GF ', N'LightTow: CITY - Off Freeway Light Tows,HeavyTow: CITY - Ray''s Towing', NULL, NULL, NULL, NULL)
i have atached the DDL and DML
Thanks,
Charmer

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Great job posting the DDL, that makes it SO easy to help!

by using the INTERSEC operator, i seem to get the results you might be looking for;

you could do somethign simlar with group by or row number as well, but this is fast and easy:

SELECT Prefix, StreetName,StreetType from TempTable

INTERSECT

select intprefix,intstreetname,intstreettype from TempTable

/*

PrefixStreetNameStreetType

S 62NDST

S 67THST

S ROLLING MEADOWSCT

S WILDINGDR

S WOLLMERRD

W ANTHONYDR

W ARMOURAVE

W HOLMESAVE

W HOWARDAVE

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Charmer SSChampion Points: 14903 More actions · Answer 2

Lowell (5/7/2012)
Great job posting the DDL, that makes it SO easy to help!
by using the INTERSEC operator, i seem to get the results you might be looking for;
you could do somethign simlar with group by or row number as well, but this is fast and easy:
SELECT Prefix, StreetName,StreetType from TempTable
INTERSECT
select intprefix,intstreetname,intstreettype from TempTable
/*
PrefixStreetNameStreetType
S 62NDST
S 67THST
S ROLLING MEADOWSCT
S WILDINGDR
S WOLLMERRD
W ANTHONYDR
W ARMOURAVE
W HOLMESAVE
W HOWARDAVE
*/

Thank you so much Lowell...

if you don't mind, i have a doubt...if we want to cross check the columns to find same data, we have to use union or intersect? i was thinking union....

Thanks,
Charmer

Lowell SSC Guru Points: 323518 More actions · Answer 3

well, it depends on the results you want, right?

you had said at first you just wanted check how many rows are existed as like as above scenario...

so displaying the stuff via intersect show the matches;

i would think if you want to see all the data so you can narrow it down, you might want to JOIN the two together, on those three columns:

SELECT

T1.*,

T2.*

FROM TempTable T1

LEFT OUTER JOIN TempTable T2

ON T1.Prefix = T2.intprefix

AND T1.StreetName = T2.intstreetname

AND T1. StreetType = T2.intstreettype

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Charmer SSChampion Points: 14903 More actions · Answer 4

Lowell (5/7/2012)
well, it depends on the results you want, right?
you had said at first you just wanted check how many rows are existed as like as above scenario...
so displaying the stuff via intersect show the matches;
i would think if you want to see all the data so you can narrow it down, you might want to JOIN the two together, on those three columns:
SELECT
T1.*,
T2.*
FROM TempTable T1
LEFT OUTER JOIN TempTable T2
ON T1.Prefix = T2.intprefix
AND T1.StreetName = T2.intstreetname
AND T1. StreetType = T2.intstreettype

wow....you are absolutely correct...simple query did not strike my mind...Thank you so much Lowell...

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 5

Lowell (5/7/2012)
well, it depends on the results you want, right?
you had said at first you just wanted check how many rows are existed as like as above scenario...
so displaying the stuff via intersect show the matches;
i would think if you want to see all the data so you can narrow it down, you might want to JOIN the two together, on those three columns:
SELECT
T1.*,
T2.*
FROM TempTable T1
LEFT OUTER JOIN TempTable T2
ON T1.Prefix = T2.intprefix
AND T1.StreetName = T2.intstreetname
AND T1. StreetType = T2.intstreettype

i hope intersect is not giving me the exact result...i have attached a sample result that i want...please take a look at it...

Thanks,
Charmer