Linking two tables to find non orders

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

General SQL Server Forums

New to SQL Server Programming

Linking two tables to find non orders

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-03-14 : 06:57:18

Hi

I have two tables. Customer and Orders.

Both are linked by CustomerID

If I wanted to find out customer details from the customer table to find out who has not placed an order do you know what the SQL Script would be like? Trying to link but no luck so far.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 07:05:15

SELECT custid FROM customers
EXCEPT
SELECT custid FROM Orders

--
Chandu

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-14 : 07:51:51

and also...........

select c.customername from customer c
inner join orders o
on c.custid=o.custid
where c.custid not in(select custid from orders)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 07:59:08

--One more alternate
By this approach you can select all columns in both tables
SELECT c.*
FROM customers c
LEFT JOIN Orders o ON o.custid = c.custid
WHERE o.custid IS NULL

Note: As per ahmeds08's solution, If you have NULL value for custid in Orders Table, then it won't give any single record (even we have non-order customers)......
For that solution is NOT IN ( SELECT Custid FROM Orders WHERE custid IS NOT NULL)

--
Chandu

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-14 : 08:04:56

quote:
Originally posted by bandi

--One more alternate
By this approach you can select all columns in both tables
SELECT c.*
FROM customers c
LEFT JOIN Orders o ON o.custid = c.custid
WHERE o.custid IS NULL

Note: As per ahmeds08's solution, If you have NULL value for custid in Orders Table, then it won't give any single record (even we have non-order customers)......
For that solution is NOT IN ( SELECT Custid FROM Orders WHERE custid IS NOT NULL)

--
Chandu

Thanks chandu.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 08:06:53

quote:
Originally posted by ahmeds08

quote:
Originally posted by bandi

--One more alternate
By this approach you can select all columns in both tables
SELECT c.*
FROM customers c
LEFT JOIN Orders o ON o.custid = c.custid
WHERE o.custid IS NULL

Note: As per ahmeds08's solution, If you have NULL value for custid in Orders Table, then it won't give any single record (even we have non-order customers)......
For that solution is NOT IN ( SELECT Custid FROM Orders WHERE custid IS NOT NULL)

--
Chandu

Thanks chandu.

Welcome......

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources