Foreign key assign - simplified (hopefully)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Foreign key assign - simplified (hopefully)

Author

Topic

LaurieCox

158 Posts

Posted - 2012-04-27 : 10:43:28

I think I over complicated the problem I am having that I described in these two threads:

Foreign key assignment with set based insert
Foreign key assign (part 2 ... More Complicated)

The first does not have a solution. There is no way to "discover' the foreign keys without adding extra columns.

The second one should have a solution but the problem description was lost in a wall of text, and sql so that I am not surprised no one saw the question much less understood it.

So I have simplified the problem.

I have two tables (simplified from the tables in the other topics to remove extraneous stuff):


CREATE TABLE Instructions(
	InstructionId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	GroupId int,
	OrderNumber int
) 
-- ddl and dml for InputData at end of post (same table from other posts)
insert into Instructions (GroupId,OrderNumber)
select i.GroupID
     , i.OrderNumber
  from InputData i 
 where i.EventType in ('N', 'C')

CREATE TABLE ScriptDrugs(
	ScriptDrugId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	InstructionId int foreign key references Instructions(InstructionId) not null,
	GroupId int,
	OrderNumber int
)

Now I want to insert records from InputData into ScriptDrugs with the correct InstructionId.

So the question is how is the correct InstructionId selected.


Instruction Table
InstructionId GroupId     OrderNumber
------------- ----------- -----------
4             5834        1
7             5834        4
5             5834        5
3             178248      1
6             178248      2
1             178248      3
2             178248      5

Input Data
GroupID     OrderNumber EventType
----------- ----------- ---------
5834        1           N
5834        2           R
5834        3           R
5834        4           C
5834        5           C
178248      1           N
178248      2           C
178248      3           C
178248      4           R
178248      5           C
178248      6           R

I have color coded the lines that show which records from the InputData table should join with which record in the Instructions table.

So why were the red rows linked? Note that the OrderNumber for the three red rows in the InputData are equal to or greater than the OrderNumber in the red row in the Instructions table but less than the OrderNumber in the green row.

Or if you want to look at it from the EventType column: all Reorders (for a given group) map back to the most recent previous change or new order. New and Changed records (for a given group) map directly to the corresponding New or Changed record in the Instructions table.

So I am trying to write the join to this insert statement:


insert into ScriptDrugs (InstructionId,GroupId,OrderNumber)
select i.InstructionId
     , d.GroupID
     , d.OrderNumber
  from InputData d join Instructions i on ??????????????????????????????

That will get me these rows in the ScriptDrugs table


ScriptDrugId InsructionId GroupID     OrderNumber
------------ ------------ ----------- -----------
<auto gen>   1            5834        1
<auto gen>   1            5834        2
<auto gen>   1            5834        3
<auto gen>   7            5834        4
<auto gen>   5            5834        5
<auto gen>   3            178248      1
<auto gen>   6            178248      2
<auto gen>   1            178248      3
<auto gen>   1            178248      4
<auto gen>   2            178248      5
<auto gen>   2            178248      6

Laurie

InputData:


CREATE TABLE InputData(
	[GroupID] [int] NULL,
	[OrderNumber] [int] NOT NULL,
	[EventType] [varchar](1) NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL
) 
select 178248, 3, 'C', '2008-10-02 00:00:00.000', '2008-12-23 14:00:47.000' union all
select 178248, 5, 'C', '2009-03-16 00:00:00.000', '2009-06-14 00:00:00.000' union all
select 178248, 1, 'N', '2008-05-05 00:00:00.000', '2008-07-10 00:00:00.000' union all
select 5834, 3, 'R', '2010-01-19 16:57:06.000', '2010-01-26 00:00:00.000' union all
select 5834, 1, 'N', '2010-01-19 00:00:00.000', '2010-01-19 16:28:02.000' union all
select 5834, 5, 'C', '2010-03-02 00:00:00.000', '2010-03-09 00:00:00.000' union all
select 178248, 4, 'R', '2008-12-23 14:00:47.000', '2009-03-16 00:00:00.000' union all
select 178248, 6, 'R', '2009-06-17 14:19:08.000', '2009-09-15 00:00:00.000' union all
select 178248, 2, 'C', '2008-07-10 00:00:00.000', '2008-10-02 00:00:00.000' union all
select 5834, 2, 'R', '2010-01-19 16:28:02.000', '2010-01-19 16:57:06.000' union all
select 5834, 4, 'C', '2010-01-26 00:00:00.000', '2010-02-25 00:00:00.000'

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-27 : 11:33:29

I got issues with the colors you used http://en.wikipedia.org/wiki/Color_blindness If you could use some numbering scheme as a forth column, might be able to help you

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

LaurieCox

158 Posts

Posted - 2012-04-27 : 12:19:19

Hi yosiasz,

Sorry about that. I am having a hard time putting into words how the mapping works. While I work on that here are the tables without color and quick attempt at explaining it.


Instructions:
InstructionId GroupId     OrderNumber  
------------- ----------- -----------  
4             5834        1            
7             5834        4            
5             5834        5            
3             178248      1            
6             178248      2            
1             178248      3            
2             178248      5            

InputData:
GroupID     OrderNumber EventType  (Maps to InsturctionId)
----------- ----------- ---------  ---------------------
5834        1           N          4          
5834        2           R          4          
5834        3           R          4

Look at the OrderNumbers in the InputData table for the these three rows they are >= to OrderNumber in the first row in the Instructions table but are less than the next higher OrderNumber in the Instructions table (for the given group). So that is the row (InsructionId = 4) that they map to.


5834        4           C          7          

5834        5           C          5          

178248      1           N          3          

178248      2           C          6          

178248      3           C          1          
178248      4           R          1 

178248      5           C          2          
178248      6           R          2

Edit: Because I had the last two rows mapping to InstuctionId 6 and they should map to InstuctionId 2.

You should be able to see a pattern between the OrderNumbers in the InputData table and the rows that they map to in the Instructions table.

If I can come up with a better description I will post it.

And much thanks to anyone who can help.

Laurie

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-27 : 13:01:22

spongebob I mean algaesea :)

please re-post the color coded rows and columns (without color) with a 4th numeric column with column names and all

I believe you are trying too hard to explain it and confusing yourself and others. simply state here is the sample data and here is the expected result.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

LaurieCox

158 Posts

Posted - 2012-04-27 : 13:10:58

Ok so this might help explain the problem a little better.

I have discovered if I run this query:


select InstructionId
	 , GroupId
	 , OrderNumber  
 from (select *
            , ROW_NUMBER() over (partition by groupId order by OrderNumber desc) as RecId
		from Instructions x
		where x.OrderNumber <=  4 
		  and x.GroupId = 5834 ) as i
 where RecId = 1

Plugging in valid pairings of GroupId and OrderNumber from the InputData table I get the correct InstructionId for mapping.

Therefore if SQL would allow me to write a query like this:


select i.InstructionId
     , d.*
  from InputData d join	                     
         (select InstructionId
               , GroupId
               , OrderNumber  
            from (select *
                      , ROW_NUMBER() over (partition by groupId order by 
                                           OrderNumber desc) as RecId
                    from Instructions x
                    where x.OrderNumber <= d.OrderNumber
                     and x.GroupId = d.GroupId) as i
           where RecId = 1) i

I would have my solution. Of course it doesn’t as the d.OrderNumber and d.GroupId do not exists inside the inner select.

So how do I do this

Laurie

LaurieCox

158 Posts

Posted - 2012-04-27 : 13:13:38

Hi

I forgot to look to see if I had any responses before I posted my last message showing my work on the solution so far.

Anyway in respone to your post. Here is the basic information.


InputData table

GroupID     OrderNumber EventType row 
----------- ----------- --------- ---
5834        1           N         1
5834        2           R         2
5834        3           R         3
5834        4           C         4
5834        5           C         5
178248      1           N         6
178248      2           C         7
178248      3           C         8
178248      4           R         9
178248      5           C         10
178248      6           R         11

Instructions Table

InstructionId GroupId     OrderNumber  row
------------- ----------- -----------  ---
4             5834        1            1
7             5834        4            2
5             5834        5            3
3             178248      1            4
6             178248      2            5
1             178248      3            6
2             178248      5            7

ScriptDrugs table (Expected Results:

ScriptDrugId InsructionId GroupID      OrderNumber  (maps to Instruction table row)
------------ ------------ -----------  -----------  -------------------------------
<auto gen>   4            5834         1            1
<auto gen>   4            5834         2            1
<auto gen>   4            5834         3            1
<auto gen>   7            5834         4            2
<auto gen>   5            5834         5            3
<auto gen>   3            178248       1            4
<auto gen>   6            178248       2            5
<auto gen>   1            178248       3            6
<auto gen>   1            178248       4            6
<auto gen>   2            178248       5            7
<auto gen>   2            178248       6            7

Edit: I had the InstructionId wrong in the first three records.

I am trying to write this insert statement that will give the expected results shown above:


insert into ScriptDrugs (InstructionId,GroupId,OrderNumber)
select i.InstructionId
     , d.GroupID
     , d.OrderNumber
  from InputData d join Instructions i on ??????????????????????????????

Laurie

LaurieCox

158 Posts

Posted - 2012-04-27 : 15:12:51

I solved it:


insert into ScriptDrugs (InstructionId,GroupId,OrderNumber)
select i.InstructionId
     , d.GroupID
     , d.OrderNumber
  from InputData d join Instructions i on i.GroupId = d.GroupID
 where i.InstructionId in 
          (select InstructionId
             from (select *
                , ROW_NUMBER() over (partition by groupId order by OrderNumber desc) as RecId
		             from Instructions x
		            where x.OrderNumber <=  d.OrderNumber 
		              --and x.GroupId = d.GroupID 
		    ) as f
            where RecId = 1

Yeah!!!!!!!!!!!

Any critique of my solution would be appreciated.

Laurie

Edit to clean up formatting and it doesn't look like I need the x.GroupId = d.GroupId in the subquery.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 15:39:37

quote:

Originally posted by AlgaeSea

I solved it:


insert into ScriptDrugs (InstructionId,GroupId,OrderNumber)
select i.InstructionId
     , d.GroupID
     , d.OrderNumber
  from InputData d join Instructions i on i.GroupId = d.GroupID
 where i.InstructionId in 
          (select InstructionId
             from (select *
                , ROW_NUMBER() over (partition by groupId order by OrderNumber desc) as RecId
		             from Instructions x
		            where x.OrderNumber <=  d.OrderNumber 
		              --and x.GroupId = d.GroupID 
		    ) as f
            where RecId = 1

Yeah!!!!!!!!!!!

Any critique of my solution would be appreciated.

Laurie

Edit to clean up formatting and it doesn't look like I need the x.GroupId = d.GroupId in the subquery.



insert into ScriptDrugs (InstructionId,GroupId,OrderNumber)
select i.InstructionId
     , d.GroupID
     , d.OrderNumber
  from InputData d
 cross apply (select top 1 InstructionId
              from Instructions 
              where GroupID = d.GroupID
              and OrderNumber < = d.OrderNumber
              order by OrderNumber DESC
             )i

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LaurieCox

158 Posts

Posted - 2012-04-27 : 15:50:38

Thanks visakh16,

I like your solution better than mine. It much cleaner and easier to read.

Laurie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 15:54:12

thanks
Understand what are uses of apply here

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources