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
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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, ...
user avatar
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/...
user avatar
  • 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,...
user avatar
  • 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 ...
user avatar
  • 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: ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ------...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 (...
user avatar
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 ...
user avatar
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 ( ...
user avatar
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....
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
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 ...
user avatar
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 (...
user avatar
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, ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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) => ...
user avatar
  • 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÷...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 |...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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). ...
user avatar
  • 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, ...
user avatar
  • 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 -----------...
user avatar
  • 143