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?
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
All Courses

From novice to tech pro — start learning today.