select only records that have records in a relat..

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)

select only records that have records in a relat..

Author

Topic

jwhelan
Starting Member

7 Posts

Posted - 2002-02-06 : 14:41:40

I have 2 tables:
Employees:
---------------
ID int(PK)
Name varchar(100)

Availability:
---------------
ID int(PK)
EmpID int(FK)
AvailType varchar(50)

Here is some sample data:
Employees:
------------
1, John Doe
2, John Henry
3, Jane Doe

Availability
-------------
1, 1, 'Wanted'
2, 2, 'Not Wanted'
3, 1, 'Whatever'

What I need to be able to do is, Select Employees that have an Availability of 'Wanted', and I also need to return employees that do not have an Availability (Like Jane Doe)

Thanks,
Jason

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-06 : 14:49:15

The first one:

SELECT E.* FROM Employees E INNER JOIN Availability A ON (E.ID=A.EmpID)
WHERE A.AvailType='Wanted'

The second query:

SELECT E.* FROM Employees E LEFT JOIN Availability A ON (E.ID=A.EmpID)
WHERE A.AvailType Is Null

You could combine them into 1 query:

SELECT E.*, IsNull(A.AvailType,'None') AS AvailType
FROM Employees E LEFT JOIN Availability A ON (E.ID=A.EmpID)
WHERE A.AvailType Is Null OR A.AvailType='Wanted'

If there is no availability, the IsNull() function will return "None" in that column.

jwhelan
Starting Member

7 Posts

Posted - 2002-02-06 : 14:53:34

Thanks so much for the quick reply!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources