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.
| Author |
Topic |
|
dve
Starting Member
19 Posts |
 Posted - 2005-12-07 : 10:25:10
|
Hi,Considder the following 2 tables with data: How can I do a query that returns me all people from department 'SALES' and with firstname 'Dave' using ANSI joins ?This: SELECT * FROM employees e JOIN departments d ON e.deptID = d.id WHERE d.name = 'SALES' OR e.firstname = 'dave'doesn't work and I can see why?? But how is this done using ANSI joins? Can it be done without using UNIONs?Thanks! |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-12-07 : 10:26:06
|
| >>How can I do a query that returns me all people from department 'SALES' and with firstname 'Dave' using ANSI joins ?You want AND, not OR. You said it yourself ! |
 |
|
|
dve
Starting Member
19 Posts |
Posted - 2005-12-07 : 10:59:25
|
| Oops! You're right!!!How can I do a query that returns me all people from department 'SALES' OR with firstname 'Dave' using ANSI joins ?Thanks! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-12-07 : 11:40:41
|
| Using your original SQL statement, with the OR. I am really not sure what your question is; none of this has anything to do with joins. Just because you are using ANSI joins does not mean you can no longer put non-join criteria in your WHERE clause. |
|
|
|
chwanhun
Starting Member
22 Posts |
Posted - 2005-12-07 : 19:23:56
|
| Just by using the keyword "JOIN" indicates that you are using an ANSI JOIN.You can try this:select *from employees e inner join departments don e.id = d.deptidwhere d.name = 'SALES' or e.firstname = 'DAVE'The old way (not using Ansi) would look like this:select * from employees e, departments dWHERE e.ID = d.DEPTIDAnd d.name = 'SALES'OR e.firstname = 'DAVE' |
|
|
|
dve
Starting Member
19 Posts |
Posted - 2005-12-08 : 10:23:24
|
| The problem is that your query doesn't include 'dave' since it's not joined by the JOIN I guess my only option is to LEFT JOIN? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-12-08 : 10:28:28
|
quote:
Originally posted by dve
The problem is that your query doesn't include 'dave' since it's not joined by the JOIN I guess my only option is to LEFT JOIN?
Yes, that's what a LEFT OUTER JOIN is for. |
|
|
|
|
|
|
Help with a select; OR with ANSI joins ???