I have the following two tables: Items: id price shop_id name 1 10.22 1 apple 2 10.50 1 pear 3 10 2 orange 4 9 2 apricot Shops: id name 1 fruit-shop 2 grocery-shop I am trying to get all the shops that have EVERY item price > 10. I want to retrieve all the items associated with that shop.
Tag: sql
SQL query GROUP BY groups
I have something like this: id name totalAmount 1 name1 10 2 name1 20 3 name1 25 4 name2 5 5 name2 12 And need to looks like this: id’s name totalAmount 1,2 name1 30 2,3 name1 45 1,3 name1 35 1,2,3 name1 55 4,5 name2 17 I’m using the STRING_AGG but don’t know how to separated in the first
SQL – Group records together based on value
Currently I have a table with the following values: I would like a resulting SELECT to return the below: eg. The timestamp is grouped by a duplicate some_id Is this possible with SQL? Answer This appears to be just a simple aggregation,
Raw SELECT (without FROM) of most recent 7 days to current
I want to get the query result (e.g. to populate table) of last 7 dates (without times). I know that we can select some scalars without FROM statement. So I ended up with following solution: Please point me to better (and more elegant) solution if there is one. Answer The VALUES, table value constructor, is a…
PostgreSQL dynamic query: find most recent timestamp of several unrelated tables
I have a number of tables, and many of them have a timestamp column. I can get a list of every table with a timestamp column: I can get the hightest timestamp in the sharks table as follows I would like to get a table like I’m suspecting this requires dynamic SQL, so I’m trying something like But …
FOR XML PATH produces only first item
I have two tables: Reservations and ReservationNights (every reservation has many nights). In a stored procedure, I have a variable that looks like this: @roomTypeList = ‘2;3;4;5;’ -> its a list of RoomUseIds. I need to display only the reservations that have a reservation nights with one of th…
Select and join with sequelize
How to make this code with the sequelize? I used include, but it brings all the user data but I only need the name my code: Answer If you just want to reduce User model attributes then just indicate needed attributes in the attributes option: If you want to add a user’s name as a string prop at the same
How to get only comma values in SQL Server
I have a employee table and I am trying to get only comma values in SQL Server. Emp table: Eid Ename 1 Peter,J 2 Mike,S 3 , 4 ,,,,,, I tried this code: I am not getting the length 0 if the values contain only commas. Expected result: I want only 3 and 4 emp ids. Answer The answer there
Add geometry column from exisiting x,y,z columns
I have a table as such: All columns are Decimal(18, 3), there are also millions of rows. I would like to add a fourth column to be a geometry point. I have tried but to no avail: Driving me insane! Cheers Answer As mentioned by @lptr in the comments, you can use STPointFromText db<>fiddle
How does a multi-column index work in MySQL?
More specifically, what data structure does MYSQL use for multi-column indexing? I know MYSQL use Btree for indexing, which can only index one column. How does multi-column indexing work then? Answer Think of a MySQL “composite” index this way. Concatenate all the columns in the index together, th…