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
 Query help needed

Author  Topic 

kravi88
Starting Member

3 Posts

Posted - 2014-06-06 : 11:54:21
Hi !

I'm new to SQL and was struggling with a query. The tables are as below. This is just a sample set. The actual data is a lot !


emp | role | duty
-----------------------------------------------------------------
name role | role duty | duty conflict
-----------------------------------------------------------------
David role1 | role1 duty1 | duty1 duty2
David role2 | role2 duty2 | duty2 duty1
Tom role3 | role3 duty3 |
Jen role4 | role4 duty1 |

Desired output:

Output:

name role duty conflict
---------------------------------
David role1 duty1 duty2
David role2 duty2 duty1


Thanks and regards,
Ravi.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-06 : 11:57:56
A couple of things:
1. Can you put your data in a consumable format (i.e.: create table and insert statements)
2. Can you describe in words what the logic to "transform" the source into your desired output?

Go to Top of Page

kravi88
Starting Member

3 Posts

Posted - 2014-06-06 : 12:03:20
Im not able to copy and paste what I have on Excel.
I'll try to paste the data again.

emp
name role
David role1
David role2
Tom role3
Jen role4

role
role duty
role1 duty1
role2 duty2
role3 duty3
role4 duty1


duty
duty conflict
duty1 duty2
duty2 duty1

desired output:
name role duty conflict
David role1 duty1 duty2
David role2 duty2 duty1


Basically, employees can have multiple roles. Roles have Duties.
I need to check if there are conflicting duties.
And I want the query to display name, role, duty and conflicting duty
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-07 : 00:09:29
Is this what you want

CREATE TABLE emp(name varchar(MAX),[role] varchar(MAX))
INSERT INTO emp VALUES('David','role1'),('David','role2'),('Tom','role3'),('Jen','role4')
CREATE TABLE role([role] VARCHAR(MAX),duty VARCHAR(MAX))
INSERT INTO role VALUES('role1','duty1'),('role2','duty2'),('role3','duty3'),('role4',' duty1')
CREATE TABLE duty(duty VARCHAR(MAX),conflict VARCHAR(MAX))
INSERT INTO duty VALUES('duty1','duty2'),('duty2','duty1')

SELECT E.name
, E.[role]
, d.duty
, d.Conflict
FROM emp E
INNER JOIN role R
ON E.[role] = R.[Role]
INNER JOIN duty d
ON R.duty = D.duty


---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-06-07 : 08:06:56
Maybe this:
select e1.name
,e1.role
,r1.duty
,d1.conflict
from emp as e1
inner join role as r1
on r1.role=e1.role
inner join duty as d1
on d1.duty=r1.duty
where exists (select 1
from emp as e2
inner join role as r2
on r2.role=e2.role
inner join duty as d2
on d2.duty=r2.duty
or d2.conflict=r2.duty
where e2.name=e1.name
and r2.role<>r1.role
and r2.duty<>r1.duty
)
Surpose Jen has both role1 and role4 (which points to duty1), do you consider this a conflict?
If so, remove the red line
Go to Top of Page
   

- Advertisement -