SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 question regarding Not equal
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

113 Posts

Posted - 03/13/2013 :  10:28:30  Show Profile  Reply with Quote
I have 2 tables one that houses all of our customer data. Call this table customer. And the fields I want used are cust-no, name, address, city, state, zip. Then I have a table that holds all transactions. Call this table Transaction. Now the link between both tables is cust-no. What I am trying to acomplish is I want all customers from the customer table that do not have a transaction.

So I was thinking like this.

Select Case WHEN (customer.[cust-no] <> transaction.[cust-no]) THEN (customer.[cust-no]) End AS Customer

This is not working shows all null values.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/13/2013 :  10:33:41  Show Profile  Reply with Quote

SELECT *
FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM transactions WHERE cust_no = c.cust_no)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

hbadministrator
Posting Yak Master

113 Posts

Posted - 03/13/2013 :  11:15:34  Show Profile  Reply with Quote
ok that does not seem to work here is the actual code.

SELECT CASE WHEN (customer.[cust-no] <> [ar-open-item].[Cust-no]) THEN (customer.[cust-no]) END AS Customer, customer.name, customer.Address, customer.City,
customer.St, customer.[Zip-code], customer.Telephone
FROM customer INNER JOIN
[ar-open-item] ON customer.[Cust-no] = [ar-open-item].[Cust-no]

You are saying I should

Select customer.[cust-no],customer.name, customer.Address, customer.City, customer.St, customer.[Zip-code], customer.Telephone
FROM customer INNER JOIN
[ar-open-item] ON customer.[Cust-no] = [ar-open-item].[Cust-no]
Where NOT EXISTS (SELECT 1 FROM [ar-open-item] WHERE [ar-open-item].[cust-no] = customer.[cust-no])
Go to Top of Page

hbadministrator
Posting Yak Master

113 Posts

Posted - 03/13/2013 :  11:16:38  Show Profile  Reply with Quote
This does not show me anything I want to see the customers that do not have a transaction. With this I get no results.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/13/2013 :  11:19:16  Show Profile  Reply with Quote
thats because you're not using it as suggested


Select customer.[cust-no],customer.name, customer.Address, customer.City, customer.St, customer.[Zip-code], customer.Telephone 
FROM customer INNER JOIN
[ar-open-item] ON customer.[Cust-no] = [ar-open-item].[Cust-no]
Where NOT EXISTS (SELECT 1 FROM [ar-open-item] WHERE [ar-open-item].[cust-no] = customer.[cust-no])


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

hbadministrator
Posting Yak Master

113 Posts

Posted - 03/13/2013 :  11:23:13  Show Profile  Reply with Quote
Example data

Customer table
Cust-no Address City St Zip-code Telephone
3241234 123 lala sure PA 17022 7172312442
3241233 133 lala surer PA 17022 7172312444
3241231 134 lala surers PA 17022 7172312441


Ar-open-item Table (transaction table)
Cust-no Transaction cost
3241234 123456 12.00
3241233 234566 14.42

I want to see the customer that did not have a transaction. This guys info below so then I can say hey you never purchased from us here is a coupon. Something of that nature at a later date.

3241231 134 lala surers PA 17022 7172312441
Go to Top of Page

hbadministrator
Posting Yak Master

113 Posts

Posted - 03/13/2013 :  11:24:32  Show Profile  Reply with Quote
OIC could you explain why I am interested in why I would not want to see those tables.
Go to Top of Page

hbadministrator
Posting Yak Master

113 Posts

Posted - 03/13/2013 :  11:28:02  Show Profile  Reply with Quote
Thank you btw this helped
Go to Top of Page

hbadministrator
Posting Yak Master

113 Posts

Posted - 03/13/2013 :  11:33:22  Show Profile  Reply with Quote
ok now another level of complexity. I have a History table I need to check as well. That table name is [ar-hi-item]

SELECT [Cust-no], name, Address, City, St, [Zip-code], Telephone
FROM customer
WHERE (NOT EXISTS
(SELECT 1 AS Expr1 FROM [ar-open-item] OR [ar-hi-item] WHERE ([Cust-no] = customer.[Cust-no])))

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/13/2013 :  11:47:11  Show Profile  Reply with Quote

SELECT [Cust-no], name, Address, City, St, [Zip-code], Telephone
FROM customer
WHERE NOT EXISTS
(SELECT 1  FROM [ar-open-item] WHERE [Cust-no] = customer.[Cust-no])
AND NOT EXISTS (SELECT 1 FROM [ar-hi-item] WHERE ([Cust-no] = customer.[Cust-no])


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000