RE: Another how can I do this in SQL ?

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

How about

SELECT * FROM CUSMST CM WHERE CM.PHONE# NOT IN (SELECT CD.PHONE# FROM
CUSDTL  CD JOIN CODMST CC ON CD.CHGCOD = CC.CHGCOD WHERE CC.GRPCOD='TONE')

At 04:10 PM 4/2/02 -0500, you wrote:
>Phone #'s that do not have a matching charge code/group code (there will
>be many charge detail records for each phone #)
>
>For example I would want all the phone #'s that do not have a specific
>charge code or a group code (i.e. all customers who do not have touch
>tone service, this is about 15 different charge codes, Group code=TONE)
>
>Again easy to do if I want all those with tone, still easy but takes 2
>passes want a "single" pass approach
>
>Mark Allen
>I.S. Manager
>Wilkes Telephone & Electric
>A Dycom Company
>Phone: (706) 678-9565
>Email: allenmark@nu-z.net
>http://www.nu-z.net
>
>
>
>
>:-----Original Message-----
>:From: midrange-l-admin@midrange.com
>:[mailto:midrange-l-admin@midrange.com] On Behalf Of Vernon Hamberg
>:Sent: Tuesday, April 02, 2002 3:50 PM
>:To: midrange-l@midrange.com
>:Subject: Re: Another how can I do this in SQL ?
>:
>:
>:Can you say more? Do you want "charge code/group code that do
>:not have a matching phone number"? or "phone numbers that do
>:not have a matching charge code/group code"?
>:
>:At 03:03 PM 4/2/02 -0500, you wrote:
>:>Customer Master File
>:>
>:>Phone #
>:>
>:>Customer Charge Detail file
>:>
>:>Phone #
>:>Charge code
>:>
>:>Charge code master
>:>
>:>Charge code
>:>Group code
>:>
>:>Easy enough to find all customers who have a specific charge
>:code or a
>:>specific group (i.e. we may have 3 charge codes, one for
>:employees, one
>:>for business, one for residence) that all signify touch tone service,
>:>so search using group code = TONE
>:>
>:>
>:>Up till now I have been doing it in multiple steps in queries (i.e.
>:>step 1 - find all customers with the charge code/group code -
>:output to
>:>file
>:>
>:>Step 2 compare cust master phone # to output file in step 1
>:list/output
>:>all non matches
>:>
>:>How do I convert this to a single SQL statement, I've looked
>:and looked
>:>and I JUST DON'T SEE IT!!!!
>:>
>:>Mark Allen
>:>I.S. Manager
>:>Wilkes Telephone & Electric
>:>A Dycom Company
>:>Phone: (706) 678-9565
>:>Email: allenmark@nu-z.net
>:>http://www.nu-z.net
>:>
>:>
>:>
>:>
>:>
>:>_______________________________________________
>:>This is the Midrange Systems Technical Discussion
>:(MIDRANGE-L) mailing
>:>list To post a message email: MIDRANGE-L@midrange.com To subscribe,
>:>unsubscribe, or change list options,
>:>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>:>or email: MIDRANGE-L-request@midrange.com
>:>Before posting, please take a moment to review the archives
>:>at http://archive.midrange.com/midrange-l.
>:
>:_______________________________________________
>:This is the Midrange Systems Technical Discussion (MIDRANGE-L)
>:mailing list To post a message email: MIDRANGE-L@midrange.com
>:To subscribe, unsubscribe, or change list options,
>:visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>:or email: MIDRANGE-L-request@midrange.com
>:Before posting, please take a moment to review the archives
>:at http://archive.midrange.com/midrange-l.
>:
>:
>
>
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@midrange.com
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>or email: MIDRANGE-L-request@midrange.com
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.