Creating Relationship between tables

Question

Creating Relationship between tables

Steven Dyson

SSC Enthusiast

Points: 162
More actions
September 6, 2007 at 2:46 am

#176550

Thanks for the help so far on this Db, but now i have a table relationship problem, I have imported all my data to find that there no Primary keys or links to each table.
I need to add a primary key field and populate with Auto Incrementing Numbers, I also need to make sure that when i import data to the table that it adds the P Key and auto increments this.
Then I need to have in another table a foriegn key that is linked to the Primary key of table 1 But needs to be between a date range.
As follows:
Table 1 Needs Primary Key called Batch_ID and has (StartDate, End Date) as fields
Table 2 Needs Foriegn Key from Table 1 where StartTime(Table 2) is between StartDate and EndDate from Table 1
hope this makes sense
Regards
Steve

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

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

Steven Dyson SSC Enthusiast Points: 162 More actions · Answer 1

Reading Previous Posts i have managed to create the Primary Key on each of the tables that i need to use, I now need to insert a Foriegn Key into 3 of my tables As requested.

Vladan SSC-Insane Points: 22072 More actions · Answer 2

I'm afraid I don't understand the requirement of "between start and end date".

What do you need to do, just create the FK constraint, or establish the relationship based on new PK identity column?

How are the tables linked now? How do you know for each row of table2 to which row of table1 it refers?

Maybe it would help if you are more specific - what is in table1 and what is in table2 - what is their meaning?

Steven Dyson SSC Enthusiast Points: 162 More actions · Answer 3

Thanks for the reply,

Details are.

Table 1

Downtime_ID | StartTime | Shift_ID | Duration

1 | 09/07/2007 16:49:00 | NULL | 53.7809998989105

Table 2

Batch_ID | BatchName | Batch Start | Batch End | Line 3 Uptime

1 | 2007/07/12 06:00 | 12/07/2007 06:00:00 | 12/07/2007 18:00:00 | 12974.9640016556

There are no relationships between the tables at all at the moment.

I need a statement that will look at table 1 and check whether the StartTime is between BatchStart and BatchEnd, If it is Then Update Shift_ID in Table 1 with the Batch_ID in Table 2

Does this make more sense

Steve

Vladan SSC-Insane Points: 22072 More actions · Answer 4

If really the date columns alone are enough to identify the matching rows, then it could be like this:

UPDATE t1

SET Shift_ID = t2.Batch_ID

FROM table1 t1

JOIN table2 t2 ON t1.StartTime >= t2.[Batch Start] AND t1.StartTime <= t2.[Batch End]

However, be careful and check first whether the result is as you expect. What if there are 2 rows in table2 that match the criteria? Is that something that will never happen by design (i.e., there was some check in place to ensure that in table2 the start-end periods can never overlap)? If it can happen, then you need some additional criteria, because you need to pinpoint exactly one row from table2, otherwise the results of update will be random.

Steven Dyson SSC Enthusiast Points: 162 More actions · Answer 5

Thanks for that,

This should by design be a 1 to Many relationship as far as Table 1 Is ShiftDate Ie will be StartDate(09/09/2007 06:00:00) EndDate(09/09/2007 18:00:00) and StartDate(09/09/2007 18:00:00) EndDate(10/09/2007 06:00:00)

It will be very unlikly that a Downtime occurence happens right on the button, so far it has not happened

<= t2.[Batch Start] AND t1.StartTime >

On your statement above UPDATE t1 (should i replace t1 with my tablename)

Regards

Steve

Vladan SSC-Insane Points: 22072 More actions · Answer 6

OK, seems that the time ranges should not overlap, except a single possibility if it happens right on the dot when the shift changes. If the data are as you described, then this should work nicely:

UPDATE t1

SET Shift_ID = t2.Batch_ID

FROM table1 t1

JOIN table2 t2 ON t1.StartTime >= t2.[Batch Start] AND t1.StartTime < t2.[Batch End]

Mark that I have changed the comparison with batch end to < instead of <= to avoid any problems if it happens in that moment.

Replace "table1" and "table2" with your actual table names. Do not replace the "t1" in the "UPDATE t1" - as soon as you define aliases, you need to use them in the line with "UPDATE ...".

Steven Dyson SSC Enthusiast Points: 162 More actions · Answer 7

Thank You,

That worked like a dream, I can now update my Crystal Reports as i have had to change some table Names..

This should help me immensly. I now have to figure out how to fire off an email at 06:05 and 18:05 with the Crystal report Exported as a PDF document OH JOY

Regards

Steve Dyson

Steven Dyson SSC Enthusiast Points: 162 More actions · Answer 8

Also,

What will happen if i re run this statement tomorrow when i have imported tomorrows data and got Null values, Will it just update what it can or will it update all

Regards

Steve

Vladan SSC-Insane Points: 22072 More actions · Answer 9

"Will it just update what it can or will it update all"

That question looks a bit funny 🙂 if something can't be updated, then it won't be updated. Problem is, what do you mean by "what it can"? All rows that match criteria will be updated. If you only want to update rows that have NULL in the column (e.g. after every import into this table), then you have to modify the SQL:

UPDATE t1

SET Shift_ID = t2.Batch_ID

FROM table1 t1

JOIN table2 t2 ON t1.StartTime >= t2.[Batch Start] AND t1.StartTime < t2.[Batch End]

WHERE t1.Shift_ID IS NULL

Steven Dyson SSC Enthusiast Points: 162 More actions · Answer 10

Vladan,

That works brilliantly, especially when i spell the tablenames correctly

Thanks for the help, this is an ongoing project thats gonna need a lot of help here and there, so thanks very much

STeve