Find source and final destination using sql – Mysql
I have a table source_flight_destination like this ID Flight source destination 1 Indigo II BB 2 Air Asia AA II 3 Indigo BB JJ 4 spice jet SS BB 5 Indigo JJ SS 6 Air Asia II KK 7 spice…
Get Count 0 if there are no entries in the RIGHT Table – Postgresql
Websites: website_Id website_name 1 website_a 2 website_b 3 website_c 4 website_d 5 website_e Fixtures: fixture_Id website_id fixture_details 1 1 a vs b 2 1 c vs d 3 2 e vs f 4 2 g vs h 5 4 i…
Athena UNNEST array into column values – Amazon Web Sevices
WITH key_value AS ( SELECT e FROM headers CROSS JOIN UNNEST(CAST(json_parse(headers) AS array(json))) t(e) ) SELECT CAST(JSON_PARSE(e) AS map(varchar, varchar)) AS value FROM key_value { "headers": [{ "key": "Name", "value": "xxxx" }, { "key": "Email", "value": "xxxx" }, { "key":…
Data not getting inserted into database, despite of 201 request status – postgresql express js
I am using Typescript, Express, PostgresDB. Here is my code for connecting to the database cluster. import { Pool } from "pg"; const myPool = new Pool({ host: `${process.env.DATABASE_URL}`, //somedb.abc.us-east-1.rds.amazonaws.com database: `${process.env.DATABASE_NAME}`, //dbName user: `${process.env.DATABASE_USER}`, //dbUser password: `${process.env.DATABASE_PASSWORD}`, //dbPassword port:…
How to split array data to next row in Postgres – Postgresql
I have data in a table. I want to split the array data into separated rows. create table test1 ( _id serial, questionId character varying (50), answer character varying (50), subquestionId character varying (50), subquestionAnswer character varying (50), isActive character…
SELECT entries if they don't match every entry in another table [edit] – Mysql
I have a problem with an SQL query. I want to select every user that has unread messages. I have three tables users messages object_visited (has an entry [user_id,message_id], if the user has read the message) So I make a…
Calculate first value based on condition – Postgresql
I have the following table ticks datetime lowest_tick tick_lower 2022-10-01 00:02:00 204406 204306 2022-10-01 00:03:00 204525 204425 2022-10-01 00:04:00 204414 204314 2022-10-01 00:05:00 204200 204100 2022-10-01 00:06:00 204220 204120 2022-10-01 00:07:00 204120 204020 What I want to get is to…
PostgreSQL query to return 10 highest numbers in ASC order
I found an issue in my query: SELECT * FROM stocks_historic WHERE ticker = x ORDER BY YEAR ASC LIMIT 10 Current table is this one: stocks_historic ( historic_id SMALLSERIAL UNIQUE, ticker VARCHAR(10) NOT NULL, year VARCHAR(5) NOT NULL, eps…
Group by Use Query if SQL LEFT JOIN is performed multiple times – Mysql
I think it's impossible, but I'm asking if there's a good way. There are A table / B table / C table. The table was joined LEFT JOIN based on table A with FK called id of each table. At…
Null instead of null values in jsonb_build_object – Postgresql
I am trying to build query that gets data from 3 different tables using sqlalchemy I tried select( models.Animals.id, models.Animals.description, models.AnimalTypes.name.label('type'), func.coalesce( func.array_agg( func.jsonb_build_object( 'id', models.Animals.id ) ), None ).label('animals') ) .join(models.FamilyTypes, models.Family.id == models.Family.family_type_id) .outerjoin(models.Animals, models.Animals.campaign_id == models.Animals.id) .group_by(models.Family.id,…