Message-Id: <10718.125280@fatcity.com> From: Dennis Taylor Date: Fri, 22 Dec 2000 11:47:29 -0800 Subject: Re: sql problem (solved) Well, I got enough prodding from list'ers that I was able to come up with something. Here's an actual sql (using the real table and column names -- sorry). select issuer_id,if_name,if_value from issuerflags where issuer_id = 1 and if_group = 'VARIOUSFLAGS' UNION (select issuer_id,code_key "IF_NAME",2 "IF_VALUE" from (select 1 "ISSUER_ID",CODE_KEY,'?' "IF_VALUE" from codes where code_type = 'VARIOUSFLAGS') MINUS (SELECT ISSUER_ID,if_name "CODE_KEY",2 "IF_VALUE" from issuerflags where issuer_id = 1)) / codes is 'T1', and issuerflags is 'T2' in my original question. This query gives: ISSUER_ID IF_NAME IF_VALUE --------- ---------------- --------- 1 BORES 0 1 ENC 1 1 GSTX 1 1 NCI 2 1 PAD 1 1 VERI 0 Whereas "select issuer_id,if_name,if_value from issuerflags where issuer_id = 1 and if_group = 'VARIOUSFLAGS' " gives ISSUER_ID IF_NAME IF_VALUE --------- ---------------- --------- 1 GSTX 1 1 BORES 0 1 ENC 1 1 PAD 1 1 VERI 0 For those who may be wondering why I'm being so obsessive about this, it's for a Delphi app. Giving it the full list of possible feecodes using an sql statement simplifies the logic of the app, since I now don't have to worry about validating and adding fee codes on the form. --- Dennis Taylor --- Don't worry about people stealing your ideas. If your ideas are any good, you'll have to ram them down people's throats.