select query

[G venkatesh]

Hi All,

I have following 2 tables

1. Employee table with Emp_ID and Emp_Name
2. Vehicle table with Veh_ID, Emp_ID, Vehicle
And both are related with one to many relationship.

In these case, Can can I know the list of employees having Car and Bike. The
list should not include if employee is having only Car or only Bike or (Car
and Van) Or (Bike and Van)

Regards
Venkatesh

 

[John Spencer]

Simplest method is probably to use two sub-queries in the WHERE clause.

SELECT E.Emp_ID, E.Emp_Name
FROM Employee as E
WHERE E.Emp_ID IN
(SELECT V.Emp_ID
FROM Vehicle as V
WHERE V.Vehicle= 'Car')
AND
E.Emp_ID IN
(SELECT V2.Emp_ID
FROM Vehicle as V2
WHERE V2.Vehicle= 'Bike')

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[G venkatesh]

Thank you Spencer. It works just as my requirement.