Tag: subquery

Subquery with joins – need help returning rows even if empty – MySQL

okay guys, this is a big one that’s confusing as hell. I’m creating a forum type dealio: I have an array of Topics and each topic has an array of Posts associated with the Topic. The Topics list page should show the latest Post from that specific Topic. (Each topic is in a category as well) Topic Latest post Check

MySQL: How to sum on joined queries (subqueries) with multiple conditions?

datetime mysql mysql-workbench sql subquery

I have two tables: Table Y: indicates over what time period money is valid +———–+———+——+ | starttime | endtime | name | +———–+———+——+ | 1 | 2 | Mark | | …

Postgresql – Select in Select Alias Problem

arrays postgresql postgresql-12 sql subquery

I have query. https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1b3a39357a5fe028f57b9ac26d147a1d SELECT users.id as user_ids, (SELECT ARRAY_AGG(DISTINCT CONCAT(user_has_bonuses….

mysql – fetch related rows from multiple tables without all combinations

left-join mysql sql subquery union

Let’s say I have a primary table record, and it has 2 related tables fields and comments. I would like to run one query that fetches a set of records AND fetches all the related fields for that record, AND fetches all the comments related to that record. If I do left joins to ensure I get the records, I

Count how many times a rows enter time is within the enter and exit times of all other rows

count datetime mysql sql subquery

I am looking to estimate a queue length for historical data at the time a record enters the queue. I would like to do this by counting how many of the rows in the data set have an enter time less than the enter time of the record, and an exit time greater than the enter time of the record.

Select from cross tables in postgres problem

left-join postgresql sql subquery where-clause

Lets imagine i have a table A that contains rows X,Y and Z, and i have another table that relate elements from A to another table B. A_B : element_A, element_B, and i want a query that for element b in B returns for all elements a in A return True if {a, b} exist in the table A_B and

Logic for backward updation based on parent value

sql sql-server sql-update subquery tsql

I have below tables with data as below Table_Cell Table_column Table_row I need to update SwitchCol in Table_column and Switchrow in Table_row Logic is If for an column_id in Table_column, all the combination with row_id in Table_row,present in table Table_Cell is 1, then make the SwitchCol =1 for that column_id Similary, for an id in Table_row, all the combination of

How do I get the matching id for every record?

datetime greatest-n-per-group postgresql sql subquery

My table is called platform_statuses, here is its schema: And this is my query, I would like to also get the matching id for the returned records. Also note that the abs function you see in the query is a custom one I got off this answer. Here is its definition: Answer I understand that, for each account and day,

Oracle – update data in first table with rows from second table

oracle sql sql-update subquery

How to update ‘date_from’ (t1) using ‘modfied’ (t2) when it is like 20/07/20. So in this case in t1 id’s 1 and 2 are to be updated and id 3 stays. Table 1: id date_from ———————– 1 …

SQL – get summary of differences vs previous month

gaps-and-islands sql sql-server subquery window-functions

I have a table similar to this one: It contains the stores that are active at BOM (beginning of month). How do I query it to get the amount of stores that are new that month – those that where not active the previous month? The output should be this: I now how to count the first time that each