1

I'm new to SQL and postgreSQL and I have an assignment.

I have two tables in database:

CREATE TABLE orders(
 order_id SERIAL PRIMARY KEY,
 ... some other fields
);

CREATE TABLE user(
     customer_id SERIAL PRIMARY KEY,
     customer_fname VARCHAR(30)
     customer_lname VARCHAR(30)
     order_id FOREIGN KEY (order_id) REFERENCES orders(order_id),
     ... some other fields
);

This table contains come data row with unique ID.

CREATE TABLE some_chosen_data_in_order(
 customer_id SERIAL PRIMARY KEY,
 id_order INTEGER[],
 customer_fname VARCHAR(30),
 customer_lname VARCHAR(30),
);

This table contains array type field. Each row contains values of IDs from table items in specific order. For example: {1,2,3,4}.

Now, I want to get data from table items for chosen rows from table some_chosen_data_in_order with order for elements in array type.

Using that, how can I get all of the customers and the order IDs on the users table who placed those specific orders?

My attempt was JOIN:

SELECT I.* FROM items AS I 
JOIN some_chosen_data_in_order AS S ON I.customer_id = ANY(S.id_order) WHERE S.customer_id = ?
Macy Creed
  • 57
  • 5
  • i also believe something from my query is wrong but please let me know. i am currently testing this through raw query only and not yet on postgreSQL. Please help. Really new to this. – Macy Creed Sep 01 '22 at 06:32
  • A [mcve] is a great start when asking for SQL assistance. – jarlh Sep 01 '22 at 06:42
  • i just want to learn how to extract the datas of joined table, name customer name and id number from different tables. can you please help with an example? – Macy Creed Sep 01 '22 at 06:56
  • Does this same question from 12 years ago have an answer? [PostgreSQL JOIN with array type with array elements order, how to implement?](https://stackoverflow.com/questions/2486725/postgresql-join-with-array-type-with-array-elements-order-how-to-implement) – qaziqarta Sep 01 '22 at 07:57

0 Answers0