Link to home
Start Free TrialLog in
Avatar of Brock
BrockFlag for Canada

asked on

Sql select problem

Hello,

The following is the data that I need to model in sql :

runcntl          OPRID      EMPLID       cntrct    DEPTID              
TEST               L             2001548      148137      8437             
TEST               L              2001548 148137      8500             
TEST             L               2001548 148139      8450             
TEST            L             2001548      148139      8460             
TEST            L               4086876      148140      8370             
TEST            L               4088876  148151      8370             
TESTS            L               4088876      148152      8370             
TEST           L              9700994 148150      8360             
TEST           L             9700994      148150      8370             
TEST           L             4086876      148152      8370             


I do not want  the values below to appear in the result

TEST   L      4088876      148151      8370
TEST   L      4088876      148152      8370
TEST   L      4086876      148152      8370


So I only want the EMPLID who have different deptids either within a single cntrct or across multiple cntrcts.

Can this be done without a CTE?

Thanks, Brock
ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brock

ASKER

Thank you for your responses.  Working on this - this morning.

Brock
Avatar of Brock

ASKER

Thank you, to Both!

Olaf - for some reason my boss does not want to see me using CTE.  We get peer reviews of code and someone complained :-(
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brock

ASKER

Thank you, Olaf.

Unfortunately,  I don't have much say in the matter :-)

Yes, I saw the multiple solutions. And I am greatful.

I  have not been here lately and wanted to reward both options...'this is my solution' was a bit confusing.

Thank you,
Brock