Link to home
Start Free TrialLog in
Avatar of wkolasa
wkolasaFlag for United States of America

asked on

SQL, Coldfusion - Exclude all rows with specfic value if one of those rows contains...

Hi,

I have a SQL query (below) in which I want to exclude rows based on the following conditions:

If any row with column SCSTULINK= 800 has the value NULL in column SCEXCLATTEND, then exclude ALL rows with SCSTULINK = 800 in results.  

Basically, the way I have the SQL query setup now, it's excluding results that have a NULL value in the scexclattend field, but I need it to exclude all results if one of the results fulfills the parameters stated above (which it's not doing now).

Oh, and I'm using an Oracle DB, Coldfusion MX 7.

Thank you very much.

SELECT   Distinct   
            SCTCHNUM,
            a.SCSCHOOLNUM,
            a.sctchnum,
            b.SCEXCLATTEND
            c.SCSTULINK
 
FROM        SASI.PS_SCATCH a,
            SASI.PS_SCAMST b,
            SASI.PS_SCACLS c
 
WHERE       a.SCSCHOOLNUM = 601
AND         c.scstulink = 873
AND         a.SCSCHOOLNUM = b.SCSCHOOLNUM
AND         a.sctchnum = 179
AND         a.SCTCHNUM = b.SCTCHNUM
AND         b.SCSCHOOLNUM = c.SCSCHOOLNUM
AND         b.SCEXCLATTEND is not null
AND         c.SCSCHOOLNUM = d.SCSCHOOLNUM
AND         c.SCSTULINK = d.SCSTULINK
 
DATA RESULTS
 
SCSCHOOLNUM		SCSTULINK	SCTCHNUM	SCEXCLATTEND
601				800			11			Y
601				800			11			NULL
601				780			11			Y
 
So ALL rows containing SCSTULINK = 800 need to be excluded b/c 1 of the rows contains NULL in the SCEXCLATTEND field

Open in new window

Avatar of reb73
reb73
Flag of Ireland image

The results listed by you does not reflect the query accurately. It would help if you list the whole query here as the table declaration for alias d is not included above.

Avatar of wkolasa

ASKER

My apologies... use this instead:
SELECT   Distinct   
            SCSCHOOLNUM,
            SCTCHNUM,
            SCEXCLATTEND,
            SCSTULINK
 
FROM        SCATCH
 
WHERE       SCSCHOOLNUM = 601
AND         scstulink = 873
AND         sctchnum = 179
 
 
DATA RESULTS
 
SCSCHOOLNUM		SCSTULINK	SCTCHNUM	SCEXCLATTEND
601				800			11			Y
601				800			11			NULL
601				780			11			Y
 
So ALL rows containing SCSTULINK = 800 need to be excluded b/c 1 of the rows contains NULL in the SCEXCLATTEND field

Open in new window

Avatar of wkolasa

ASKER

This is laid out better:
SELECT   Distinct   
            SCSCHOOLNUM,
            SCTCHNUM,
            SCEXCLATTEND,
            SCSTULINK
 
FROM        SCATCH
 
WHERE       SCSCHOOLNUM = 601
AND         scstulink = 873
AND         sctchnum = 179
 
 
DATA RESULTS
 
SCSCHOOLNUM		SCSTULINK	SCTCHNUM	SCEXCLATTEND
601			800		11		Y
601			800		11		NULL
601			780		11		Y
 
So ALL rows containing SCSTULINK = 800 need to be excluded b/c 1 of the rows contains NULL in the SCEXCLATTEND field

Open in new window

Avatar of Mark Geerlings
I think you need a sub-query to first find the values of SCSTULINK that have a NULL in column: SCEXCLATTEND, then exclude those SCSTULINK values from the main query.  Or, do you only exclude them if SCSTULINK = 800?

I have no idea though what limitations (or features?) ColdFusion adds to your ability to write SQL statements.  Does it allow you (or help you) to include a sub-query if you wish?
Avatar of wkolasa

ASKER

Yes, I can sub-query with CF.  It's an interesting idea... I'm going to give it some thought.  

To answer other question:  No, not only it SCSTULINK = 800.   There can be a bunch of rows with the same SCSTULINK, and 0, 1, or more times where that SCSTULINK (let's say it's 800 for example) has the NULL value at SCEXCLATTEND.  If that happens, then I need to exclude ALL SCSTULINK that = 800, even if they don't have a NULL value on SCEXCLATTEND
You can do a query of queries in coldfusion. So using your initial SQL statement to put everything into a recordset. Then check this RS to see if SCSTULINK = 800 and the value is NULL in column SCEXCLATTEND (simple cfif while looping through the original query). If it finds a record which means the IF statement, then break out and do a query of queries to exclude all records with 800 in SCSTULINK .
SOLUTION
Avatar of reb73
reb73
Flag of Ireland 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
SOLUTION
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
SOLUTION
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
I just noticed Tomarse111's suggestions,  Yes, that may work in ColdFusion, but from a performance perspective it is *MUCH* better if the database can do the filtering directly and only return the desired records to the application server.  Otherwise the DB server, network and application server are burdened with processing lots of rows that will be filtered out later.  (I'm assuming that ColdFusion runs on a server other than the DB server, but I realize I'm making a guess about that.)
ASKER CERTIFIED SOLUTION
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
Avatar of wkolasa

ASKER

Thank you everyone.  I'm posting a mega-easy one in just about a minute if anyone wants a quick extra 500 points.  Thank you again!