Query help

[stigissimo]

Can someone help me make a query?

I have a table with a companyid, employee_id and a interest_id,This could look like this:

comp_id...empl_id...int_id
1...............1.................1
1...............1.................2
1...............1.................3
1...............2.................2
2...............8.................1
2...............8.................3

I'am looking employees of companies who are only interested in 1 and 3( in this case only com 2 empl 8, and not com 1 empl 1). I'am also looking for employees of companies who are only interested in id 2(that would be only com 1 empl 2)

I hope someone can help me with this.

Kind regards

Erik

 

[PHV]

Some starting points:
employees of companies who are only interested in 1 and 3
SELECT comp_id, empl_id
FROM yourTable
GROUP BY comp_id, empl_id
HAVING MIN(int_id)=1 AND MAX(int_id)=3 AND COUNT(*)=2

employees of companies who are only interested in id 2
SELECT comp_id, empl_id
FROM yourTable
GROUP BY comp_id, empl_id
HAVING MIN(int_id)=2 AND MAX(int_id)=2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[stigissimo]

tnx for you help, helped a lot!

 

[pentegr]

If you're looking for all the employee itersted in int_id 1 or 3 try this:

SELECT comp_id, empl_id
FROM yourTable
WHERE int_id IN (1,3)