Create a direct relationship 2

[royyan]

I have two tables.
Table1 stores all headings
Table2 stores all detailed items which should belong to one of headings.
Both headings and items have a key(integer). If an item belongs to the heading, then that HeadingKey is the closest one to the ItemKey and must smaller than ItemKey.

Now I want to assign HeadingID to Items belong to that heading.

Can anybody point a way to do this?

Thanks!

 

[amorous]

I would structure the database soemthing like this:

Table 1- Headings

HeadingID
HeadingName
HeadingDescription
ActiveInd

Table 2- Items
ItemID
HeadingID
ItemName
ItemDesc
ActiveInd

-VJ

 

[royyan]

Thank you for the reply. But I am asking how to assign correct HeadingID to items on table2 based their current relationship(Key).

 

[SQLSister]

Am I correct in assuming that the heading IDs in table one have no direct matches in table2? Am I interpreting that correctly? And that you are now trying to fix this mess?


Questions about posting. See faq183-874

 

[amorous]

I thinking he is trying to assign a HeadingID dynamically with respect to the related ItemID...

-VJ

 

[royyan]

There is no headingID in table2. HeadingID in table1 is automatically generated when I dump the data into table1.

The current relationship between items and headings is their position in the book. "Key" is basically the position of Heading or Item.

For example, I have two headings and their keys are 1200, 1250. I also have 5 items. Their keys are 1210, 1220, 1230, 1260, 1280. So I know items with key 1210, 1220, 1230 are belong to Heading 1200, and items with key 1260, 1280 are belong to Heading 1250.

Now I want to assign headingID directly to items belong to it so I have a direct relationship instead of their positions.

Hope this will make my question a little bit clear.

 

[SQLSister]

Ok how about this.

Create a temp table that has 2 columns from and to.

Write an insert statement that inserts all the heading! ids into the to column and all the headings IDs excluding the first one to the second column (maybe an update would be easier for this).

This leaves you with one empty field onthe last record, fill it withthe next logical unused heading number


Now you can write an update to update the new headingID field in table2 based on whether the value is betwen the to and from column, put the value from the from column in as the HEadingID

I know that is nasty, but it is a one-time workaround to get the initial values in. After that you should be able to pick up the value at the time you insert the record.






Questions about posting. See faq183-874

 

[royyan]

Hi SQLSister,
I just carried out your idea. Here is the complete code I wrote for this process. Thank you for the help!

CREATE TABLE #tempFromTo
  (HeadingID int, VKeyFrom int, VKeyTo int)
 
INSERT INTO #tempFromTo (HeadingID, VKeyFrom)
  SELECT HeadingID, VKey
    FROM Heading
 
UPDATE #tempFromTo
  SET VKeyTo = VKey
    FROM Heading h INNER JOIN #tempFromTo t ON h.HeadingID = (t.HeadingID + 1)

UPDATE #tempFromTo
  SET VKeyTo = 1000000000
  WHERE VKeyTo is NULL

UPDATE Listing SET Listing. HeadingID = #tempFromTo.HeadingID
  FROM #tempFromTo WHERE Listing.VKey BETWEEN VKeyFrom AND VKeyTo

 

[SQLSister]

I just love it when I point the way, but the person writes his or her own code! Kudos to you for not asking me to provide it!

Questions about posting. See faq183-874

 

[ESquared]

Just to be annoying, this can be done in one update statement. But you guys worked it out and it doesn't have to be done it again so what am I complaining for?

 

[royyan]

Better solutions never get late.
ESquared, any detail?

 

[SQLSister]

Esquared, I don't find you annoying at all. In fact I rather respect your opinion. If this was a repeated process I would agree with you. But for something done once, somtimes it is easier to avoid the complexities of using the case statement in an update. But yes indeed if this were a recurring process, I would take the time to figure out how to do it in one update. And then test both processes to see if the single update was in fact more efficient. (I can see a possibility that if there were a lot of records, it might not be. But you never know til you test!)

Questions about posting. See faq183-874

 

[ESquared]

I am so visual that sometimes I have trouble pulling the pieces together without a 'map.'

Heading:
HeadingID - identity(1,1)
VKey - some assigned number

Listing:
HeadingID - field to be determined
VKey - number that indicates relationship to the next lowest Heading VKey

Aha! Here's what was throwing me off: if your solution worked then the HeadingID is in the same sort order as HeadingKey. Is this *absolutely* true throughout ALL the rows? Did you ever create a new HeadingKey that had a lower value than the HeadingKeys already in the table?

Assuming I can rely on the sort orders being the same, the way you did, here's the query. I'm also assuming there are no VKeys in Listing that are less than all VKeys in Heading, and no VKeys in Listing that are equal to any VKeys in Heading.

UPDATE Listing
   SET Listing.HeadingID = FT.HeadingID
   FROM Listing
      INNER JOIN (
         SELECT F.HeadingID,
            VKeyFrom = F.VKey,
            VKeyTo = ISNULL(T.VKey,1000000000)
         FROM Heading F
             LEFT JOIN Heading T ON F.HeadingID + 1 = T.HeadingID
      ) FT ON Listing.VKey BETWEEN FT.VKeyFrom AND FT.VKeyTo

I *think* it's right... but I didn't test it.

 

[ESquared]

Oh... I'm also assuming there are no missing HeadingIDs. The query can be modified to remove many of these assumptions, but it was easier with them!

 

[royyan]

Hi ESquared,

Just tested your code. It worked!
I appreciate you, VJ, and SQLSister's help. SQLSister pointed me a way to write the code and you helped me making code much cleanner.

 

[ESquared]

Thanks! But still... Bah! it was a single-use thingie, how useful can my code be to you?

I enjoy this kind of puzzle. You did the exact same steps I did... I just encapsulated the temp operations in a derived table.

Can you tell how picky I am about details? Hm? Hm?

 

[royyan]

ESquqre, I forgot answer the question in your assumption. Yes, you are right. The HeadingID and VKey has the same order. When I generated HeadingID during the data import process, I set the data ordered by VKey.
As for how useful your code might be, I can tell you that it will not be just a single use thingie. I probably will put this in stored procedure and call it from some web pages to allow non-tech user to excute the process.
Again, thak you all for the input!