Query to get rows

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:

╔═════╦══════╗
║ 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.

2 answers

  • answered 2020-05-22 12:40 Gordon Linoff

    I think you want:

    select c.uid
    from junction j join
         course c
         on j.cid = c.cid
    group by c.uid
    having sum( c.course = 'Trial Course'  ) > 0 and
           sum( c.course not in (Trial Course', 'Introductory Course') ) = 0;
    

    The first having condition checks that the user has the trial course. The second that they have no non-trial or introductory courses.

  • answered 2020-05-22 13:14 nbk

    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