| Author |
 Topic  |
|
|
hbadministrator
Yak Posting Veteran
63 Posts |
 Posted - 03/13/2013 : 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
India
47099 Posts |
Posted - 03/13/2013 : 10:33:41
|
SELECT *
FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM transactions WHERE cust_no = c.cust_no)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
hbadministrator
Yak Posting Veteran
63 Posts |
Posted - 03/13/2013 : 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
Yak Posting Veteran
63 Posts |
Posted - 03/13/2013 : 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
India
47099 Posts |
Posted - 03/13/2013 : 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
Yak Posting Veteran
63 Posts |
Posted - 03/13/2013 : 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
Yak Posting Veteran
63 Posts |
Posted - 03/13/2013 : 11:24:32
|
| OIC could you explain why I am interested in why I would not want to see those tables. |
|
|
|
hbadministrator
Yak Posting Veteran
63 Posts |
Posted - 03/13/2013 : 11:28:02
|
Thank you btw this helped
|
|
|
|
hbadministrator
Yak Posting Veteran
63 Posts |
Posted - 03/13/2013 : 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
India
47099 Posts |
Posted - 03/13/2013 : 11:47:11
|
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/
|
|
|
| |
Topic |
|
question regarding Not equal
