Query Help Please! - SQL Server Forums

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

Transact-SQL (2000)

Query Help Please!

Author

Topic

neo302
Starting Member

30 Posts

Posted - 2006-07-30 : 18:06:26

Hello, please help.

Say I have the three tables below.

Customer
CustID
CustFname

Order
OrderID
CustID
EmpID

Emp
EmpID
EmpName

How do I Query this so I could specify the custid
and get CustFName, OrderID and EmpName.
EmpID is not required in an Order. If there is no
EmpID in the order, just return null.

When I do a left outer join, it returns all of the
customers and orders and it ignores the custid I
specified in the query. i.e.- I'd get
Custid OrderID EmpFName
1 1 null
2 2 null

even though I'd only want the first record.

Thank you!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-30 : 19:35:06

[code]select custid, orderid, empname
from customer c inner join order o
on c.custid = o.custid
left join emp e
on o.empid = e.empid[/code]

neo302
Starting Member

30 Posts

Posted - 2006-07-30 : 19:40:36

quote:

Originally posted by khtan

select custid, orderid, empname
from   customer c inner join order o
       on     c.custid = o.custid
       left join emp e
       on     o.empid  = e.empid

Thanks for the reply! This is what I did, but also
I specified where c.custid = 1 on the end but it ignores that and gives me all the customers. I need only the customer I specify.

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-30 : 21:18:07

can you post your table DDL and some sample data ?

neo302
Starting Member

30 Posts

Posted - 2006-07-31 : 12:49:35

quote:
Originally posted by khtan

can you post your table DDL and some sample data ?

KH

My question would be, can you do a left outer join and not return all rows from the left table, but only ones you want to see from info specified in the where clause?

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-31 : 19:00:12

"My question would be, can you do a left outer join and not return all rows from the left table, but only ones you want to see from info specified in the where clause?"
Yes you can. Post your table DDL, same sample data and result that you want if you are still having problem with the query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources