Solved Filter a query by another

[Kamolga]

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

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

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]

???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]

I also do not fully understand. I am guessing either you need a subquery with an IN or NOT IN operator

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

or possibly just a LEFT OUTER JOIN instead of an INNER JOIN

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[Kamolga]

I am looking for a 'not in' on combination of two fields: I should have (from first query)

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

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

 

[Kamolga]

In essence

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 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.

mysql "Where not in" using two columns

I have one temporary table that contains userID and taskID. It is called CompletedTasks. I have a second table that contains userID and taskID. It is called PlannedTasks. I need to get a list of all

stackoverflow.com

 

[Kamolga]

Thank you very much MajP, I tried both solutions

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

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