Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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