Query to get IDs related to certain foreign keys but not others

I have two sites with separate WordPress instances. One is in the process of getting WooCommerce, the other has existing users with set groups to access courses. I the future access to the courses is supposed to be sold via the shop. The answer I’m looking for has nothing to do with WordPress per se but with SQL. Most of the data I know how to get from either one table or by slight joining but not the one for the last step. Consider the following:

User table (superfluous but for clarification):

╔═════╦══════╗
║ UID ║ NAME ║
╠═════╬══════╣
║   1 ║ Bob  ║
║   2 ║ Tom  ║
║   3 ║ Joe  ║
║   … ║ …    ║
╚═════╩══════╝

Course table:

╔═════╦═════════════════════╗
║ CID ║ COURSE              ║
╠═════╬═════════════════════╣
║   1 ║ Introductory Course ║
║   2 ║ Trial Course        ║
║   3 ║ Actual Course 1     ║
║   … ║ …                   ║
╚═════╩═════════════════════╝

Junction table to connect users to courses n-to-n:

╔═════╦═════╗
║ UID ║ CID ║
╠═════╬═════╣
║   1 ║   1 ║
║   2 ║   1 ║
║   2 ║   2 ║
║   3 ║   1 ║
║   3 ║   2 ║
║   3 ║   3 ║
║   … ║   … ║
╚═════╩═════╝

Not gonna make you look up the relationships, so:

Bob (1) had the Introductory Course
Tom (2) had the Introductory and the Trial Course
Joe (3) had the Introductory, the Trial and at least one “actual” course

The Introductory Course grants nothing, the Trial Course grants a discount for any actual course. If somebody already has an actual course the discount has already been granted in the past. So in the result of the query I’m only looking for the user IDs of those who have the Trial Course, optionally also the Introductory course, but none of any others:

╔════╗
║ ID ║
╠════╣
║  1 ║
║  2 ║
╚════╝

I can’t just exclude rows from the result that don’t match the introductory or the trial course, obviously, as that would still give me user IDs that may have other courses attached to them.

Can’t help but think I’m missing an obviously simple solution.

Answer

As ffar as i understand you want a UID that where not in 3 Actual Course 1 AND I ASSUME THAT 4 is Actual Course 2 amd so on.

but usually there different introduction coirses and trial courses for dofferent categories, depending on University.

so i think you must rethink your strategy, or give us a glance how you manage this

SELECT
    DISTINCT UID
FROM junction 
WHERE 
UID NOT IN (SELECT UID FROM junction WHERE CID >= 3)

| UID |
| --: |
|   1 |
|   2 |

db<>fiddle here

Advertisement

Answer