SELECT Query with AND in criteria

[isladogs]

@cheekybuddha
Sorry but that's wrong as well. It just returns CAT=C when the desired outcome is CAT=A

 

[cheekybuddha]

OK. scrub me! I'm going back to bed and blaming it all on COVID 19!

At least it answers my question as to why the JOIN was needed!

d

 

[iXm]

@cheekybuddha
SELECT
CAT
FROM Test
WHERE Product IN ('P1', 'P2', 'P3')
GROUP BY
CAT,
Product
HAVING COUNT(*) = 3;

is not working!

The CJL's solution in post # 4 is working but it's computationally intensive if I go for let's say 10 AND criteria!

I've to build a query on form with almost 15 combo boxes exercising AND criteria among products.

Please help with some optimised solution..?

 

[cheekybuddha]

Can you clarify whether you are looking for the CAT which has a record for each of the 10 Products, or just any of the 10 products.

Also, if you can post some more representative data to test on.

This works for 2 categories, but I haven't tried scaling up to 3 or more.:

SELECT
  CAT
FROM (
  SELECT
    CAT,
    COUNT(*) AS Cnt
  FROM Test
  WHERE Product IN ('P1', 'P2')
  GROUP BY 
    CAT,
    Product
)
GROUP BY
  CAT
HAVING COUNT(*) > 1;

 

[cheekybuddha]

It *should* work if you scale it:

SELECT
  CAT
FROM (
  SELECT
    CAT,
    COUNT(*) AS Cnt
  FROM Test
  WHERE Product IN ('P1', 'P2', 'P3')
  GROUP BY 
    CAT,
    Product
)
GROUP BY
  CAT
HAVING COUNT(*) = 3;

 

[isladogs]

@iXm
If you remove the GroupBy Product it definitely works for 3 conditions

SELECT Table1.CAT
FROM Table1
WHERE (((Table1.Product) In ('P1','P2','P3')))
GROUP BY Table1.CAT
HAVING (((Count(*))=3));

That gives the correct result : CAT=A.

If you are looking at more complex conditions, I would suggest using a specially designed function.

 

[cheekybuddha]

@Colin,

If you add another row to the table with CAT = 'C' And Product = 'P2' my guess is that 'C' will be returned again like in my original suggestion.

However, wrapping in another aggregation to weedle out the duplicates should give the correct answer (See post#25)

hth,

d

 

[isladogs]

Hi David
Yes I agree with your first sentence. I think that approach is too dependant on having just the right conditions in terms of data.
Whereas both of your subquery results worked perfectly....

Hard to say whether it would be faster with 10 conditions than CJL's self-join approach.
However, i do wonder why @iXm would need such a scenario in real life

 

[cheekybuddha]

>> Hard to say whether it would be faster with 10 conditions than CJL's self-join approach. <<

Yes, only testing will provide the answer.

The benefit of the aggregate approach is that you only need to change the IN clause with the parameters to match and the HAVING COUNT(*) with the number of parameters.

The self-join requires much more manipulation of the SQL.


>> However, i do wonder why @iXm would need such a scenario in real life <<

I wonder the same when I read about 99% of the questions on these forums!

 

[The_Doc_Man]

I've to build a query on form with almost 15 combo boxes exercising AND criteria among products.

I think we need clarification of something here. Are you suggesting that you need to have 15 separate and independent sub-clauses on 15 different fields? Or are you suggesting that you need 15 different selections that could be in one given field? Or something else. This is a case where precision of language is needed to not "go down a rabbit hole" for a solution that isn't what you really wanted.

In the first case, 15 separate and independent sub-clauses, you will possibly need AND operators between each sub-clause. In the second case, 15 different values that would be in the same field, we are talking about OR operators. Massively different. And the "something else" case is yet to be determined here.

I might have tried that as a cross-tab query to show counts for each possible category (as a row) against each product (as a column) with counts. Then I would have done a second-layer query to find rows for which specific columns had counts greater than zero.

 

[iXm]

Here is my DB attached, I am generating a database for keeping a record of aircraft maintenance engineers, which come in A, B & C categories. They need to undertake exams P1,P2,P3,P4......, more than a dozen in each category. Now what I want is a Form Query named "My Query" in my database, where I have the option to select people as per my selection criteria.

Selection Criteria can be one Category (A) or two Categories (A And B), similarly, exam status such as Pass Or Fail, or Pass And Fail for each exam P1,P2,P3,..........P15, as people can take one exam multiple times if they fail it. At the end, which exams, such as P1 And P2 And P3 And P4 And..... are passed by whom?

Please help!

 

[isladogs]

I haven't looked at your database but, from your screenshot and description, I think you should abandon trying to do this with a query

I suggest you look at my two example databases in this article Multiple Group and Filter
These show how to build filter criteria in SQL to handle various different possible filter criteria.

 

[Minty]

Looking at your form and a quick look at your database I'm puzzled by the structure.
Additionally, I have no idea what all those P fields are in your query form. It just looks all wrong?

 

[The_Doc_Man]

Seeing that form tells me you are going to drive yourself flat-out crazy. I still don't understand what you are seeking because of the possible involvement of AND and OR combinations.

I think you need to seriously look into a crosstab query that would give you counts that you could then search, but I sense a level of fluidity in this requirement that makes me wonder what you are really seeking. I honestly don't see the connection here between what you say you want and what you have suggested that you have. it is possible that in your initial post you over-abstracted the problem.

Referring to your first display, you have categories and products. Got that part OK. But now you are talking about a "query" form that clearly will have to be dynamic and that involves a third element, STATUS. When you build this kind of form, you are obviously preparing to asking a question of some kind, and there is where I'm having a disconnect.

What is the plain-language question you want to ask of your database?

 

[iXm]

I haven't looked at your database but, from your screenshot and description, I think you should abandon trying to do this with a query

I suggest you look at my two example databases in this article Multiple Group and Filter
These show how to build filter criteria in SQL to handle various different possible filter criteria.

Your Multiple Group and Filter is an excellent example, what if I want to select the kids of age Year-7 And Year-8 And Year-9 at the same time..?

 

[isladogs]

Both those examples use combo boxes for filtering which means you can only filter for one item from each combo e.g. One year group (or leave unfiltered to select all).
Whilst that makes sense for the two examples supplied, where you do need to select several 'similar' items, you would instead use a multiselect list box. The process of building filter SQL is almost identical