qry help - joins - 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 2005 Forums

Transact-SQL (2005)

qry help - joins

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-02-27 : 03:19:13

how can i get all records from customers where customerid is not in select customerid from orders

(returning all customers that don't have an order record)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-02-27 : 03:41:01

[code]SELECT c.*
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL[/code]
Having said that, you were almost there in pseudo-code in your question. What have you tried so far?

Mark

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-02-27 : 03:55:24

quote:
Originally posted by esthera

how can i get all records from customers where customerid is not in select customerid from orders

(returning all customers that don't have an order record)

even u can:

Select * From Customers Where OrderId Not In (Select OrderId From Orders)

thanks,
Mahesh

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-02-27 : 04:03:08

the not in is pulling up 0 records

the

SELECT c.*
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL

pulls up more records then there are in customers

also customerid is a varchar field and not an id (i know that's confusing but there is another id field in each table) and customerid is not unique in both tables neccessarily -- does that change this? please help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 04:25:47

Yes, this is because some customers have more than one order.

Add a DISTINCT or try this

SELECT c.*
FROM dbo.Customers AS c
WHERE NOT EXISTS (SELECT NULL FROM dbo.Orders AS oc.CustomerID = o.CustomerID)

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 04:29:25

[code]
SELECT distinct c.*
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL
[/code]

OR

[code]
SELECT c.*
FROM dbo.Customers AS c
LEFT JOIN (
select CustomerID
from dbo.Orders
group by CustomerID
) AS o
ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL
[/code]

OR

[code]
SELECT c.*
FROM dbo.Customers AS c
WHERE NOT EXISTS (select * from dbo.Orders o where o.CustomerID = c.CustomerID)
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 05:26:28

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources