I have a many to many relationship implemented: CREATE TABLE public.message ( id BIGSERIAL PRIMARY KEY, name varchar(40) UNIQUE NOT NULL ); CREATE TABLE public.package( id BIGSERIAL PRIMARY KEY, name varchar(40) UNIQUE NOT NULL ); CREATE TABLE public.package_to_message ( message_id BIGINT NOT NULL, package_id BIGINT NOT NULL, CONSTRAINT package_to_message_pk PRIMARY KEY (message_id, package_id) ); I need to select a package that has an association with a precisely defined set of messages [message_name_1, message_name_2, message_name_3]. With all of the above and none more. Is it possible to do this using a more or less optimized query? Nothing comes to mind.

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 table that supports queries against the actual fields of tasks, but also on the tags (tag names) of a task. Queries on the task fields themselves are straight-forward. Queries on the tags for a "does it have tag A?" are also straight-forward. What I am struggling with is coming up with a select/query structure that would allow me to also match things like "does it have tag A and tag B"? The best I've come up with is a lateral join on a subquery with an array aggregation, and then using array matching functions, e.g.: SELECT DISTINCT ON (tasks.id) tasks.* FROM tasks, LATERAL (SELECT array_agg(tags.name) AS tags FROM task_tags INNER JOIN tags ON task_tags.tag_id = tags.id WHERE task_tags.task_id = tasks.id GROUP BY task_tags.task_id) tt WHERE tt.tags @> array['tag1'::varchar, 'tag3'::varchar]; That way, it should be possible to programmatically build a WHERE clause (using tasks.* and tt.tags) satisfying all of the conditions of the user-provided "query". However, I'm not sure if this is the best way of doing it - thoughts? Is this query any efficient? Is there any index I could create that would improve it? Similarly, is there any way at all of making it work with wildcards against the tag names? Normal array matching wouldn't allow that, and solutions I've seen suggest using unnest (or, well, not using arrays in the first place), but then I'd lose the ability of saying "it needs to have both tagA and tagB". Is there any other way of building a query on these relationships that would allow that kind of "both tagA and tagB" matching?

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 = 'campaign_one') AND id IN (SELECT event_id FROM params WHERE key = 'utm_source' AND value = 'facebook') GROUP BY visitor_id UNION ALL SELECT visitor_id, '2'::text AS filter FROM events WHERE id IN (SELECT event_id FROM params WHERE key = 'utm_campaign' AND value = 'campaign_two') AND id IN (SELECT event_id FROM params WHERE key = 'utm_source' AND value = 'facebook') GROUP BY visitor_id As you can see, it performs different filtering on the params table 4 times. I'm using Redshift and although it scans this table very quickly, I have quite a few of these statements UNIONed together. Is there a way to rewrite the SQL using CASE/IF statements? The example uses key = 'utm_source' AND value = 'facebook' in both, but this is not necessarily true for all selects.

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 BY gid HAVING MIN(val) = MAX(val) SELECT gid FROM t GROUP BY gid HAVING COUNT(DISTINCT val) = 1 People seem to suggest that the first variant would be faster (if assuming appropriate indexes exist then looking up MIN and MAX would be faster than counting all values). Is that a fact or a myth.

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, ------------------- Lentil Soup Egg Drop Soup * Ingredients ------------------- name, ------------------- Lentils Chicken Broth Tomato Egg Onion * Recipe_Ingredients ------------------- recipe, ingredient ------------------- Lentil Soup, Lentils Lentil Soup, Chicken Broth Lentil Soup, Tomato Egg Drop Soup, Egg Egg Drop Soup, Chicken Broth, Egg Drop Soup, Onion I want my application to be able to return recipes based on the ingredients I have available. Something like: "show me all recipes that include both Chicken Broth and Egg". My SQL is a bit rusty, however, it seems like I would want to use SQL division to essentially divide out all recipes that include both of these ingredients. Can anyone provide an easy SQL division implementation that SQLite supports?

>

> 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 division of relational algebra) to retrieve the project name which is contributed by all employees. > > Question 2: Write an SQL query (equivalent to relational division of relational algebra) to retrieve the first name and last name of all employees who work on every project. > >

> ITEM (Item#, Item_Name, Unit_Price)
> ORDER (Order#, Ord_Date, Cust#, Ord_Amt)
> ORDER_ITEM (Order#, Item#, Qty)
> WAREHOUSE (Warehouse#, Warehouse_City)
> SHIPMENT (Order#, Warehouse#, Ship_Date)
> WAREHOUSE_ITEM (Item#, Warehouse#, Qty)
>

> Question 3: Write an SQL query (equivalent to relational division of relational algebra) to list Item_Name and Quantity that are stored in all warehouses in Sydney. My answer for Question 1: SELECT Pname FROM PROJECT WHERE EXISTS ( SELECT Pname FROM PROJECT, EMPLOYEE WHERE PROJECT.Pnumber = WORKS_ON.Pno); My answer for Question 2: SELECT Fname, Lname FROM EMPLOYEE WHERE EXISTS ( SELECT Fname, Lname FROM EMPLOYEE, WORKS_ON, PROJECT WHERE EMPLOYEE.Ssn = WORKS_ON.Essn AND WORKS_ON.Pno = PROJECT.Pnumber); My answer for Question 3: SELECT Item_Name, Qty FROM ITEM, ORDER_ITEM WHERE ITEM.Item# = ORDER_ITEM.Item# AND EXISTS( SELECT * FROM WAREHOUSE WHERE Warehouse_City = 'Sydney'); (If these are correct, I will able to convert them in relational algebra with relational division.) What are the mistakes in my SQL queries?

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 (course_id, course_name, professor_id) GRADE (student_id, course_id, grade, date_of_exam) I have to display the students that failed at least at all the courses that student with id = 1 failed. What I tried: SELECT s.student_id, s.first_name, s.last_name, n.grade, n.course_id FROM student s JOIN grade n ON n.student_id = s.student_id WHERE n.grade 1 AND NOT EXISTS ( SELECT 1 FROM GRADE g1 JOIN GRADE g2 ON g1.course_id = g2.course_id WHERE g1.student_id = 1 AND g1.grade 4 );

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 marketing_package_product ( package_id int not null references marketing_package(id), product_id int not null references product(id), primary key (package_id, product_id) ); insert into marketing_package_product values (1,1), (1,2), (1,3), (2,1), (2,5); Given products 1,2,3, I want to get marketing_package 1. But given products 1,2 only I do not want marketing_package 1. Is there a name for this type of query, and what is the most efficient way to go about it?

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 (course_id, course_name, professor_id) GRADE (student_id, course_id, grade, date_of_exam) I have to display the students that failed at least at all the courses that student with id = 1 failed. What I tried: SELECT s.student_id, s.first_name, s.last_name, n.grade, n.course_id FROM student s JOIN grade n ON n.student_id = s.student_id WHERE n.grade <= 4; ...this gets all the students that failed but I don't know how to go from this to all the students that failed at least at all the courses that student with id = 1 failed. If someone could point me in the right direction I would be grateful! **Additional details** For example: If student with id = 1 failed at courses with ids = 2,3. And we have other two students who failed at courses with ids = 2,3 (like student 1) and they might fail at other courses too, then I want to display those two students.

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_id group_id Now, I want to write a query selecting all users that are in all of some specified groups. e.g. Select * from users where the user is part of every one of "group1", "group2", and "group3". With a Django ORM query, I'd do something like users = ( User.objects .filter(user_group__group_id=group1.id) .filter(user_group__group_id=group2.id) .filter(user_group__group_id=group2.id) ) Which would produce a join for each call to .filter, e.g. SELECT * FROM users INNER JOIN user_group g1 ON g1.user_id = id INNER JOIN user_group g2 ON g2.user_id = id INNER JOIN user_group g3 ON g3.user_id = id WHERE g1.group_id = %s AND g2.group_id = %s AND g3.group_id = %s This becomes a bit hairy if I were to query a bigger set to match by. So what is a better way to do this? If I were to ask for "any" rather that "all", if would be a simple matter of SELECT * FROM users INNER JOIN user_group g1 ON g1.user_id = id WHERE g1.group_id in %s But that is not what I need. A small note: My specific environment is on Postgres, so no fancy MSSql thing will help me here. Preferably, the answer should be general enough to use in any SQL flavour.

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 values in a jsonb column

CREATE TABLE users (id BIGINT, fields JSON)

With entries like id|values -|- 1|{"f1":true, "f2":false} 2|{"f1":true, "f2":true} 3|{"f1":false, "f2":false} I'd like to write a query that returns only the user with 1 in my example.

I have the following tables, Order and OrderLine: Order: id | total ---------- 1 | 55.09 2 | 62.42 OrderLine: order_id | line_number | item | qty ---------------------------------------- 1 | 1 | Product A | 50 1 | 2 | Product B | 15 2 | 1 | Product A | 23 I am looking to construct a query that will select all Orders which contain both Product A and Product B. Some caveats: 1. There may be multiple lines that have Product A and Product B. For instance, there could be a third line in the order 1 that has Product A 2. There can also be situations with many items that need to be requested, not just 2 as it in this case. 3. I am also looking to query by total quantity. So, for example, only orders which have more than 20 units of Product A in total (across all lines). My first thought was do an inner join per item, so something like: SELECT T0.id FROM Order T0 INNER JOIN OrderLine T1 on T1.order_id = T0.id AND T1.ItemCode = 'Product A' INNER JOIN OrderLine T2 on T1.order_id = T0.id AND T2.ItemCode = 'Product B' GROUP BY T0.id However, I'm not sure how to extend this to have the ability to select based on total quantity. Possibly using SUM and HAVING?

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 I have like this AND CRED_TYPE IN (1,2) For the AND, I am really scratching my head. What they are saying is they want a list of institutions that have cred type 1 and cred_type 2. Perhaps I'm not thinking clearly but this is row by row, so doing this would lead to no results. AND cred_type = 1 AND cred_type = 2 -- you can't have a single row have two different values, this would return no results. They require that the user can select 10, 20, or more, so writing out a bunch of code for each and combining them all would be really tough - but this is the only thought I had so far. It would be like this Select institution_no from table where cred_type = 1 UNION Select institution_no from table where cred_type = 2 -- this would combine both and get me what I want, but you could imagine all the code for 10 or 20 of these.

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 | artist | | ------- | ------ | | 1 | K/DA | | 2 | K/DA | | 2 | Seraphine | | 3 | K/DA | | 3 | Seraphine | | 3 | Laszlo | Let's say I want to look for songs by K/DA and Seraphine, meaning no songs that only have one of the two artists and no songs that have those two plus other artists. This is some code that technically does what I want, but is very slow: SELECT title FROM songs WHERE EXISTS (SELECT * FROM songartists WHERE songartists.song_id == songs.id and songartists.artist == "K/DA") AND EXISTS (SELECT * FROM songartists WHERE songartists.song_id == songs.id and songartists.artist == "Seraphine") AND NOT EXISTS (SELECT * FROM songartists WHERE songartists.song_id == songs.id AND songartists.artist NOT IN ("K/DA","Seraphine")) Obviously I can also do a very simple lookup for the title, then for the artists and programmatically compare. But is there a performant SQL solution to this?

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

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, or both. | UserId | Permission | | ---- | ----- | | 1 | A| | 1 | B| | 2 | A Required permissions are stored in RequiredPermissions. From this, the POST action requires both permissions A and B | ActionType | Permission | | ---- | ---- | POST | A | | POST | B | | GET | B I want to query all users that can POST (user 1). This query would give users that have any permissions (that is, user 1 and 2):

select distinct u.UserId
from Users as u
join RequiredPermissions as rp
  on rp.ActionType = "POST"
join Permissions as p
  on u.UserId = p.UserId

How do I query users which have both permissions A and B **with this schema**?

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/9LnJN32WLVu1HsdmG8NUAc/5 Primary keys are given in bold. The question: Is it possible to define a view that will report the following detail for each employee: Ssn and the number of employees who work on exactly the same set of projects? Explain why not or given an SQL query. My idea was to find unique pairs of employees and check if projects worked by first employee equal those of the second one (this was done by using set operation except as if s1 except s2 union s2 except s1 is empty than it seems like s1 and s2 are equal) My attempt for this was: SELECT e1.ssn,count(e2.ssn) FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.ssn < e2.ssn AND NOT EXISTS ( ( (SELECT Pno FROM WORKS_ON w1 WHERE w1.Essn = e1.Ssn) EXCEPT ALL (SELECT Pno FROM WORKS_ON w2 WHERE w2.Essn = e2.Ssn) ) UNION ALL ( (SELECT Pno FROM WORKS_ON w2 WHERE w2.Essn = e2.Ssn) EXCEPT ALL (SELECT Pno FROM WORKS_ON w1 WHERE w1.Essn = e1.Ssn) ) ) GROUP BY(e1.ssn) Does this query look correct? Also is there a neater way of doing this? As a final side note what exactly determines if it is impossible for us to construct an SQL query for a problem?

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, and the amenity keys will be sent to the backend. On the backend, I've got these three tables:

hotels
| id | name         | vacancies
| ---| ------------ | -------- |
|   1| Marriott     |        0 |
|   2| Best Western |       10 |
|   3| Sheraton     |        3 |
 ------------------------------

          amenities
| id | name                  | key                   |
| ---| --------------------- | --------------------- |
|   1| Cafe                  | cafe                  |
|   2| Wheelchair Accessible | wheelchair_accessible |
|   3| Wifi                  | wifi                  |
 ----------------------------------------------------


    hotels_amenities_lookup
| id | amenity_id | hotel_id |
| ---| ---------- | -------- |
|   1|          1 |        3 |
|   2|          2 |        1 |
|   3|          2 |        2 |
|   4|          2 |        3 |
|   5|          3 |        2 |
|   6|          3 |        1 |
 ----------------------------

To search for *one* amenity, such as wheelchair_accessible, I would do something like this:

WITH hotels_with_amenity as (
	SELECT ha.hotel_id
	  FROM hotels_amenities_lookup ha
	  JOIN (
			SELECT id from amenities a 
			WHERE  a.key = 'wheelchair_accessible'
		) amenity ON ha.amenity_id = amenity.id
)

SELECT 	h.name, 
		h.vacancies 
   FROM hotels h, hotels_with_amenity hwa
  WHERE h.id = hwa.hotel_id;

Returns all three hotels. The question is: if the user selects multiple amenities, wheelchair_accessible and wifi for example, how would I query for hotels that have both? With this current set up, I couldn't do WHERE a.key = 'wheelchair_accessible AND a.key = 'wifi' Is there a better way of setting up these tables to make this query easier? I'm new to relational databases and it's likely I'm missing something obvious here.

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 now, my code is only pulling any student who has a grade of 'NA' in any course. I need for them to have 'NA' in ALL courses, not just 1 or 2 courses. My data: |Name |Course |Grade| |--------|-------|-----| |student1| en101 | NA | |student1| ma101 | B | |student1| py102 | A | |student2| en101 | NA | |student2| ma205 | NA | |student2| en206 | NA | |student3| ma101 | NA | I am trying to pull ALL rows for a student, ONLY if they have a grade = 'NA' in all their courses. Results should be: |Name | Course| Grade | |--------|-------|-------| |student2| en101 | NA | |student2| ma205 | NA | |student2| en206 | NA | |student3| ma101 | NA | my code is pulling every row that has a grade of 'NA', even if the other rows don't meet the condition. I need to pull ALL rows for that record, only if it meets the condition for every row. Seems easy in my mind...can't seem to make it work. thanx

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:

CREATE TABLE Cards(
	card_id INT PRIMARY KEY IDENTITY(1,1),
	number VARCHAR(25),
	CVV CHAR(3),
	bankAccount_id INT REFERENCES BankAccount(account_id)
);
CREATE TABLE Transactions(
	transaction_id INT PRIMARY KEY IDENTITY(1,1),
	ATM_id INT REFERENCES ATM(id),
	card_number INT REFERENCES Cards(card_id),
	sum_money INT,
	transaction_time DATETIME
);

And I tried below query:

SELECT *
FROM Cards C
WHERE C.card_id = ALL (SELECT *
						FROM Transactions T
						WHERE T.card_number = C.card_id
						)

And it doesn't work since I used card_id on both sides. Can somebody help me, please?