Tuesday, March 20, 2012

Non-Equi Joins

I am taking Microsoft Course 4329 on Joining Data from Multiple Tables in
SQL Server 2005, and I am stumped in the Lab, Task 2, Step 3.

How can I rewrite the following SQL Statement to produce the same results by
using a non-equality operator in the join?

use adventureworks
select firstname + ' ' + lastname as name, gender, vacationhours as hours
from humanresources.employee e join person.contact c on e.contactid=c.contactid
where gender='f' and vacationhours>50
order by hours desc

Thanks in advance for your help!

--Justin--

There may be mulitple answers,

use adventureworks
select firstname + ' ' + lastname as name, gender, vacationhours as hours
from humanresources.employee e CROSS JOIN person.contact c where e.contactid=c.contactid
and gender='f' and vacationhours>50
order by hours desc

or

use adventureworks
select firstname + ' ' + lastname as name, gender, vacationhours as hours
from humanresources.employee e JOIN person.contact c On vacationhours>50 where e.contactid=c.contactid
and gender='f'
order by hours desc

No comments:

Post a Comment