Only show rows with same filename that meets criteria.

Hello Experts,

The SQL output would be only shows the Filenames with all the rows  that meets the criteria below:

1) All [Column 4] that has ACH and NON in  [Column 0] that is "DD"
2) If  [Column 2] shows "Altire" in  [Column 0] that is "HH1" and BOP in  [Column 0] that is "DD"
3) If  [Column 4] shows "CHK" and [Column 2] that is greater than "500" in  [Column 0] and that is "DD"

See attached example.

My query below is not working.

SELECT [UniqueID]
      ,[Column 0]
      ,[Column 1]
      ,[Column 2]
      ,[Column 3]
      ,[Column 4]
 
      ,[Filename]
      ,[RowID]


  FROM dbo.SQLResults

  WHERE [FILENAME] NOT IN (SELECT [FILENAME] FROM (SELECT * FROM dbo.SQLResults 
                           WHERE [Column 4] IN ( 'ACH', 'NON' )
                             OR ([Column 4] = 'CHK'  AND [Column 2] >= '500')
                             OR ([Column 4] = 'BOP' AND [Column 2] = 'Altire'))

Open in new window


Please help.

Thank you for all your help.
OnlyShowFilenameWithCriteriaINWhere.xlsx
Queennie LAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Please reread what you posted and then rewrite more clearly the "criteria" you want to use more clearly and accurately.

For example

and BOP in  [Column 0] that is "DD"

doesn't make sense or isn't clear as worded.

And I don't see any references to [Column 0] in your WHERE clause, even though you mention it in the criteria, why is that?


»bp
0
Queennie LAuthor Commented:
@Bill Prew:

That is "BOP" in [Column 4] in [Column 0] under "DD"

Thank you.
0
Queennie LAuthor Commented:
Any help please?

Thank you.
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Queennie LAuthor Commented:
I tried this WHERE CLAUSE:


WHERE  EXISTS  (
    SELECT 1  FROM (SELECT * FROM dbo.SQLResults
                           WHERE [Column 4] IN ( 'ACH', 'NON' )
                             OR ([Column 4] = 'CHK'  AND [Column 2] >= '500')
                             OR ([Column 4] = 'BOP' AND [Column 2] = 'Altire'))
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, I still can't write you a query if I don't understand what you want.  Please look at the below and adjust in the same fashion I have written to be accurate.

It sounds like you want to see records in the results when any of three situations exist, correct?

From your first post and later comment here is what I am guessing at.  Please check it and adjust so that we can build a working query.

It's still not clear to me where 'HH1' and 'BOP' should be fit in, or if there is a 4th case, so I left those out for now.

Case 1
    - [Column 0] must equal 'DD'
    - [Column 4] must be either 'ACH' or 'NON'
Case 2
    - [Column 0] must equal 'DD'
    - [Column 2] must equal 'Altire'
Case 3
    - [Column 0] must equal 'DD'
    - [Column 4] must equal 'CHK'
    - [Column 2] must be greater than '500'


»bp
0
Queennie LAuthor Commented:
@Bill Prew:

"Case 1
    - [Column 0] must equal 'DD'
    - [Column 4] must be either 'ACH' or 'NON'"

Case 1: Show all rows with the same filename if above condition met. This case is correct.


"Case 2
    - [Column 0] must equal 'DD'
    - [Column 2] must equal 'Altire'"
Case 2: Show all rows with the same filename IF [Column 0] = 'DD' and [Column 4] = 'BOP'
                                                                             AND [Column 0] = 'HH1' and [Column 2] = 'Altire'

Case 3
    - [Column 0] must equal 'DD'
    - [Column 4] must equal 'CHK'
    - [Column 2] must be greater than '500'
0
Bill PrewIT / Software Engineering ConsultantCommented:
Case 2: Show all rows with the same filename IF [Column 0] = 'DD' and [Column 4] = 'BOP'
                                                                             AND [Column 0] = 'HH1' and [Column 2] = 'Altire'
This one I don't understand.  As written you require [Column 0] to be equal to two different values, which can never be true.


»bp
0
Queennie LAuthor Commented:
@Bill Prew:

"Case 1
    - [Column 0] must equal 'DD'
    - [Column 4] must be either 'ACH' or 'NON'"

Case 1: Show all rows with the same filename if above condition met. This case is correct.
       



"Case 2
    - [Column 0] must equal 'DD'
    - [Column 2] must equal 'Altire'"
Case 2: Show all rows with the same filename IF ([Column 0] = 'DD' and [Column 4] = 'BOP')
                                                                             AND ([Column 0] = 'HH1' and [Column 2] = 'Altire')

Case 3
    - [Column 0] must equal 'DD'
    - [Column 4] must equal 'CHK'
    - [Column 2] must be greater than '500'
0
Queennie LAuthor Commented:
@Bill Prew:

I don't think it is possible.
0
Bill PrewIT / Software Engineering ConsultantCommented:
You seem to have posted the same thing again, which has the same contradiction I mentioned.


»bp
0
Queennie LAuthor Commented:
@Bill Prew:

I thought my question does not make any sense. I accidentally deleted it. Sorry.
0
Bill PrewIT / Software Engineering ConsultantCommented:
No problem, it can be hard to explain exactly what you are after sometimes.

Can you try and explain a bit more what criteria you are trying to get with this one:

Case 2: Show all rows with the same filename IF ([Column 0] = 'DD' and [Column 4] = 'BOP')
                                                                             AND ([Column 0] = 'HH1' and [Column 2] = 'Altire')



»bp
0
Mark WillsTopic AdvisorCommented:
Anything is possible :)


Psuedo-Code - need to test and check syntax...
; with cte_filename as
(select filename from dbo.SQLResults 
                           WHERE ([Column 4] IN ( 'ACH', 'NON' ) and [column 0] = 'DD')
                             OR ([Column 4] = 'CHK'  AND [Column 2] >= '500' and [column 0] = 'DD' )
                             OR ([Column 0] = 'DD' AND [Column 4] = 'BOP')
                             OR ([Column 0] = 'HH1' AND [Column 2] = 'Altire')
) select s.* 
  from dbo.SQLResults S
 where S.filename in (select filename from cte_filename)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
How about this:
SELECT [UniqueID]
  ,[Column 0]
  ,[Column 1]
  ,[Column 2]
  ,[Column 3]
  ,[Column 4]
  ,[Filename]
  ,[RowID]


FROM dbo.SQLResults

WHERE (
           [Column 4] IN ( 'ACH', 'NON' )
       OR ([Column 4] = 'CHK'  AND [Column 2] >= '500')
       OR ([Column 4] = 'BOP' AND [Column 2] = 'Altire')
      )

Open in new window

0
Queennie LAuthor Commented:
I hope this example will clarify.

I don't think this is possible.

See attached.

@MarkWillis:

I will test it and get back with you.

@AikiMark:

I don't think that query is working. I tried that earlier but it only shows certain based on the criteria but not all rows.

Thank you.
OnlyShowFilenameWithCriteriaINWhere.xlsx
0
Mark WillsTopic AdvisorCommented:
correction

instead of :
                             OR ([Column 0] = 'DD' AND [Column 4] = 'BOP')
                             OR ([Column 0] = 'HH1' AND [Column 2] = 'Altire')

could be :
                             OR ([column 2] = 'Altire' and [column 0] in ('BOP','DD'))


Hmmmm... 2nd thoughts... maybe not.....
0
Mark WillsTopic AdvisorCommented:
case 2 is a problem...

the only 'BOP' I can see is column 4 and has 'DD' in column 0
the only 'altire i can see has 'HH1' in column 0

which means  : column 2 = 'altire' + column 0 = 'HH1' or  column 0 ='DD' + column 4 = 'BOP'

back to my original...
0
aikimarkCommented:
I can get 11111,2222,3333,7777 filenames with this Access query.  However, your criteria fails 44444.  There is no row in the 44444 set that matches  [Column 4] = 'BOP' AND [Column 2] = 'Altire'
There are two separate rows matching one of these two conditions, but no single row that matches both.
SELECT [UniqueID]
  ,[Column 0]
  ,[Column 1]
  ,[Column 2]
  ,[Column 3]
  ,[Column 4]
  ,[Filename]
  ,[RowID]

  FROM Q_range

WHERE
  Exists (
    Select *
    From Q_Range as I
    Where
        [Q_range].Filename = I.Filename And
        I.[Column 4] IN ( 'ACH', 'NON' )
  )
   OR Exists (
    Select *
    From Q_Range as I
    Where 
        [Q_range].Filename = I.Filename And
        I.[Column 4] = 'CHK'  AND I.[Column 2] >= '500'
  )
  OR Exists (
    Select *
    From Q_Range as I
    Where 
        [Q_range].Filename = I.Filename And
        I.[Column 4] = 'BOP' AND I.[Column 2] = 'Altire'
  )

Open in new window

0
Queennie LAuthor Commented:
@MarkWillis:

Your SQL query is working but it is still showing CHK >='500' all the amount, it did not limit only to greater than 500. I tried to convert it to this, CONVERT(NVARCHAR(36), [Column 2]) > CAST(500 AS NUMERIC(35,2)) but there is an Error converting data type nvarchar to numeric.

I have to convert it because all values in [Column 2] are different types.

Thank you again.
0
Mark WillsTopic AdvisorCommented:
OK,

What version of SQL ? If 2012 or more recent, you can use

TRY_CAST([COLUMN 2] as MONEY) > = 500

otherwise you will need to do something like

( CASE WHEN ISNUMERIC([Column 2]) = 1 THEN CAST([Column 2] as DECIMAL(35,2)) ELSE 0.00 END ) >= 500

but isnumeric() is not fullproof.... it can regard some things as numeric that might cause the CAST() to fail.
1
Queennie LAuthor Commented:
@MarkWillis:

OMG! WOW! It is working.

The original output query  is total of 62,521 and with all the criteria meets the output query is total of 53,473.

Thank you again for your help.

I really appreciate it. I will test it more and I will close this as soon as I finish.
0
awking00Information Technology SpecialistCommented:
with cte as
(select filename from sqlresults where col0= 'DD' and col4 in ('ACH','NON')
 union
 select filename from sqlresults where col0= 'DD' and col4 = 'CHK' and col2 > 500
 union
 (select filename from sqlresults where col0 = 'DD' and col4 = 'BOP'
  intersect
  select filename from sqlresults where col0 = 'HH1' and col2 = 'Altire'))
select * from sqlresults where filename in
(select filename from cte);
0
awking00Information Technology SpecialistCommented:
Just noticed my unions query would also have to be modified to accommodate converting the nvarchar data to numeric. But it looks like you have a solution anyway.
0
Queennie LAuthor Commented:
This is the SQL query that works best for me of my situation. Simple and Clean query to where not a lot of WHERE Clause:

; with cte_filename as
(select filename from dbo.SQLResults
                           WHERE ([Column 4] IN ( 'ACH', 'NON' ) and [column 0] = 'DD')
                             OR ([Column 4] = 'CHK'  AND TRY_CAST([COLUMN 2] AS MONEY) > = 500)) and [column 0] = 'DD' )
                             OR (([Column 0] = 'DD' AND [Column 4] = 'BOP')
                             AND ([Column 0] = 'HH1' AND [Column 2] = 'Altire'))

) select s.*
  from dbo.SQLResults S
 where S.filename in (select filename from cte_filename)


Thank you for all your help Mark and all who participated to make this a success!  Without your intelligence and expertise, I dont' think I can make this.

Thank you again from the bottom of my heart.
1
Mark WillsTopic AdvisorCommented:
Nice CTE - Well Done :)
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.