Questions tagged [relational-division]

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

Filter by
Sorted by
Tagged with
2
votes
3answers
169 views

Get rows that exist exactly once per day for a given period

I have a case where I want to extract the device ids (DID) that exist once and only once for each day in a certain period. I have tried different methods and partitions but I seem to only be able to ...
0
votes
3answers
48 views

Select results from table ONLY if rows meet 2 conditions

**OrdersTable** OrderID Product SELECT OrderId, Product FROM OrdersTable WHERE Product = 'ProductA' AND Product = 'ProductB' --doesn't work as AND looks for the same data in one row OrderID ...
1
vote
3answers
144 views

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 ------...
0
votes
2answers
42 views

MySQL Using AND Statement

I am having issues with this question: Find the cities that have direct (non-stop) flights to both Honolulu and Newark. The answer should be Dallas, since its the only location that has ...
0
votes
2answers
71 views

Using EXCEPT with NOT EXISTS [closed]

SELECT DISTINCT cname FROM account AS cust_account WHERE NOT EXISTS ( SELECT type FROM account EXCEPT SELECT type FROM ...
5
votes
1answer
120 views

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 ...
0
votes
3answers
62 views

How to select specific items from table A which appear with all items of some column in table B?

I have 2 tables with the following schemas: t1(id, type) where id and type are the primary key t2(type, rate) where type is the primary key I want to find all id's which appear with all types from ...
1
vote
3answers
134 views

How to select students that failed at least at all courses that student with id = 1 failed?

I have the following tables: STUDENT (student_id, first_name, last_name, birth_date, year , domain) PROFESSOR (professor_id, first_name, last_name, birth_date, hire_date, title, salary) COURSE (...
0
votes
2answers
488 views

Names of courses where *every* enrolled student is a participant

I have 3 tables: StuddentMaster CourseMster Enroll_Master The data is about students enrolling for courses at an institute. I need a query which shows the names of courses where every enrolled ...
4
votes
3answers
197 views

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 ...
2
votes
2answers
809 views

Query For Where Table 1 (A,B,C) for each distinct A, has rows matching all rows in Table 2 (B,C)

I'm trying to optimize a query here: Given Table 1 (A,B,C) and Table 2 (B,C) Given Table 1 contains many unique tuples (B,C) for each A Select (1.A, 1.B, 1.C) Where for a given set of rows where 1....
0
votes
2answers
36 views

Finding Records that Have Same Options

Trying to figure out how find out which user records have at least 1 similar option enabled and then display these. Tables: user (id, username) thing (id,title) uthing (id, uid, tid) Uthing table ...
2
votes
1answer
465 views

Arbitrary queries on n:m relationship, including “all” and “any”

I'm using postgres >= 9.6. I have tasks, tags and task_tags tables, for a typical n:m relationship between tasks and tags. I'd like to be able to programmatically build queries against the tasks ...
0
votes
2answers
197 views

PostgreSQL find groups with same members

Given the following schema: User id Group id Membership group_id user_id (With a unique index on group_id, user_id) How would I select the group(s) of which the same two users are a ...
5
votes
2answers
205 views

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 ...
0
votes
1answer
615 views

Why does the UNION operation require that the relations on which they are applied be union compatible?

With respect to the operations in relational algebra (not in SQL): I'm having trouble understanding why 2 relations have to be union-compatible before the union operation can be applied on them. I ...
5
votes
5answers
14k views

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 ...
4
votes
2answers
1k views

Writing SQL query equivalent to relational division of relational algebra?

These are the list of 3 tables EMPLOYEE (Ssn#, Fname, Lname, Bdate, Address, Sex, Salary, Super_ssn) WORKS_ON (Essn#, Pno, Hours) PROJECT (Pnumber#, Pname, Plocation) Question 1: Write SQL query (...
4
votes
5answers
240 views

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, ...
4
votes
3answers
3k views

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 ...
0
votes
1answer
58 views

Declaratively select an entity requiring relations from all (dynamic number of) related entities

I am trying to construct a where clause that requires an X number of children to have a relation to a specific parent with a N-to-N relationship. Something similar to "Get all receipts where these ...
0
votes
3answers
10k views

Select one column with same value and check another column's value

I have a table with following structure (rest of the columns omitted for brevity) : +------------+-------+ | Cust_ID | Status| +------------+-------+ | 312 | A | | 312 | D ...
1
vote
1answer
36 views

Get rows that have ALL join criteria

I have three tables growth_spaces, equipment and growth_space_equipment which links the two tables together. I'm trying to write a query to get all growth spaces that have all of the equipment that I ...
2
votes
1answer
170 views

Select column values linked to exact set of values in another column

Given the below chats_users table in Postgres 9.5, with integer columns chat_id and user_id, I want to find the chat_id associated with an exact set of user_ids, for example: user_id IN (1, 3) => ...
0
votes
1answer
54 views

Prove or disprove that `T=R÷S` implies `S=R÷T`? [closed]

Prove or disprove: Given that T=R÷S, it is implied that S=R÷T. My attempt: Let us take some R,S tables and we will divide R÷S: step 1: R S ==> ...
3
votes
1answer
155 views

Finding parent by set of children

Given a many-to-many relationship (product/component), I need to find a product (or all products) that are composed from the given set of components. I can't wrap my head around the SQL query for ...
6
votes
1answer
257 views

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 ...
-1
votes
2answers
81 views

Finding two users with same pair group

I have the following table which details actions of users and the action's parameters: action_id(auto-increment) | user_id | action_name | action_attr1 I want to find which users performed the same ...
2
votes
1answer
59 views

Querying on multiple joined rows

I have a table called visits that is structured like so: +----+--------------+-------------+ | id | name | visitor_id | |----+--------------+-------------+ | 1 | event1 | 1 |...
1
vote
1answer
140 views

Matching a value with every element of a set of values - Postgresql

I'm new in Postgres and I can't achieve what I'm trying to do. I have a list of films in a Films table ID_FILM FILM NAME 1 Terminator 2 Forrest Gump 3 Nueve reinas A ...
3
votes
2answers
681 views

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 ...
2
votes
4answers
204 views

Make IN clause behave like AND clause

I've asked the same question on stackoverlow, but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice). Suppose I have 3 ...
2
votes
1answer
853 views

What Happens when Cartesian Product is applied to Relations with same attribute name

I understand that the Cartesian product (X) operation on two databases does not need to be UNION compatible. So,if there is a same attribute called name in two relations R and S where name in R is ...
3
votes
3answers
1k views

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 ...
9
votes
2answers
3k views

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). ...
4
votes
3answers
16k views

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, ...
4
votes
4answers
4k views

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 -----------...