July 1, 2012 at 2:21 pm
Hi,
I hope somebody can help me solve the following problem.
I need to select unique records based on a combination of 2 or 3 columns. Its basically a 3 level mapping table that I build up.
To set everything up please run the following scritps:
-- ===================
-- Source table & data
-- ===================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExternalSource]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ExternalSource](
[locname1] [varchar](max) NULL,
[locname2] [varchar](max) NULL,
[locname3] [varchar](max) NULL
) ON [PRIMARY]
END
INSERT [dbo].[ExternalSource] ([locname1], [locname2], [locname3]) VALUES (N'Location1', N'Floor1', N'Room123')
INSERT [dbo].[ExternalSource] ([locname1], [locname2], [locname3]) VALUES (N'Location2', N'Floor2', N'Room234')
INSERT [dbo].[ExternalSource] ([locname1], [locname2], [locname3]) VALUES (N'Location3', N'Floor2', N'Room111')
-- ===================
-- Destination table
-- ===================
CREATE TABLE [dbo].[Location](
[LocationID] [int] IDENTITY(1,1) NOT NULL,
[CompanyID] [tinyint] NOT NULL,
[ParentID] [int] NULL,
[LocationCode] [nvarchar](20),
[LocationName] [nvarchar](60) NOT NULL,
[CanAssign] [bit] NOT NULL)
Results of ExternalSource will look as follows:
locname1 locname2 locname3
---------- ---------- -----------
Location1 + Floor1 + Room123
Location2 + Floor2 + Room234
Location3 + Floor2 + Room111
For instance, the first insert at level 1 will look like this:
-- Level 1 records in the mapping table
insert into Location
(
CompanyID,
ParentID,
LocationName,
CanAssign
)
select distinct 1, NULL, ES.locname1, 1
from dbo.ExternalSource ES
where ES.locname1 not in (select LocationName from Location) and ES.locname1 is not null
Above results will look as follows:
Location1
Location2
Location3
...
The above is not a problem because I select all the distinct records at level 1
Now for level 2 in the mapping the query look like this:
-- Level 2 records in the mapping table
insert into Location
(
CompanyID,
ParentID,
LocationName,
CanAssign
)
select 1, max(Loc.LocationID), ES.locname2, 1
from ExternalSource ES
left join Location Loc on ES.locname1 = Loc.LocationName
where ES.locname2 not in (select LocationName from Location) and ES.locname2 is not null and ES.locname1 is not null
group by ES.locname2
order by ES.locname2
Above results will look as follows:
Floor1
Floor2
This is where the challenge get introduced.
If you look at the results of ExternalSource you will notice that there are two Floor2 records on the locname2 column, but they are unique at row level because they have a different value at the first level (locname1).
How can I adjust the query at level 2 to include both Floor2 records based on uniqueness at row level?
The expected results should look like this:
Floor1
Floor2
Floor2
I would really appreciate your help on this.
To clean up:
-- ===================
-- Clean up
-- ===================
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExternalSource]') AND type in (N'U'))
DROP TABLE [dbo].ExternalSource
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Location]') AND type in (N'U'))
DROP TABLE [dbo].[Location]
Thanking you in advance for any help or hints you may be able to provide.
July 1, 2012 at 3:01 pm
You're asking us to look at the results of something when we have no schema nor data to work from, unfortuantely we're not going to be able to help much here without some more details from you.
If you check out the first link in my signature below, it'll show you the best way to setup your question so that we'll have the best chance at answering it accurately.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 2, 2012 at 8:15 am
Just wondering, why can't you just do
SELECT DISTINCT ES.locname1, ES.locname2
FROM dbo.ExternalSource ES etc etc
when selecting for your unique values at level 2?
July 3, 2012 at 12:47 am
Hi,
Thanks for taking the time to get back to me.
I have updated the post to make more sense and per your suggestion.
If there is anything I still missed please let me know.
July 3, 2012 at 8:29 am
ryno-544382 (7/3/2012)
Hi,Thanks for taking the time to get back to me.
I have updated the post to make more sense and per your suggestion.
If there is anything I still missed please let me know.
The ddl and sample data seems to work. I don't understand at all what your desired output is. Can you explain that more clearly?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 3, 2012 at 10:49 am
Hi
If you look at the source table (ExternalSource) you will see there are two Floor2 under locname2.
Now, a simple distinct will only return one Floor2 I.e.
Floor1
Floor2
However, based on the previous column (locname1) they are unique and as such I do need to have a second Floor2.
The results in the case of the sample data provided should look like this when doing the second insert:
16Floor11
17Floor21
18Floor21
July 3, 2012 at 10:59 am
ryno-544382 (7/3/2012)
HiIf you look at the source table (ExternalSource) you will see there are two Floor2 under locname2.
Now, a simple distinct will only return one Floor2 I.e.
Floor1
Floor2
However, based on the previous column (locname1) they are unique and as such I do need to have a second Floor two.
The results in the case of the sample data provided should look like this when doing the second insert:
16Floor11
17Floor21
18Floor21
OK you need to step back and look at your explanation. It doesn't make any sense. You provided 3 rows of information. I don't understand what the above sample output is. Where do the values 6,7,8 come from?
The more I look this the less sense I can make of it. Please explain clearly what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 3, 2012 at 11:03 am
I compiled all the separate query windows into a single larger script to make things easier. Please take a look and see if this is correct.
-- ===================
-- Source table & data
-- ===================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExternalSource]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ExternalSource](
[locname1] [varchar](max) NULL,
[locname2] [varchar](max) NULL,
[locname3] [varchar](max) NULL
) ON [PRIMARY]
END
INSERT [dbo].[ExternalSource] ([locname1], [locname2], [locname3]) VALUES (N'Location1', N'Floor1', N'Room123')
INSERT [dbo].[ExternalSource] ([locname1], [locname2], [locname3]) VALUES (N'Location2', N'Floor2', N'Room234')
INSERT [dbo].[ExternalSource] ([locname1], [locname2], [locname3]) VALUES (N'Location3', N'Floor2', N'Room111')
-- ===================
-- Destination table
-- ===================
CREATE TABLE [dbo].[Location](
[LocationID] [int] IDENTITY(1,1) NOT NULL,
[CompanyID] [tinyint] NOT NULL,
[ParentID] [int] NULL,
[LocationCode] [nvarchar](20),
[LocationName] [nvarchar](60) NOT NULL,
[CanAssign] [bit] NOT NULL)
-- Level 1 records in the mapping table
insert into Location
(
CompanyID,
ParentID,
LocationName,
CanAssign
)
select distinct 1, NULL, ES.locname1, 1
from dbo.ExternalSource ES
where ES.locname1 not in (select LocationName from Location) and ES.locname1 is not null
-- Level 2 records in the mapping table
insert into Location
(
CompanyID,
ParentID,
LocationName,
CanAssign
)
select 1, max(Loc.LocationID), ES.locname2, 1
from ExternalSource ES
left join Location Loc on ES.locname1 = Loc.LocationName
where ES.locname2 not in (select LocationName from Location) and ES.locname2 is not null and ES.locname1 is not null
group by ES.locname2
order by ES.locname2
select * from ExternalSource
select * from Location
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExternalSource]') AND type in (N'U'))
DROP TABLE [dbo].ExternalSource
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Location]') AND type in (N'U'))
DROP TABLE [dbo].[Location]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 3, 2012 at 11:29 am
Ok so here is the updated sql for the second insert into the Location table, I think this might help:
select 1 as CompanyID, max(Loc.LocationID) as ParentID, ES.locname2 as LocationName, 1 as CanAssign
from ExternalSource ES
left join Location Loc on ES.locname1 = Loc.LocationName
where ES.locname2 not in (select LocationName from Location) and ES.locname2 is not null and ES.locname1 is not null
group by ES.locname2
order by ES.locname2
6,7,8 is the PK (LocationID) on my Location table - I deleted rows earlier and didn't reset the identenity count. Sorry for the confusion.
The first insert as per my original question is not a problem at all, all I want at the first insert is unique Location names.
Now at my second insert I need to be able to tell whether ExternalSource.locname2 & Location.LocationName are unique in a "combined" fashion, if that makes sense...
If they are unique, then I need to have it selected.
Here is an example:
Below is what you get when you do a select * from ExternalSource
locname1 locname2 locname3
Location1 Floor1Room123
Location2 Floor2Room234
Location3 Floor2Room111
Given the above, there is only one Floor1 so no issues there but as you can see there are two Floor2 on the locname2 column. I need a way to check if the value on locname2 + locname1 are unique when "combined". If they are I should select them both.
But lets say the output of ExternalSource where to look like this:
locname1 locname2 locname3
Location1 Floor1Room123
Location2 Floor2Room234
Location2 Floor2Room111
Note the bold Location2 above, because there are two rows with the same value on locname2 + locname1 it doesn't make it unique anymore and then the desired output whould have looked like this:
16Floor11
18Floor21
July 3, 2012 at 12:21 pm
OK let's try it this way.
Here is the code you posted and I made the change to original insert to ExternalSource.
-- ===================
-- Source table & data
-- ===================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExternalSource]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ExternalSource](
[locname1] [varchar](max) NULL,
[locname2] [varchar](max) NULL,
[locname3] [varchar](max) NULL
) ON [PRIMARY]
END
INSERT [dbo].[ExternalSource] ([locname1], [locname2], [locname3]) VALUES (N'Location1', N'Floor1', N'Room123')
INSERT [dbo].[ExternalSource] ([locname1], [locname2], [locname3]) VALUES (N'Location2', N'Floor2', N'Room234')
INSERT [dbo].[ExternalSource] ([locname1], [locname2], [locname3]) VALUES (N'Location2', N'Floor2', N'Room111')
-- ===================
-- Destination table
-- ===================
CREATE TABLE [dbo].[Location](
[LocationID] [int] IDENTITY(1,1) NOT NULL,
[CompanyID] [tinyint] NOT NULL,
[ParentID] [int] NULL,
[LocationCode] [nvarchar](20),
[LocationName] [nvarchar](60) NOT NULL,
[CanAssign] [bit] NOT NULL)
-- Level 1 records in the mapping table
insert into Location
(
CompanyID,
ParentID,
LocationName,
CanAssign
)
select distinct 1, NULL, ES.locname1, 1
from dbo.ExternalSource ES
where ES.locname1 not in (select LocationName from Location) and ES.locname1 is not null
-- Level 2 records in the mapping table
insert into Location
(
CompanyID,
ParentID,
LocationName,
CanAssign
)
select 1, max(Loc.LocationID), ES.locname2, 1
from ExternalSource ES
left join Location Loc on ES.locname1 = Loc.LocationName
where ES.locname2 not in (select LocationName from Location) and ES.locname2 is not null and ES.locname1 is not null
group by ES.locname2
order by ES.locname2
select * from ExternalSource
select * from Location
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExternalSource]') AND type in (N'U'))
DROP TABLE [dbo].ExternalSource
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Location]') AND type in (N'U'))
DROP TABLE [dbo].[Location]
I don't understand what it is you are trying to do. Help me understand the rules and I can help you with the sql. Are you trying to get help with inserting rows to Location? Take the new sample and explain to me what you to happen.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 4, 2012 at 11:44 am
Hi,
Yes I am trying to insert rows into the Location table FROM ExternalSource.
Let me try explain it this way:
As the query selects the rows from ExternalSource, it needs to do the following:
1.) Check the current row for the value on locname1 and locname2 and "combine" them in a way
I.e.
Location1-Floor1
Location2-Floor2
2.) On each row it needs to take the above combination of locname1 and locname2 and check whether they are unique by comparing them to the rest of the rows.
When I say "unique" I mean there is only one combinaiton of Location1-Floor1 for instance.
3.) If it is unique based on the combination, then it needs to select the value on locname2, if not don't select it.
Sorry, I can think of no other way to better explain myself. I guess I have it all in my head and just having a hard time to explain to you what I'm trying to do here.
July 5, 2012 at 7:54 am
Have you looked into EXCEPT? http://msdn.microsoft.com/en-us/library/ms188055.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 5, 2012 at 4:26 pm
CELKO (7/1/2012)[/bCode should be in Standard SQL as much as possible and not local dialect.
Given that this a SQL Server forum, SQL server dialect is fine. In particular, there is certainly no reason to re-code anything just to avoid something that is SQL Server specific.
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".
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply