Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

suggestions on one to may relationship in the data processing Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2014 10:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 10:17 PM
Points: 132, Visits: 812
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.
Post #1585731
Posted Tuesday, June 24, 2014 11:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 13,359, Visits: 10,223
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1585750
Posted Wednesday, June 25, 2014 12:38 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 635, Visits: 1,156
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.

Post #1585759
Posted Wednesday, June 25, 2014 3:54 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 15,562, Visits: 27,940
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1585794
Posted Wednesday, June 25, 2014 9:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 10:17 PM
Points: 132, Visits: 812
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.
Post #1586214
Posted Thursday, June 26, 2014 1:26 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 635, Visits: 1,156
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
Post #1586259
Posted Thursday, June 26, 2014 1:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 1,389, Visits: 6,272
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')
Post #1586262
Posted Thursday, June 26, 2014 3:30 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 15,562, Visits: 27,940
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1586316
Posted Thursday, June 26, 2014 3:37 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 635, Visits: 1,156
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.

Post #1586322
Posted Thursday, June 26, 2014 6:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:02 PM
Points: 1,220, Visits: 6,574
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.
Post #1586391
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse