Help needed in AdventureWorks in a sql query.

Question

I was just playing with adventureworks database in sqlserver. I got stuck in a query. I wanted to Select all titles from HumanResources.Employee which are either 'Male' or 'Female' but not both. i.e if title Accountant is Male and Female both I want to leave that title. I need only those titles where Gender is either Male or Female.

I have done this till yet.

select distinct(title) from humanresources.employee where gender='M'
select distinct(title) from humanresources.employee where gender='F'

Probably a join between these two queries, would work. But If you have any other solution, please let me know.

It is not a homework. :)

Thanks in advance.

Answer 1

Use:

SELECT t.title
  FROM HUMANRESOURCES.EMPLOYEE t
 WHERE t.gender = 'M'
   AND NOT EXISTS(SELECT NULL
                    FROM HUMANRESOURCES.EMPLOYEE e
                   WHERE e.gender = 'F'
                     AND e.title = t.title)
UNION ALL
SELECT t.title
  FROM HUMANRESOURCES.EMPLOYEE t
 WHERE t.gender = 'F'
   AND NOT EXISTS(SELECT NULL
                    FROM HUMANRESOURCES.EMPLOYEE e
                   WHERE e.gender = 'M'
                     AND e.title = t.title)

Answer 2

Try this

select distinct title FROM humanResources.employee 
WHERE gender = 'M'  and 
  jobtitle not in (select title FROM humanResources.employee  WHERE gender='F') 
union
select distinct title FROM humanResources.employee 
WHERE gender = 'F'  and 
  jobtitle not in (select title FROM humanResources.employee  WHERE gender='M')

Answer 3

SELECT DISTINCT(title)
  FROM HumanResources.Employee
 WHERE t.gender = 'M'
EXCEPT
SELECT DISTINCT(t.title)
  FROM HumanResources.Employee
 WHERE t.gender = 'F'

UNION

SELECT DISTINCT(title)
  FROM HumanResources.Employee
 WHERE t.gender = 'F'
EXCEPT
SELECT DISTINCT(t.title)
  FROM HumanResources.Employee
 WHERE t.gender = 'M'

Answer 4

Here is the corrected version

select title from HumanResources.Employee as t
where gender='M' And Not Exists(select null from HumanResources.Employee as e
         where gender='F' And e.title =t.title)
Union 

select title from HumanResources.Employee as t1
where gender='F' And Not Exists(select null from HumanResources.Employee as e1
         where gender='M' And e1.title =t1.title)
order by title

Answer 5

select title from (select distinct title, gender from HumanResources.Employee) as temptable group by title having count(title) = 1