Stack Overflow
Stack Overflow

Questions tagged [relational-division]

Operation in relational algebra or in an RDBMS (mostly SQL) to partition relations. The inverse of a Cartesian product (CROSS JOIN in SQL).

E.F. Codd identified eight relational algebra operators in his defining paper "A Relational Model of Data for Large Shared Data Banks". Division is the least known and probably most complex of them. It is the inverse operation of a Cartesian product (CROSS JOIN in SQL).

It means partitioning a (bigger) table with rows from another (smaller) table. For requirements like:
"Find tuples that combine a particular value in one attribute with several values in another attribute."

A practical example for a setup with a many-to-many relationship between clubs and people:
"Find all clubs where Sue and John and James are members."

More information

467 questions
110
votes
13 answers

How to filter SQL results in a has-many-through relation

Assuming I have the tables student, club, and student_club: student { id name } club { id name } student_club { student_id club_id } I want to know how to find all students in both the soccer (30) and baseball (50)…
Xeoncross
  • 55,620
  • 80
  • 262
  • 364
93
votes
12 answers

SELECTING with multiple WHERE conditions on same column

Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column... My table is a very simple linking setup for applying flags to a user ... ID …
RyanNehring
  • 1,033
  • 1
  • 9
  • 9
52
votes
3 answers

Select group of rows that match all items in a list

Assume I have two tables: cars – list of cars carname | modelnumber | ... passedtest – contains every test that a car passed: id | carname | testtype | date | ... 1 | carA | A | 2000 | 2 | carB | C | 2000 | 3 | carC | D …
user1229351
  • 1,985
  • 4
  • 20
  • 24
34
votes
6 answers

Select values that meet different conditions on different rows

Let's say I have a two-column table like this: userid | roleid --------|-------- 1 | 1 1 | 2 1 | 3 2 | 1 I want to get all distinct userids that have roleids 1, 2 AND 3. Using the above example, the only…
John
  • 32,403
  • 80
  • 251
  • 422
29
votes
9 answers

PostgreSQL where all in array

What is the easiest and fastest way to achieve a clause where all elements in an array must be matched - not only one when using IN? After all it should behave like mongodb's $all. Thinking about group conversations where conversation_users is a…
pex
  • 7,351
  • 4
  • 32
  • 41
25
votes
9 answers

SQL query where 'IN' act as 'AND' not 'OR'?

Pardon the title, I've no clue what to call this. So imagine I have this table_ref id_x|id_y --------- 6|70 6|71 6|72 6|73 8|70 8|73 9|72 9|73 How can i select id_y only if it matches id_x= 6 & 8 & 9? in this case it should return me 73 the…
sharif y
  • 513
  • 4
  • 11
24
votes
6 answers

SQL query through an intermediate table

Given the following tables: Recipes | id | name | 1 | 'chocolate cream pie' | 2 | 'banana cream pie' | 3 | 'chocolate banana surprise' Ingredients | id | name | 1 | 'banana' | 2 | 'cream' | 3 | 'chocolate' RecipeIngredients | recipe_id |…
Bryan Ash
  • 4,385
  • 3
  • 41
  • 57
18
votes
9 answers

How to efficiently set subtract a join table in PostgreSQL?

I have the following tables: work_units - self explanatory workers - self explanatory skills - every work unit requires a number of skills if you want to work on it. Every worker is proficient in a number of skills. work_units_skills - join…
ndnenkov
  • 35,425
  • 9
  • 72
  • 104
16
votes
9 answers

How to find all pizzerias that serve every pizza eaten by people over 30?

I'm following the Stanford Database course and there's a question where we have Find all pizzerias that serve every pizza eaten by people over 30 using Relational Algebra only. The problem consist of a small database with four…
Ivo Flipse
  • 10,222
  • 18
  • 50
  • 63
13
votes
3 answers

MySQL select join where AND where

I have two tables in my database: Products id (int, primary key) name (varchar) ProductTags product_id (int) tag_id (int) I would like to select products having all given tags. I tried: SELECT * FROM Products JOIN ProductTags ON…
Darrarski
  • 3,882
  • 6
  • 37
  • 59
13
votes
8 answers

Using same column multiple times in WHERE clause

I have a following table structure. USERS PROPERTY_VALUE PROPERTY_NAME USER_PROPERTY_MAP I am trying to retrieve user/s from the users table who have matching properties in property_value table. A single user can have multiple properties. The…
ivish
  • 572
  • 11
  • 35
13
votes
3 answers

jsonb query with nested objects in an array

I'm using PostgreSQL 9.4 with a table teams containing a jsonb column named json. I am looking for a query where I can get all teams which have the Players 3, 4 and 7 in their array of players. The table contains two rows with the following json…
Timo
  • 433
  • 5
  • 17
10
votes
5 answers

check if a column contains ALL the values of another column - Mysql

Let's suppose I have a table T1 with people IDs and other stuff IDs, as the following Table: T1 personID | stuffID 1 | 1 1 | 2 1 | 3 1 | 4 2 | 1 2 | 4 3 | 1 3 | 2 And…
Marco
  • 159
  • 1
  • 1
  • 9
9
votes
5 answers

MySQL Select ID's which occur on different rows with multiple specific values for a column

I'm trying to select items from an associative table that have satisfy two or more values of the same field, sounds confusing, let me explain. +-----------------------+ | item_id | category_id | +-----------------------+ | 1 | 200 | | …
flicker
  • 593
  • 2
  • 6
  • 12
9
votes
4 answers

In Postgres, how to match multiple "tags" for best performance?

Table: articles +--------+------+------------+ | id | title| created | +--------+------+------------+ | 201 | AAA | 1482561011 | | 202 | BBB | 1482561099 | | 203 | CCC | 1482562188 | +--------+------+------------+ Table:…
Elect2
  • 1,349
  • 2
  • 12
  • 22
1
2 3
…
31 32