select all rows which satisfy constraints from another table

Question

my database (MySQL) contains 5 tables.

It should describe certain actions, states and constraints.

For any action to be executable all of its constraints must be met. Each state assures that certain constraints are met:

Actions (ID, name)
States (ID, name)
Constraints (ID, name)
has_constraints (Action_ID, Constraint_ID)
assures_constraints (State_ID, Constraint_ID)

My Question is: how do I select all actions, which are executable for a given state?

Thanks in advance, Jan

@Matthew Farwell: All sorts of things but I'm somewhat SQL-illiterate. Just experimented with some nested selects. It's not homework, btw. I just want to know — sl0815, Oct 09 '11 at 22:32
It sounds to me like you're looking for a JOIN, sl0815. Read the MySQL docs on JOINs and then try to come back with a more specific question. — Corbin, Oct 09 '11 at 22:32
This is a relational division problem. Try searching SO for that: http://stackoverflow.com/search?q=sql+relational+division — ypercubeᵀᴹ, Oct 09 '11 at 23:21
Check this question and answers: stackoverflow.com/questions/6523856/newbie-question-n-n-join-that-matches-all-a-list/ — ypercubeᵀᴹ, Oct 10 '11 at 00:02
And this: http://stackoverflow.com/questions/5482803/is-there-a-way-to-remove-the-nested-query-in-this-type-of-sql-select/5482912#5482912 — ypercubeᵀᴹ, Oct 10 '11 at 00:03
And Celko's article **Divided We Stand: The SQL of Relational Division**: http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/ — ypercubeᵀᴹ, Oct 10 '11 at 00:06

Erwin Brandstetter · Accepted Answer · 2011-10-10T00:38:37.467

2

SELECT a.*
  FROM actions a
 WHERE NOT EXISTS
       (
   SELECT 1
     FROM has_constraints hc
    WHERE hc.action_id = a.id
      AND NOT EXISTS
          (    
      SELECT 1 
        FROM assure_constraints ac
       WHERE ac.state_id = $my_state_id
         AND ac.constraint_id = hc.constraint_id))

Retrieves all actions with no constraints that a state wouldn't allow.
Includes actions without constraints at all.

edited Oct 10 '11 at 00:38

answered Oct 10 '11 at 00:31

Erwin Brandstetter

605,456
145
1,078
1,228

Nice. I prefer `SELECT *` inside `EXISTS` subqueries but that's just personal preference. – ypercubeᵀᴹ Oct 10 '11 at 06:07
@ypercube: AFAIK `SELECT 1` is the canonical syntax. But as you said, really a matter of personal preference. Maybe stuff for one of those questions we aren't supposed to post on SO. ;) – Erwin Brandstetter Oct 10 '11 at 06:24
@ypercube: Clearly, my "canonical" was unfounded. I've seen, you have seen my post: http://stackoverflow.com/questions/7710153/what-is-easier-to-read-in-exists-subqueries – Erwin Brandstetter Oct 10 '11 at 09:41

score 0 · Answer 2 · answered Oct 09 '11 at 22:33

0

You can do a select using JOINs, I cannot tell the exact table schema fro your example. But take a look in MySQLs documentation and learn about JOINs, they will take you where you want.

answered Oct 09 '11 at 22:33

score 0 · Answer 3 · answered Oct 09 '11 at 23:43

I don't don't know the columns you from each of those tables but according to your statement perhaps we could also have the statement done like this:

SELECT 
<columns>
FROM
has_constraints `constraint` INNER JOIN actions action ON `constraint`.action_id = action.id
RIGHT JOIN assure_constraints assure ON assure.constraint_id = `constraint`.id
WHERE constraint.state_id = <id of state>

If you want to get some columns from the state table you may add it on the join statements just inside the from.

select all rows which satisfy constraints from another table

3 Answers3