SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


suggestions on one to may relationship in the data processing


suggestions on one to may relationship in the data processing

Author
Message
Gangadhara MS
Gangadhara MS
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 840
Hi all,

I need your suggestion on below program development

I have a scenario where one partner can have multiple competency like Grade A,Grade B or Grade C and Grade AB,Grade BC etc.

I need to pull the records of competency of higher level also there is a chance of changing this competency level in feature like Grade A of Grade AA like this.

What would be the best option to select this kind of records.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27167 Visits: 13268
Can you give the table DDL if possible, some sample input and the desired output?
In the first link of my signature you find an article that helps you with those things.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
twin.devil
twin.devil
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2340 Visits: 2666
i hope i understand it right, i think you are looking for how you want to design you tables to handle such one to many relation with Grades and partner, if so then please share your problem in more detail so that we can help you. please do review Koen suggestion.
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39341 Visits: 32624
Hard to completely understand what you're going for, but it actually sounds like a many to many relationship, not one to many. In this case, I'd have TableA which is the owner of these Grades. Then I'd have a Grade table that defines the grades. Then I'd have an OwnerGrades table that matches between the primary key of TableA and the primary key of the Grades table. Make those two columns the primary key of the third table. That way any given owner can only own one grade, but grades can be distributed to lots of owners and if you have to change the definition of a grade, it changes for everyone at the same time.

That's assuming I understand what you're asking.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Gangadhara MS
Gangadhara MS
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 840
Let me elaborate want i want to do.

I have a table
create table mastercompetency
(program varchar(10),
SKUgroup varchar(10),
competencyname varchar(10))

insert into mastercompetency values('DIRECT','SKG1','GOLD 1')
insert into mastercompetency values('DIRECT','SKG1','GOLD 2')
insert into mastercompetency values('EA','SKG2','GOLD 1')
insert into mastercompetency values('EA','SKG1','SILVER 1')
insert into mastercompetency values('DIRECT','SKG1','GOLD 1')

I need to process the records based on above competency which should follow the below criteria.

It should consider (GOLD1 and GOLD 2) OR SILVER 1 kind of condition for the input data in order to pass and that competencyname which be any of either AND/OR condition.

What will be my table design for this.
twin.devil
twin.devil
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2340 Visits: 2666
Gangadhara MS (6/25/2014)
Let me elaborate want i want to do.

It should consider (GOLD1 and GOLD 2) OR SILVER 1 kind of condition for the input data in order to pass and that competencyname which be any of either AND/OR condition.



Still not clear
Jo Pattyn
Jo Pattyn
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2736 Visits: 9895
Can you elaborate further?

if you had the 3 tables grade,owner,ownergrades
it would be something like

select owner
from owners
where (
exists (select * from ownergrades inner join grades on ownergrades.grade=grades.grade and ownergrades.owner=owners.owner and grades.grade='GOLD 1')
and exists (select * from ownergrades inner join grades on ownergrades.grade=grades.grade and ownergrades.owner=owners.owner and grades.grade='GOLD 2')
)
or exists (select * from ownergrades inner join grades on ownergrades.grade=grades.grade and ownergrades.owner=owners.owner and grades.grade='SILVER 1')
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39341 Visits: 32624
I'm back where I was. I'd break it down into three tables, one to define the owner, one to define the competency and one to relate them to each other.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
twin.devil
twin.devil
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2340 Visits: 2666
Grant Fritchey (6/26/2014)
I'm back where I was. I'd break it down into three tables, one to define the owner, one to define the competency and one to relate them to each other.


+1, The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood. :-D
Greg Edwards-268690
Greg Edwards-268690
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1503 Visits: 8482
Expected result is still not real clear.
It sounds like are looking for the Max for each competency.
I tend to agree with Grant's scenario, although you might also consider a slowly changing dimension or current record flag.

Post DDL for 3 tables, sample data, and expected results.
This should help generate better answers.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search