Woocommerce Question posted in
The official Woocommerce documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    I think you want:

    select j.uid
from junction j join
     course c
     on j.cid = c.cid
group by j.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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search