Help needed in AdventureWorks in a sql query

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.

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)

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

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') 

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

For the sake of variety, I like this one (just for aesthetics)

with WomensJobs as
(
    select distinct(Title) title from HumanResources.Employee where Gender='F' 
),
MensJobs as
(
    select distinct(Title) title from HumanResources.Employee where Gender='M'
),
WomenOnlyJobs as
(
    SELECT title from WomensJobs EXCEPT SELECT title from MensJobs
),
MenOnlyJobs as
(
    SELECT title from MensJobs EXCEPT SELECT title from WomensJobs
)
select 'M', title from MenOnlyJobs
UNION 
select 'F', title from WomenOnlyJobs

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'