Two Table records matching query

[oleanganesh]

Hi

I have 2 tables:
Employees
---------
empid

EmpGroup
--------
empid
Groupid

I would like to write a query to fetch the empid,Groupid from empgroup table ONLY if the empid in empgroup table has an exact match for the empid in Employees table for the groupid in empgroup table.

Sample Employees table data:
------------------
Empid
5
6
7
35

Sample EmpGroup Table data:
----------------------------
Empid GroupId
5 3
6 3
7 3
5 4
6 4
8 4
====================================

I would like to have the records like
Empid GroupId
5 3
6 3
7 3

Records for empidid=5,6,8 and groupid=4 under empgroup table shouldnt be listed as empid =8 is missing in Employees.empid

How can i write a program or query to get the results as in my sample records result?

Any help appreciated

Thanks
Gk

 

[NoCoolHandle]

something like


select G.EmpID,G.GroupID
From Employees E join EmpGroup G
on E.EmpID = G.EmpID
where G.EmpID = 3

 

[NoCoolHandle]

Messed up the where clause

select G.EmpID,G.GroupID
From Employees E join EmpGroup G
on E.EmpID = G.EmpID
where G.[blue]GroupID[/blue] = 3

 

[oleanganesh]

Thanks Nocoolhandle.

But if you have looked at my sample results, it will display the records only if all the empids matches in both tables for the groupid.

The query you have written will return records even if there is one matching record missing in employees table.

 

[NoCoolHandle]

Ok I think I am getting what you are asking...

try

select G.EmpID,G.GroupID
From Employees E join EmpGroup G
on E.EmpID = G.EmpID
where not G.GroupID in
(select GroupID from Employees E1 Right Outer join EmpGroup G1 on E1.EmpID = G1.EmpID
where E1.EmpID is null)

There is probably a better way to write it, but I don't have sql infront of me to play with it.

HTH

Rob