Questions tagged [relational-division]
Operation in relational algebra or in an RDBMS to partition relations. The inverse of a Cartesian product (CROSS JOIN).
37
questions
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
-----------...
