Solved Multiple WHERE condition to filter a query (1 Viewer)

[plog]

I free handed my SQL and missed it by one parenthesis (and a GROUP BY I almost edited before anyone caught). I downloaded your database and tested my SQL and got it right:

SELECT OrderFK
FROM tblProductionStage
WHERE ((StageFK=1) AND (IsNull(StageDate)=False))
OR ((StageFK=2) AND (IsNull(StageDate)=False))
OR ((StageFK=20) AND (IsNull(StageDate)=True))
GROUP BY OrderFK
HAVING COUNT(OrderFK)=3

Now, the issue with doing it in the above way is that if your data has "duplicate records" (records with same OrderFK/StageFK/StageDate values) it could cause some false positives and false negatives. My method assumes that if an OrderFK has 3 records meeting the criteria that those 3 records must be unique and each meet a different criterion. But if you had one OrderFK with 4 records that match the criteria it will not be in the result. If you had one OrderFK that had 2 records meeting the first criterion, 1 record meeting the 2nd criterion and no records meeting the 3rd criterion it would still make it into the final results because it has a total of 3 records meeting the criteria even though it didn't have any meeting the last criterion.

I ran a test on your data on to see if you had any duplicates (same OrderFK/StageFK/STageDate) and you do. Not a lot, but you do. You can use this query to find them:

SELECT tblProductionStage.OrderFK, tblProductionStage.[StageFK], IsNull([StageDate]), Count(tblProductionStage.ProductionStage) AS CountOfProductionStage
FROM tblProductionStage
GROUP BY tblProductionStage.OrderFK, tblProductionStage.[StageFK], IsNull([StageDate])
ORDER BY Count(tblProductionStage.ProductionStage) DESC;

The alternative to my method is to make 1 query for each criterion and UNION those queries together and then do a count.

 

[KitaYama]

Now, the issue with doing it in the above way is that if your data has "duplicate records" (records with same OrderFK/StageFK/StageDate values)

@plog We shouldn't have duplicate data. And since we have (8 or 9) we will correct them and put some more validation to prevent it in future.
I tested your last solution, and so far it seems to be OK. I passed it to the team in charge of managing the database and they will do some more tests in depth.
I also made a query and joined tblProducts with tblOrders and tblProdctionStages and used it in your solution instead of a single table to show the actual Product name and other relating data in the search result form.
Still your query seems to be OK and show the correct result.

As I stated in my first post above, this will be a part of a search form that users are allowed to select multiple choices and search the orders. I was expecting a select query will be able to do the job, but it seems a totals query is necessary. This makes working on search form much more complicated than what we had assumed, because there are a lot more fields to be searched for along the above criteria.

But there's no doubt your solution answers our question here. The rest is our problem how to implement it in our database.
For now, I tick this thread as solved and if the team found anything (which I'm sure they won't) I'll be back.

I really appreciated your help and sharing your experience to solve this.
Million thanks to all who put their time on our problem.

Thank you.

 

[isladogs]

@KitaYama
You've already had expert help on the specifics of your issue

In case its any help, you could look at one or both of my examples where I filter on multiple text/combo boxes

Multiple Group & Filter

This article discusses different ways of filtering and grouping data in Access. It includes two example apps showing the use of multiple combo boxes & toggle buttons to filter and group data.

www.isladogs.co.uk

 

[KitaYama]

@KitaYama
You've already had expert help on the specifics of your issue

In case its any help, you could look at one or both of my examples where I filter on multiple text/combo boxes

Multiple Group & Filter

This article discusses different ways of filtering and grouping data in Access. It includes two example apps showing the use of multiple combo boxes & toggle buttons to filter and group data.

www.isladogs.co.uk

@isladogs Thanks for the link.
When I started this thread, I hoped I could find some starlight forward way to write the sql like the sample database in your site. Several yes and No, false and true in a where clause. But as you see we ended up with a completely different result than I had expected.

Our problem is that not only we have to filter the many side of a join like the following, but also each condition is a pair connected with AND:
tblProducts <----One-to-Many----> tblOrders <----One-to-Many----> tblProductionStages

Pair 1 : StageFK=1 AND StageDate IS NULL AND Pair 2 : StageFK=3 AND StageDate IS NOT NULL AND Pair 3 : StageFK=9 AND StageDate IS NULL

-----> All from many side of the join (tblProductionStages)

And then we have to add several other filters from one side of the join :
above criteria + DrawingNo=xxxxxx AND ProductPK=xxxxx And Delivery=Date+30 days etc.....


The guy who had designed the database had a not normalized data structure. One to one joined tables. All possible StageFK were fields in another table as fields (20 fields).
But everything was just as fast as a flash, anything was possible and every kind of filter could pull the data from sql server in a blink of an eye.


Now the guy is gone. Everybody claimed he had to be out of his mind to design a one-to-one table join. They deceided to normalize the data.

Well...Data is normalized now. But everything is slower. A lot of possibilities have changed to impossibilities. A lot more needs much more complicated SQL to show us the result we need.
We have to use crosstab queries to have the data side by side and you can guess how long does it take for a crosstab query to show us 1000 rows of data.
We also have trouble to search tables. This thread shows one of those cases. In this thread I only asked for three pair of the many side, but actually the users need up to 15 pairs of above conditions + a lot of additional fields from one side, tblOrders and tblProducts.

Fortunately I'm not in a position to decide. They know I can speak a little English and I'm a member here with experts like you and others to help me.
I'm simply a man in the middle.

I really appreciate all experts here, specially you and @plog who put his time on this to give us a solution.

 

[cheekybuddha]

Apologies for the delay in getting back on this - thanks @plog for picking up the pace.

Here is another query that should return what you want irrespective of the duplicate records:

SELECT
  o.*
FROM tblOrders o
WHERE o.OrderPK IN (
  SELECT
    ps.OrderFK
  FROM tblProductionStage ps
  WHERE EXISTS (
          SELECT
            1
          FROM tblProductionStage ps1
          WHERE ps1.OrderFK = ps.OrderFK
            AND ps1.StageFK = 1
            AND ps1.StageDate IS NOT NULL
        )
    AND EXISTS (
          SELECT
            1
          FROM tblProductionStage ps2
          WHERE ps2.OrderFK = ps.OrderFK
            AND ps2.StageFK = 2
            AND ps2.StageDate IS NOT NULL
        )
    AND EXISTS (
          SELECT
            1
          FROM tblProductionStage ps3
          WHERE ps3.OrderFK = ps.OrderFK
            AND ps3.StageFK = 20
            AND ps3.StageDate IS NULL
        )
);

Obviously you mentioned that the duplicates are a mistake and shouldn't be there, so perhaps plog's aggregate query method is better - however, this method allows you to state your conditions in more clear way, like how you described them to us.

Either way, you would benefit by adding indexes to tblProductionStage.StageFK, tblProductionStage.StageDate and tblProductionStage.StageUserFK if you have not done so already - it will help a lot with query speed.



d

 

[KitaYama]

Apologies for the delay in getting back on this - thanks @plog for picking up the pace.

Here is another query that should return what you want irrespective of the duplicate records:

SELECT
  o.*
FROM tblOrders o
WHERE o.OrderPK IN (
  SELECT
    ps.OrderFK
  FROM tblProductionStage ps
  WHERE EXISTS (
          SELECT
            1
          FROM tblProductionStage ps1
          WHERE ps1.OrderFK = ps.OrderFK
            AND ps1.StageFK = 1
            AND ps1.StageDate IS NOT NULL
        )
    AND EXISTS (
          SELECT
            1
          FROM tblProductionStage ps2
          WHERE ps2.OrderFK = ps.OrderFK
            AND ps2.StageFK = 2
            AND ps2.StageDate IS NOT NULL
        )
    AND EXISTS (
          SELECT
            1
          FROM tblProductionStage ps3
          WHERE ps3.OrderFK = ps.OrderFK
            AND ps3.StageFK = 20
            AND ps3.StageDate IS NULL
        )
);

Obviously you mentioned that the duplicates are a mistake and shouldn't be there, so perhaps plog's aggregate query method is better - however, this method allows you to state your conditions in more clear way, like how you described them to us.

Either way, you would benefit by adding indexes to tblProductionStage.StageFK, tblProductionStage.StageDate and tblProductionStage.StageUserFK if you have not done so already - it will help a lot with query speed.



d

@cheekybuddha Sorry for a delay in replying.
We had to do a lot of tests before choosing one of given ideas and see how pros & cons of each method reflects the overall running of the database.

As you had guessed, @plog's method is much faster, but we faced other problems for using a total query with a count (not impossible, but not flexible enough for us).
Your last given method, though a little slow on a million records, was more flexible for adding additional filters from one side of the join.
We chose to use your method and added additional indexes to the table as suggested, still not as fast as plog's total query, but I think we can live with that.

I really appreciate all given advices/solutions. and a special thanks to you and @plog for the time you put on this and shared your knowledge and experience.

Million thanks.

 

[cheekybuddha]

I wonder if there would be any speed up if you re-wrote it like:

SELECT
  o.*
FROM tblOrders o
WHERE o.OrderPK IN (
  SELECT
    ps.OrderFK
  FROM tblProductionStage ps
  WHERE EXISTS (
          SELECT
            1
          FROM tblProductionStage ps1
          WHERE ps1.OrderFK = ps.OrderFK
            AND ps1.StageFK IN (1, 2)
            AND ps1.StageDate IS NOT NULL
        )
    AND EXISTS (
          SELECT
            1
          FROM tblProductionStage ps3
          WHERE ps3.OrderFK = ps.OrderFK
            AND ps3.StageFK = 20
            AND ps3.StageDate IS NULL
        )
);

Is your data on SQLServer? If so, you may find a speed improvement if you create the above SQL as a view and link to that.

 

[KitaYama]

I wonder if there would be any speed up if you re-wrote it like:

SELECT
  o.*
FROM tblOrders o
WHERE o.OrderPK IN (
  SELECT
    ps.OrderFK
  FROM tblProductionStage ps
  WHERE EXISTS (
          SELECT
            1
          FROM tblProductionStage ps1
          WHERE ps1.OrderFK = ps.OrderFK
            AND ps1.StageFK IN (1, 2)
            AND ps1.StageDate IS NOT NULL
        )
    AND EXISTS (
          SELECT
            1
          FROM tblProductionStage ps3
          WHERE ps3.OrderFK = ps.OrderFK
            AND ps3.StageFK = 20
            AND ps3.StageDate IS NULL
        )
);

Is your data on SQLServer? If so, you may find a speed improvement if you create the above SQL as a view and link to that.

Yes, BE is a SQL server database.

I'll try it and report back Monday morning.
thanks for additional support.

 

[cheekybuddha]

Actually, I needed more coffee when I posted this morning!

The revised query changes the logic and will return the wrong results (essentially will return results where StageFk = 1 OR StageFK = 2, instead of AND. )

Please ignore the SQL in Post #27.

Still, the advice to create a view on the server from the SQL in Post #25 still stands.

 

[KitaYama]

Actually, I needed more coffee when I posted this morning!

The revised query changes the logic and will return the wrong results (essentially will return results where StageFk = 1 OR StageFK = 2, instead of AND. )

Please ignore the SQL in Post #27.

Still, the advice to create a view on the server from the SQL in Post #25 still stands.

I already sent a mail and asked the view to be added to the server.
Thanks again.