question regarding Not equal - 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 2008 Forums

Transact-SQL (2008)

question regarding Not equal

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-03-13 : 10:28:30

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

52326 Posts

Posted - 2013-03-13 : 10:33:41

[code]
SELECT *
FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM transactions WHERE cust_no = c.cust_no)
[/code]

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

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-03-13 : 11:15:34

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])

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-03-13 : 11:16:38

This does not show me anything I want to see the customers that do not have a transaction. With this I get no results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 11:19:16

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/

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-03-13 : 11:23:13

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

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-03-13 : 11:24:32

OIC could you explain why I am interested in why I would not want to see those tables.

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-03-13 : 11:28:02

Thank you btw this helped

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-03-13 : 11:33:22

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])))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 11:47:11

[code]
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])
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources