Link to home
Start Free TrialLog in
Avatar of santoshmotwani
santoshmotwaniFlag for Australia

asked on

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.
Avatar of lcohan
lcohan
Flag of Canada image

try the updated below:

select distinct(cnum) from customermemo cm, customer c
where ltrim(rtrim(cm.memotype)) <> 'DEBT' and cm.cnum = c.customerno
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
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes.  What is great is when you see the same solution copied and pasted in both questions.  Can you say wishful thinking?
I have posted a message in CS.  More than likely points will be halved in both questions.
Avatar of santoshmotwani

ASKER

i apologize for posting it twice .
Thnx !!! that helped