Query - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Query

Author

Topic

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2007-06-15 : 12:08:23

Hi,

I have a table as follows:

Job Country Role
-----------------------------
Job001 AU Role001
Job001 AU Role002
Job001 AU Role003
Job001 BE Role001
Job001 BE Role002
Job001 BE Role004
Job002 AU Role001
Job002 BE Role001
Job003 AU Role001
Job003 AU Role002
Job003 CH Role001
Job003 CH Role002
Job003 CH Role003

The script for this is:

CREATE TABLE [#JobRole]
(
[JobID] VARCHAR(50)
,[ImpCode] VARCHAR(50)
,[RoleNo] VARCHAR(50)
)

INSERT INTO [#JobRole]
SELECT 'Job001' , 'AU' , 'Role001'
UNION ALL
SELECT 'Job001' , 'AU' , 'Role002'
UNION ALL
SELECT 'Job001' , 'AU' , 'Role003'
UNION ALL
SELECT 'Job001' , 'BE' , 'Role001'
UNION ALL
SELECT 'Job001' , 'BE' , 'Role002'
UNION ALL
SELECT 'Job001' , 'BE' , 'Role004'
UNION ALL
SELECT 'Job002' , 'AU' , 'Role001'
UNION ALL
SELECT 'Job002' , 'BE' , 'Role001'
UNION ALL
SELECT 'Job003' , 'AU' , 'Role001'
UNION ALL
SELECT 'Job003' , 'AU' , 'Role002'
UNION ALL
SELECT 'Job003' , 'CH' , 'Role001'
UNION ALL
SELECT 'Job003' , 'CH' , 'Role002'
UNION ALL
SELECT 'Job003' , 'CH' , 'Role003'

I want a listing of all Jobs that differ in their role list for each country.

So, in the above example I would want Job001 to be output as the Roles differ for AU and BE.

I would not want Job002 to be output as it has the same roles for AU and BE.

I would also want Job003 to come out as the Roles vary for AU and CH.

Their could be any number of Countries per Job. All I am interested in is whether their are any differences.

Thanks in advance,
Kabir

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-15 : 14:05:13

What about this data:

Job004, CH, Role001
Job004, AU, Role002

Does that job get returned or not?

- Jeff
http://weblogs.sqlteam.com/JeffS

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2007-06-25 : 05:47:16

Sorry for the delay (I have been on holiday). Yes, that should come out aswell.

Cheers,
Kabir

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources