SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query help needed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kravi88
Starting Member

USA
3 Posts

Posted - 06/06/2014 :  11:54:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/06/2014 :  11:57:56  Show Profile  Reply with Quote
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

USA
3 Posts

Posted - 06/06/2014 :  12:03:20  Show Profile  Reply with Quote
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

Edited by - kravi88 on 06/06/2014 12:03:50
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
107 Posts

Posted - 06/07/2014 :  00:09:29  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

406 Posts

Posted - 06/07/2014 :  08:06:56  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000