Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
1 answer
74 views

How does it work internally

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 (...
Rajendra's user avatar
0 votes
1 answer
165 views

What's the easiest way to implement division in SQLite?

I'm putting together a simple cooking application that holds recipes in an SQLite database. I have three tables right now: Recipes, Ingredients, Recipe_Ingredients * Recipes ------------------- name, -...
Izzo's user avatar
  • 141
1 vote
2 answers
629 views

Filter rows of one table with conditions coming from another table

I have a table that stores conditions CREATE TABLE conditions (field_id TEXT, value BOOLEAN) With entries like field_id value f1 true f2 false And another table that stores users with field ...
mravey's user avatar
  • 113
2 votes
3 answers
2k views

SQL Server Select orders which have all of a set of items

I have the following tables, Order and OrderLine: Order: id | total ---------- 1 | 55.09 2 | 62.42 OrderLine: order_id | line_number | item | qty ---------------------------------------...
CPanarella's user avatar
3 votes
2 answers
627 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 ...
Rob's user avatar
  • 133
2 votes
2 answers
1k 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 ...
Krateng's user avatar
  • 175
0 votes
2 answers
51 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 ...
Rajesh Kumar's user avatar
0 votes
3 answers
381 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, ...
Igor Skoldin's user avatar
1 vote
2 answers
613 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/...
pk00's user avatar
  • 45
1 vote
3 answers
1k 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,...
cberg's user avatar
  • 11
6 votes
4 answers
6k 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 ...
Sam Bou's user avatar
  • 61
0 votes
1 answer
591 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: ...
hackermanwow's user avatar
3 votes
3 answers
1k 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 ...
Darth Kangooroo's user avatar
4 votes
1 answer
91 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 ...
Shir K's user avatar
  • 197
2 votes
3 answers
1k 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 ...
roy naufal's user avatar
0 votes
3 answers
708 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 ...
TimmyTooTough's user avatar
7 votes
3 answers
14k 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 ------...
Eldamir's user avatar
  • 297
0 votes
2 answers
62 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 ...
Arturo Madrid's user avatar
2 votes
2 answers
5k 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 ...
Bharg's user avatar
  • 123
6 votes
1 answer
262 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 ...
Salman A's user avatar
  • 461
0 votes
3 answers
480 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 ...
Yos's user avatar
  • 195
1 vote
2 answers
3k 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 (...
Eduard Valentin's user avatar
0 votes
2 answers
3k 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 ...
Harish Kumar's user avatar
4 votes
3 answers
688 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 ( ...
Alexander Bird's user avatar
2 votes
2 answers
3k 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....
Daniel Green's user avatar
0 votes
2 answers
42 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 ...
Codex73's user avatar
  • 103
2 votes
1 answer
1k 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 ...
Alex Hornung's user avatar
0 votes
2 answers
1k 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 ...
jaap3's user avatar
  • 183
5 votes
2 answers
235 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 ...
H.Rahimy's user avatar
  • 153
-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 ...
qwertyc's user avatar
5 votes
5 answers
59k 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 ...
learningIT's user avatar
3 votes
2 answers
4k views

Writing SQL query equivalent to relational division of relational algebra?

EMPLOYEE (Ssn#, Fname, Lname, Bdate, Address, Sex, Salary, Super_ssn) WORKS_ON (Essn#, Pno, Hours) PROJECT (Pnumber#, Pname, Plocation) Question 1: Write an SQL query (equivalent to relational ...
learningIT's user avatar
4 votes
5 answers
699 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, ...
eComEvo's user avatar
  • 379
5 votes
3 answers
11k 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 ...
Beytun's user avatar
  • 53
0 votes
1 answer
103 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 ...
Louis Somers's user avatar
2 votes
3 answers
31k 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 ...
Sudhanva's user avatar
1 vote
1 answer
66 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 ...
Kyle Decot's user avatar
2 votes
1 answer
295 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) => ...
Arrel's user avatar
  • 123
0 votes
1 answer
63 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÷...
3SAT's user avatar
  • 171
3 votes
1 answer
198 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 ...
Dmitry B.'s user avatar
  • 141
7 votes
1 answer
509 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 ...
Sam's user avatar
  • 637
-1 votes
2 answers
137 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 ...
Tom's user avatar
  • 99
2 votes
1 answer
72 views

Querying on multiple joined rows

I have a table called visits that is structured like so: +----+--------------+-------------+ | id | name | visitor_id | |----+--------------+-------------+ | 1 | event1 | 1 |...
Sam's user avatar
  • 637
2 votes
1 answer
297 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 ...
Bruno Mamani's user avatar
3 votes
2 answers
2k 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 ...
Vahid Mirjalili's user avatar
2 votes
4 answers
267 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 ...
Dima Knivets's 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 ...
techno's user avatar
  • 123
3 votes
3 answers
4k 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 ...
ashamed's user avatar
  • 31
3 votes
1 answer
591 views

What is the name of this type of query, and what is an efficient example?

The purpose is to find a parent, given it's children. For example, say you have a marketing package (aka a "combo"), and want to match it based on the products in it. Example table/data: create table ...
Neil McGuigan's user avatar
9 votes
2 answers
5k 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). ...
poke's user avatar
  • 527