Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [relational-division]

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

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:
"Find all clubs where Sue and John and James are members."

More information

53 questions
9
votes
2 answers

Find parent rows that have identical sets of child rows

Suppose I have a structure like this: Recipes table RecipeID Name Description RecipeIngredients table RecipeID IngredientID Quantity UOM The key on RecipeIngredients is (RecipeID, IngredientID). What are some good ways for finding duplicate…
poke
  • 527
  • 2
  • 7
  • 16
7
votes
3 answers

Query "all of" across many-to-many relation

Imagine a setup of three tables, User, Group, and UserGroup, where UserGroup consists of simple a foreign key to each of User and Group tables. User ---- id name Group ----- id name UserGroup --------- user_id group_id Now, I want to write a…
Eldamir
  • 317
  • 1
  • 3
  • 13
7
votes
1 answer

Deduplicate SELECT statements in relational division

I have a query that does a lot of duplicate work: SELECT visitor_id, '1'::text AS filter FROM events WHERE id IN (SELECT event_id FROM params WHERE key = 'utm_campaign' AND value = 'campaign_one') AND id IN (SELECT event_id…
Sam
  • 637
  • 2
  • 7
  • 12
6
votes
4 answers

Selecting ALL records when condition is met for ALL records only

Sorry if this has been asked before. I couldn't find any examples. I am trying to pull a student's course work for a semester, only if they have received a grade of 'NA' in ALL their courses. Right now, my code is only pulling any student who has a…
Sam Bou
  • 61
  • 1
  • 2
6
votes
1 answer

Find groups with exactly one value: COUNT(DISTINCT x) = 1 vs MIN(x) = MAX(x)

Given this data: gid | val 1 | a 1 | a 1 | a 2 | b 3 | x 3 | y 3 | z the following queries return groups (gid) that contain exactly one distinct value (val): SELECT gid FROM t GROUP BY gid HAVING MIN(val) = MAX(val) SELECT gid FROM t…
Salman Arshad
  • 461
  • 5
  • 16
6
votes
3 answers

SQL query to find all parent records where child records are a specific set

An Item has many ItemDetails. An ItemDetail has fields of "type", "value" and "item_id". I need to find all Items if and only if item has exact ItemDetails which are restricted by some changeable conditions. For example I need to find all Items with…
Beytun
  • 63
  • 1
  • 1
  • 7
5
votes
2 answers

A better way to write this query?

I have the following schema for my database (Postgresql 10): CREATE TABLE "PulledTexts" ( "Id" serial PRIMARY KEY, "BaseText" TEXT, "CleanText" TEXT ); CREATE TABLE "UniqueWords" ( "Id" serial PRIMARY KEY, "WordText" TEXT ); CREATE TABLE…
H.Rahimy
  • 153
  • 4
5
votes
5 answers

SQL query to display names of customers who have purchased all the DVD

These are the tables I have created and inserted the values accordingly: CREATE TABLE Customer (Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY, Customer_Name VARCHAR(30) NOT NULL ) CREATE TABLE DVD (DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY, …
learningIT
  • 155
  • 1
  • 3
  • 7
4
votes
3 answers

Many-to-one Subselection in single query

I have two tables with a foreign key from T1->T2, in a one-to-many relationship. That is, 1 tuple in table T1 is associated with 0..N tuples in T2. To create a simple example, lets say T1 is Cars, and T2 is a table of imperfections. So, a car can…
Jmoney38
  • 1,175
  • 5
  • 13
  • 22
4
votes
1 answer

Incorrect result of query using JOIN of 4 tables

If someone could help me with the query below it would be great! This query is quite challenging, and I tried to do so on my own, but couldn't display the correct result... I work with DB of a system that is alike "Facebook". The query should…
Shir K
  • 197
  • 1
  • 7
4
votes
3 answers

SELECT rows based on indefinite number of filters

How do I create a function which takes indefinite parameters? And then finds all game_ids in a table where each parameter matches a different row (but same game_id)? Example Table: create table tags ( tag_id serial primary key, game_id …
Alexander Bird
  • 173
  • 8
4
votes
5 answers

Postgres join where foreign table has ALL records

I have this people and tags table, like this, CREATE TABLE people AS SELECT * FROM ( VALUES (1,'Joe'), (2,'Jane') ) AS t(id,name); CREATE TABLE tags AS SELECT * FROM ( VALUES (1, 1, 'np'), (2, 1, 'yw'), (3, 2, 'np') )…
eComEvo
  • 399
  • 1
  • 4
  • 15
4
votes
4 answers

Help with Query. Finding records that have the same relationships (MySQL)

I have a table of Listings that has a many to many relationship with a Taxons table. The table structure looks like this: listings ---------------- id (int) name (varchar) listings_taxons ---------------- listing_id (int) taxon_id…
nwwatson
  • 143
  • 1
  • 4
3
votes
2 answers

Find all users who rated all the movies rated by a specific user

So we have the following relational schema: users(user_id, ..) movies(movie_id, ..) ratings(user_id, movie_id, rate) So, we want to find all the users who have rated all the movies which are rated by a specific user '1234' In other words, if user…
Vahid Mirjalili
  • 131
  • 3
3
votes
3 answers

Joining and filtering two sets of the same table

I guess this is an easy and stupid question. Consider this MySQL purchase table (where p_id is autoincremented): +---------+-------------+---------------+---------+ | p_id | item_id | user_id | count | ...…
ashamed
  • 31
  • 1
  • 3
1
2 3 4