Primary Key & Foreign Key Problems.

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

General SQL Server Forums

New to SQL Server Programming

Primary Key & Foreign Key Problems.

Author

Topic

BUser
Starting Member

11 Posts

Posted - 2010-10-06 : 02:59:14

Experts,

Hello !

Please bear with me for the long post !

To get to the point, you can jump to the next part of the posting.

To give you a primer, this is small business place and they are running on only 1 person and that is me. Im the network admin before and have logged few years on the networking side but Im totally new to databases and all I can do right now is write simple select statements to pull what the business what.

Im running into some challenges that I dont know as how to handle and please please solve my challenges here.

Past -

What the Previous programmer did was instead of deleting the value of the Primary Key columns, he added the ""…Present"" in the Product Master table and then left it alone.

Task Ahead -

Business Owner wants me to

1.
Find out which Product ID have the most highest occurence in the Sales Detail table(This has foreign key relationship with Prod Master and this is the biggest challenge)

2.
Leave that value alone

3.
And Update the value of Updatedvalue Column to the value that is occuring the most number of times.

4.
And finally delete the value of the Primary Key in the Prod Master table.

For example here,

Cotton with product ID, 566 in Prod Master has the highest number of occurences in Sales detail and hence, the UpdatedValue column should be updated with 566.

Now, delete product id's 12, 33 and 3334.

In the same way, Corn with product id, 4774 in Prod Master has the highest number of occurences in sales detail and hence the Updatedvalue column should be set to the value of 4774.

Also, delete the Corn product id's 7767, 7788 and 3343.

Please see the attached spreadsheet to get a better understanding of the data.

Thanks,
Don.
------------------------------------------------------------------------------------------------------------------------------------------
ProdMaster
ProductID Name Description FlagToDelete
12 Cotton Cotton ..Sold in quintals Yes
33 Cotton..present Cotton without any seeds Yes
566 Cotton..present Cotton no seed
3334 Cotton..present Raw cotton with no seeds Yes

4774 Corn Corn…sold in quintals
7767 Corn..present Corn - White Yes
7788 Corn..present Corn - Adm Yes
3343 Corn..present Corn - Hybrid Yes

------------------------------------------------------------------------------------------------------------------------------------------

SalesDetail
SalesID ProductID UpdatedValue
2112 12 566
7746 33 566
5574 566 566
7575 566 566
6647 566 566

48848 4774 4774
51091 7767 4774
50095 4774 4774
50029 3343 4774
54554 4774 4774
------------------------------------------------------------------------------------------------------------------------------------------

BUser
Starting Member

11 Posts

Posted - 2010-10-06 : 03:04:24

Also, can some one help me as how to upload a spreadsheet here so that readers can understand and get a better grasp of what the situation at hand is.

Also, pardon my english. I know its not good.

Thanks,
Don.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SalesDetail
SalesID ProductID UpdatedValue
2112 12 566
7746 33 566
5574 566 566
7575 566 566
6647 566 566

48848 4774 4774
51091 7767 4774
50095 4774 4774
50029 3343 4774
54554 4774 4774
ProdMaster
ProductID Name Description FlagToDelete
12 Cotton Cotton ..Sold in quintals Yes
33 Cotton..present Cotton without any seeds Yes
566 Cotton..present Cotton no seed
3334 Cotton..present Raw cotton with no seeds Yes

4774 Corn Corn…sold in quintals
7767 Corn..present Corn - White Yes
7788 Corn..present Corn - Adm Yes
3343 Corn..present Corn - Hybrid Yes

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 04:58:17

Can you please post some sample data & desired o/p?
Have a look here on how to do it

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

PBUH

BUser
Starting Member

11 Posts

Posted - 2010-10-06 : 14:18:09

Sachin.Nand & all other experts,

here is the question rephrased in simpler steps. Im running on Sql server 2005.

There are 2 tables, Product Master & Sales Detail.

ProdMaster has the columns - ProductID, Name, Description. I was asked to add another column called FlagToDelete which has Yes or No values and this was driven by the Owner once again.

SalesDetail has two important columns among many and they are ProductID and SalesID. Now, ProductID feild in SalesDetail is the Foreign Key to the ProdMaster table.

ProductID Name Description FlagToDelete
12 Cotton Cotton ..Sold in quintals Yes
33 Cotton..present Cotton without any seeds Yes
566 Cotton..present Cotton no seed No
3334 Cotton..present Raw cotton with no seeds Yes

4774 Corn Corn…sold in quintals No
7767 Corn..present Corn - White Yes
7788 Corn..present Corn - Adm Yes
3343 Corn..present Corn - Hybrid Yes

------------------------------------------------------------------------------------------------------------------------------------------

SalesDetail
SalesID ProductID UpdatedValue
2112 12 566
7746 33 566
5574 566 566
7575 566 566
6647 566 566

48848 4774 4774
51091 7767 4774
50095 4774 4774
50029 3343 4774
54554 4774 4774

There was a Programmer who was let go & before he left, he was asked to find any duplicate rows with the same data and leave the first row alone and update the rest of the copies of the Original value with a tag, ....Present.

Qestion / Challenge -

How do I update the value of Updatedvalue Column to the value that is occuring the most number of times.

For example, here in the sample data posted above,

Cotton with product ID, 566 in Prod Master has the highest number of occurences in Sales detail and hence, the UpdatedValue column should be updated with 566. So, in the new column, UpdatedValue, it should be populated with 566.

At the same time, how to delete entries in Prod Master for the Product ID's 12, 33 and 3334.

BUser
Starting Member

11 Posts

Posted - 2010-10-07 : 04:40:05

Friends,

I been sitting here lookin at the problem at hand and at age 45, it seems that I dont understand most of the things that this task needs.

Can somebodey help us please as how to write this query ?

thanks,
Don.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-07 : 05:23:42

quote:
Originally posted by BUser

Friends,

I been sitting here lookin at the problem at hand and at age 45, it seems that I dont understand most of the things that this task needs.

Can somebodey help us please as how to write this query ?

thanks,
Don.

The reason you don't understand what to do is because this is a very, very badly designed schema!

I really thought hard about posting anything because I think this is a case of:

Owner doesn't know what they want.
Programmer doesn't know what to do.

Which never ends well.

However, sounds like you need some help.

First of all

1) PLEASE READ SACHIN's LINK
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

If you actually posted data as explained in the link then I'm sure other people here would take a punt trying to help.

I don't really understand what you are asking for. I suspect that neither do you. Saying that, I think that if you reverse your approach you'll have an easier time.

This is how you find any products in ProdMaster that the previous programmer markedwith that ...Present mark.


SELECT
    [productID]
    , [Name]
    , [Description]
FROM
    ProdMaster
WHERE
    [Name] LIKE '%...present'

This should just return the rows you are interested in doing something to.

I'm affraid that I have no idea what you want to do for the first question : How do I update the value of Updatedvalue Column to the value that is occuring the most number of times.

What you seem to do be wanting to do is

1) Find the ProductID that occurs most times. You can do this with


SELECT
	[productID]
	, COUNT([productID]) AS [entries]
FROM
	SalesDetail
GROUP BY
	[productID]

2) Do *something* with this information -- this is the bit I dont' understand, you seem to be wanting to UPDATE salesId entries that have nothing to do with the salesId entry where the most number of [productId] happens. (the only similarity is in the [name] column of ProdMaster.

Anyway. Can you please read the link and post data in that form. People will try and help you if you do.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

BUser
Starting Member

11 Posts

Posted - 2010-10-07 : 15:44:41

Mr. Charlie and other experts,

I commend you stepping forward in helping me here. Sorry I coulndt be any clearer and my not so good english is not helping me either.

Anyway, here it is...

Im not sure as what else can I do. In the last step under the expected result, I want the update_value needs to be updated.

thanks
Don.

1. State the question
1.a. How do I identify records in the ProductMaster that have the same name but for all duplicate values it is updated with "...present".
1.b. how do I find records that are occuring most number of times inside the Foreign Key table ie., SalesDetail table.
2. Identify that record and here in our exmple below it is 566 and 4774.
3. Update that column update_value wtih the value of ProductID(ie., 566 & 4774) in the sales detail table
4. Delete the duplicate occurences in the primary key table.

2. Please post the DDL of your tables (Including Indexes, and constraints)

ProdMaster table has the following columns -
ProductID Name Description FlagToDelete

Sales Detail table has the following columns -
SalesID ProductID UpdatedValue

3. Post some sample data in the form of DML

INSERT INTO ProdMaster(ProductID, Name, Description, FlagToDelete)

SELECT 12, 'Cotton', 'Cotton ..Sold in quintals', '' UNION ALL
SELECT 3, 'Cotton..present', 'Cotton without any seeds','' UNION ALL
SELECT 566, 'Cotton..present', 'Cotton no seed','' UNION ALL
SELECT 3334,'Cotton..present', 'Raw cotton with no seeds',''UNION ALL

SELECT 4774, 'Corn', 'Corn…sold in quintals','' UNION ALL
SELECT 7767, 'Corn..present', 'Corn - White',''UNION ALL
SELECT 7788, 'Corn..present', 'Corn - Adm', ''UNION ALL
SELECT 3343, 'Corn..present', 'Corn - Hybrid', ''

INSERT INTO SalesDetail(SalesID,ProductID, Update_Value)
SELECT 7746, 33, ''
SELECT 5574, 566,''
SELECT 7575, 566,566,''
SELECT 6647, 566, 566,''

4. Post whatever DML that you have attempted already...

Not too much to post in front of you experts...just selects and they dont work either.

5. Post the expected results
For Product Master, it should be as sfowllows -

ProductID Name Description FlagToDelete
12, 'Cotton', 'Cotton ..Sold in quintals', 'Yes'
33, 'Cotton..present', 'Cotton without any seeds', 'Yes'
566, 'Cotton..present', 'Cotton no seed', 'NO'
3334, 'Cotton..present', 'Raw cotton with no seeds', 'Yes'

4774, 'Corn', 'Corn…sold in quintals','NO'
7767, 'Corn..present', 'Corn - White', 'Yes'
7788, 'Corn..present', 'Corn - Adm', 'Yes'
3343, 'Corn..present', 'Corn - Hybrid', 'Yes'

See here, Only rows 566 and 4774 are updated with NO and the rest are yes becasue, 566 occurs 3 times in the sales detail table while 4774 occurs 3 times as weel.

SalesID, ProductID, Update_Value
2112, 12, 566
7746, 33, 566
5574, 566, 566
7575, 566, 566
6647, 566, 566

48848, 4774, 4774
51091, 7767, 4774
50095, 4774, 4774
50029, 3343, 4774
54554, 4774, 4774

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources