• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

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.
0
santoshmotwani
Asked:
santoshmotwani
  • 2
  • 2
  • 2
  • +2
1 Solution
 
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
 
Ephraim WangoyaCommented:
select distinct(cnum)
from customermemo
inner join customer on customer.customerno = customermemo.cnum
where ltrim(rtrim(memotype)) <> 'debt
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now