Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SQL Find Customers who have not paid 100

Posted on 2014-09-23
Medium Priority
122 Views
Hi
I have a Customers table and a Payments table, shown in that order below
What joined query would I use to find the CustID of those Customers who have
not paid an AMOUNT of 100. The [LINK ID] is the same as the CustID in CUSTOMERS

0
Question by:Murray Brown
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 70

Expert Comment

ID: 40339265
SELECT c.*
FROM (
FROM Payments
HAVING MAX(CASE WHEN Amount = 100.00 THEN 1 ELSE 0 END) = 0
) AS p
INNER JOIN Customers c ON
0

LVL 66

Expert Comment

ID: 40339269
btw, you might have the image labels off, as the set labeled 'Customers' looks like a payments table, and vise versa

Looks like Scott answered it above.

Also, do yourself a favor and lose the spaces in the First Name, Last Name columns, as it forces T-SQL to delineate it using square brackets [ ], which is one thing to potentially forget and throw an error.
0

LVL 24

Expert Comment

ID: 40339270
I assume that these two tables are the wrong way round - the first one should be payments and the customers.

``````Select C.CustID, C.[First Name], C.[Last Name], Sum(Amount) as TotalAmount
From Customers C
LEFT JOIN Payments P
GROUP BY C.CustID, C.[First Name], C.[Last Name]
HAVING Sum(Amount) < 100 OR P.[LINK ID] IS NULL
``````
0

Author Comment

ID: 40339315
Hi
Yes the two tables are the wrong way around. I was looking for Customers who have not paid the amount of 100 once(not in total).
0

LVL 24

Accepted Solution

Phillip Burton earned 2000 total points
ID: 40339324
``````Select C.CustID, C.[First Name], C.[Last Name], Max(Amount) as MaxAmount
From Customers C
LEFT JOIN Payments P
WHERE Max(Amount) < 100 OR P.[LINK ID] IS NULL
GROUP BY C.CustID, C.[First Name], C.[Last Name]
``````
0

Author Closing Comment

ID: 40339784
Thanks for the help
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
###### Suggested Courses
Course of the Month6 days, 14 hours left to enroll