Running this query on 2 tables

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

SQL Server Development (2000)

Running this query on 2 tables

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-09-06 : 10:52:37

I have following data

Orders
orderid= 1, username=u1, companyid=1
orderid= 2, username=u2, companyid=1

OrderLine
orderLineID=1, orderID=1, confirmed=0
orderLineID=2, orderID=2, confirmed=0
orderLineID=3, orderID=2, confirmed=0

Now I want to get all the orders WHERE orderlineid.confirmed=0

So I have this query:
SELECT * FROM Orders join OrderLine
on Orders.OrderID = OrderLine.OrderID

where
OrderLine.OrderConfirmed = 0
and Orders.CompanyID = @CompanyID

However, with this query, I am getting:
OrderId
1
2
2

But I should only get orderID 1 & 2

Any idea how I can make this work.

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-06 : 10:57:57

Because OrderLine table has two entries for OrderId 2

Use Distinct *

SELECT Distinct * FROM Orders join OrderLine
on Orders.OrderID = OrderLine.OrderID
where OrderLine.OrderConfirmed = 0
and Orders.CompanyID = @CompanyID

Madhivanan

Failing to plan is Planning to fail