I need to fetch key value pairs from the second object in array. Also, need to create new columns with the fetched data. I am only interested in the second object, some arrays have 3 objects, some have 4 etc. The data looks like this: The desired output: Answer Below is for BigQuery Standard SQL if applied to…
How to unnest the table based on date interval in Presto?
I have a table with events, where start_dt is a start and end_dt an end of the event. This table is partitioned by dt column derived from the end_dt. This means that events that start before and end after midnight are present only in one partition. What I need to do is to split each event into as many rows
Microsoft Access 2016 – Need to combine SQL query using JOIN …. ON Col1 MOST LIKE Col2
I am working in Microsoft Access 2016. I have a data table [Data] that contains many thousand rows of data. I have a lookup table [Lookup] that contains all of the project IDs that are known. These tables should combine on the project ID, which is a string field. Normally, I would join these tables using: The…
Select how many documents are in other table for each person
I have 3 tables. Client,Documents and ClientDocuments. The first one is the clients,where the information for each client are. The second one is the documents, which document can go in the system. The third one is the ClientDocuments, which client has which document. Tables Here I have to do a select where i …
Incorrect datetime value in MySQL table
I have just created a new MySQL in an AWS Ubuntu instance. Then I have copied a table from another MySQL server to the new created database. This is the structure from some fields from one of the tables: My issue is that every time I try to enter a new record on the table, there is an error at
Group by portion of field
I have a field in a PostgreSQL table, name, with this format: JOHN^DOE BILLY^SMITH FIRL^GREGOIRE NOEL^JOHN and so on. The format is LASTNAME^FIRSTNAME. The table has ID, name, birthdate and sex …
Which type of JOIN is used to find not matched data from a table?
Is the mentioned question is right? If yes, what will be the answer for that?
PostgreSQL Btree index on top of Unique index
I have a table with a unique index. CREATE UNIQUE INDEX task_index ON public.task USING btree (id, user); Most of my selects are using where id=? So do I still need an index on ID column? Or the …
Oracle SQL count same field across multiple tables
I have an oracle database that has the same name field in multiple tables. It kind of looks like this: The common field in each table can either be ‘yes’, ‘no’, or null. I’m trying to get the value counts of all the fields in one query, but I can’t figure it out. Basically …
SQL Server – CROSS DEPENDENCIES DATABASES [closed]
I’m able to pull cross databases dependencies but when the referencedDB is DIFFERENT DATABASE then I’m able to fetch the referenced object but not referenced OBJECT TYPE (e.g user_table,stored …