Questions tagged [relational-division]
Operation in relational algebra or in an RDBMS to partition relations. The inverse of a Cartesian product (CROSS JOIN).
47
questions
3
votes
2
answers
474
views
SQL to handle multiple AND conditions
I have the following data:
Institution_no
Cred_type
1
1
1
2
2
1
I have a list box so the user can select multiple Cred_type, they want to be able to select either OR or AND conditions.
For the OR ...
- 131
2
votes
2
answers
169
views
How to find the rows that have exactly matching relationships in another table
SQL beginner here, so bear with me please.
Let's say I have a simplified table songs looking like this
id
title
1
Villain
2
More
3
More (Laszlo Remix)
I also have a table songartists:
song_id
...
- 175
0
votes
2
answers
44
views
how to get a single row based on my condition
I have following rows in my user table as
Now I want to get user having all accesstype as view,edit and share. So I need to get two rows out of this as
How can I achieve this using postgresql ...
- 103
0
votes
3
answers
61
views
How can I filter query results based on whether they have ALL values from another table?
For example, some actions that users can do, require a set of permissions.
Users are stored in the Users table:
UserId
1
2
and permissions in the Permissions table. Users can have permission A, B, ...
- 101
1
vote
2
answers
62
views
How to write a query for exact pairs of groups
I am given the schema:
EMPLOYEE(Fname,Ssn,Dno)
DEPARTMENT(Dname,Dnumber,Mgr_ssn,Mgr_start_date)
PROJECT(Pname,Pnumber,Plocation,Dno)
WORKS_ON(Essn,Pno)
fiddle: https://www.db-fiddle.com/f/...
- 45
1
vote
3
answers
108
views
How to write "and" queries for a lookup table when you can't use the AND keyword?
I'm building a practice website that allows users to search hotels based off of the amenities that the hotel provides. From the FE, the user will select a checkbox for however many amenities they want,...
- 11
6
votes
4
answers
841
views
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 ...
- 61
0
votes
1
answer
58
views
Query to select cards and transactions
I have some trouble with a query definition. My database is about operations in a bank and I want to show the card numbers which were used in transactions at all ATMs.
This is how my tables DDL:
...
- 71
3
votes
3
answers
234
views
An alternative to querying with multiple JOIN over the same table?
I have a Postgresql 11 database.
Let's say I have a table called houses. It should have hundreds of thousands of records.
CREATE TABLE houses (
pkid serial primary key,
address varchar(255) NOT ...
- 291
4
votes
1
answer
56
views
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 ...
- 197
2
votes
3
answers
226
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 ...
- 177
0
votes
3
answers
56
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 ...
7
votes
3
answers
7k
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
------...
- 297
0
votes
2
answers
55
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 ...
1
vote
2
answers
3k
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 ...
- 113
5
votes
1
answer
135
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 ...
- 411
0
votes
3
answers
136
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 ...
- 195
1
vote
3
answers
2k
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
2
answers
2k
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 ...
- 155
4
votes
3
answers
323
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 tags (
...
- 173
2
votes
2
answers
2k
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....
- 21
0
votes
2
answers
37
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 ...
- 103
2
votes
1
answer
728
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 ...
- 123
0
votes
2
answers
832
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 ...
- 163
5
votes
2
answers
218
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 ...
- 53
-1
votes
1
answer
2k
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 ...
- 1
5
votes
5
answers
52k
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
...
- 157
4
votes
2
answers
3k
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 (...
- 157
4
votes
5
answers
615
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, ...
- 359
5
votes
3
answers
9k
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 ...
- 53
0
votes
1
answer
65
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 ...
- 513
2
votes
3
answers
26k
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 ...
- 23
1
vote
1
answer
41
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 ...
- 339
2
votes
1
answer
190
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) => ...
- 123
0
votes
1
answer
60
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 ==> T=R÷...
- 171
3
votes
1
answer
195
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 ...
- 141
7
votes
1
answer
403
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 ...
- 567
-1
votes
2
answers
107
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 ...
- 99
2
votes
1
answer
67
views
Querying on multiple joined rows
I have a table called visits that is structured like so:
+----+--------------+-------------+
| id | name | visitor_id |
|----+--------------+-------------+
| 1 | event1 | 1 |...
- 567
1
vote
1
answer
178
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 ...
- 11
3
votes
2
answers
1k
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 ...
- 131
2
votes
4
answers
225
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 ...
- 143
2
votes
1
answer
2k
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 ...
- 123
3
votes
3
answers
3k
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 ...
- 31
9
votes
2
answers
4k
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).
...
- 507
4
votes
3
answers
18k
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, ...
- 1,025
4
votes
4
answers
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
-----------...
- 143