Sql query selct statement

Hi,

I have a table with customer number and memo types left on their account.
e.g.

---------------------------
Customermemo
---------------------------
cnum     memotype
a1               debt
b1                good
a1                paid
b1                debt
b3                new
b2                leaving
b2                left
b3                paid

I have another table with only customer number
----------------
Customer
------------------
customerno
a1
b1
a2
b2
b3
a3

Now i am trying to get distinct customer number without memotype debt ( that customer number should be in both tables)

here is a query

select distinct(cnum) from customermemo, customer where
memotype <> 'DEBT'
and
cnum = customerno

But still i kept on getting a1 in the output though i do not want anything with the memo type DEBT.
LVL 16
santoshmotwaniAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ephraim WangoyaConnect With a Mentor Commented:
select distinct(cnum)
from customermemo
inner join customer on customer.customerno = customermemo.cnum
where ltrim(rtrim(memotype)) <> 'debt
0
 
lcohanDatabase AnalystCommented:
try the updated below:

select distinct(cnum) from customermemo cm, customer c
where ltrim(rtrim(cm.memotype)) <> 'DEBT' and cm.cnum = c.customerno
0
 
lcohanDatabase AnalystCommented:
even better as you may have a case sensitive database and "debt" <> "DEBT"

select distinct(cnum) from customermemo cm, customer c
where upper(ltrim(rtrim(cm.memotype))) <> 'DEBT' and cm.cnum = c.customerno
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
SharathData EngineerCommented:
0
 
Anthony PerkinsCommented:
Yes.  What is great is when you see the same solution copied and pasted in both questions.  Can you say wishful thinking?
0
 
Anthony PerkinsCommented:
I have posted a message in CS.  More than likely points will be halved in both questions.
0
 
santoshmotwaniAuthor Commented:
i apologize for posting it twice .
0
 
santoshmotwaniAuthor Commented:
Thnx !!! that helped
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.