Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

120 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
52326 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

120 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

120 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
52326 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

120 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

120 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

120 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

120 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
52326 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  
 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