santoshmotwani
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.
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.
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.memot ype))) <> 'DEBT' and cm.cnum = c.customerno
select distinct(cnum) from customermemo cm, customer c
where upper(ltrim(rtrim(cm.memot
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is this a duplicate of https://www.experts-exchange.com/questions/26627994/Sql-query-select-statement.html
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.
ASKER
i apologize for posting it twice .
ASKER
Thnx !!! that helped
select distinct(cnum) from customermemo cm, customer c
where ltrim(rtrim(cm.memotype)) <> 'DEBT' and cm.cnum = c.customerno