Solved Filter a query by another (1 Viewer)

Kamolga

New member
Local time
Today, 10:46
Joined
Aug 18, 2020
Messages
11
Hi,

I face a bit of a challenge: I have one table where I score categories by project. Those cateries are active if they are selected in the category table. I would need to make sure that the rating table has a record for each selected category for each project.
I have a query to get all selected categories per project
SQL:
SELECT T_Projets.pr_ID, T_NewPrioCategories.np_CategoryName
FROM T_Projets, T_NewPrioCategories
WHERE (((T_NewPrioCategories.np_Selected)=True))
ORDER BY T_Projets.pr_ID, T_NewPrioCategories.np_CategoryName;
and one to see which ones exist
SQL:
SELECT T_NewProjPrioRatings.pr_ID, T_NewProjPrioRatings.np_ProjPrioCategory
FROM T_NewPrioCategories INNER JOIN T_NewProjPrioRatings ON T_NewPrioCategories.np_CategoryName = T_NewProjPrioRatings.np_ProjPrioCategory
WHERE (((T_NewPrioCategories.np_Selected)=True));

Is there a way to substract one from another (filter on both column project ID and category) to insert them in T_NewProjPrioRatings?
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:46
Joined
Jan 23, 2006
Messages
15,364
???unsure of what you are asking. Perhaps you could provide an example showing your starting values and desired end values highlighting both, when
a) the rating table has a record for each selected category for each project, and
b) the rating table does not have a record for each selected category for each project.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:46
Joined
May 21, 2018
Messages
8,463
I also do not fully understand. I am guessing either you need a subquery with an IN or NOT IN operator
or possibly just a LEFT OUTER JOIN instead of an INNER JOIN
 

Kamolga

New member
Local time
Today, 10:46
Joined
Aug 18, 2020
Messages
11
I am looking for a 'not in' on combination of two fields: I should have (from first query)

1598272831318.png


for project 74, 76, etc. and so far I have (from second query)
1598272946292.png


so I need to insert

74 Capacité
74 Complexité
74 Responsabilité sociétale
74 Retour sur investissement
76 Capacité
76 Complexité
76 Responsabilité sociétale
76 Retour sur investissement

(that is the list I try to get, on 2 columns)
so all the combinations that are in query 1 but not in query 2
 
Last edited:

Kamolga

New member
Local time
Today, 10:46
Joined
Aug 18, 2020
Messages
11
In essence
1598273275554.png

query 1 is X and query 2 is Y and try to insert the yellow in a table, my issue is that the key is on 2 columns

Should I add a calculated field that concatenate both and use it as a key?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:46
Joined
May 21, 2018
Messages
8,463
You can do a not in with two fields, but you can do a left join which is easier. Join on the two fields and then have a field from you second query. It will return null since there is no match. Filter on some field is null.
See two examples here.
 

Kamolga

New member
Local time
Today, 10:46
Joined
Aug 18, 2020
Messages
11
Thank you very much MajP, I tried both solutions
SQL:
SELECT *
FROM CompletedTasks
WHERE (userID, taskID) NOT IN
      ( SELECT userID, taskID
        FROM PlannedTasks
      ) ;
and recieved a "exist is missing of the sub selection." error message Then I tried
SQL:
SELECT T_Projets.pr_ID, T_NewPrioCategories.np_CategoryName
FROM T_Projets, T_NewPrioCategories
WHERE NOT EXISTS
      ( SELECT 1
        FROM T_NewProjPrioRatings
        WHERE T_NewProjPrioRatings.pr_ID = T_Projets.pr_ID
          AND T_NewPrioCategories.np_CategoryName = T_NewProjPrioRatings.np_ProjPrioCategory
      ) and (((T_NewPrioCategories.np_Selected)=True))
ORDER BY T_Projets.pr_ID, T_NewPrioCategories.np_CategoryName ;
with success
 

Users who are viewing this thread

Top Bottom