Postgresql – Find xy points within radius
I have an app written in TS with a PostgreSQL database that I access using Prisma. In the database I have a table called Points with columns X and Y that represents points on the cartesian plane. I need to…
Fetch matched and unmatched records and different set of columns from two tables in mysql query
I have two tables having different columns and two columns are common. Some of records are same in both the tables. Need to query to fetch all two table records with joining column, Used MySQL database. Table1 - T1 transaction_id,…
JSON_TABLE Extract lists from different nodes
I have a JSON like this, stored in a CLOB column: { "code": 1, "required": [ { "field": "R1", "value": 1 }, { "field": "R2", "value": 2 } ], "optional": [ { "field": "O1", "value": 1 }, { "field": "O2",…
Mysql – SQL indexing with 2 join tables
I have 3 tables. country, team, player table Country id name 1 x 2 x 3 x table Team id name country_id 1 x 1 2 x 1 3 x 1 table Player id name country_id team_id 1 x 1…
MySQL get all weekend days between two dates
I'm trying to calculate how many weekend days passed between two dates. SELECT DISTINCT start_date AS fr_date, end_date AS to_date, DATE_FORMAT(start_date, '%a') AS wd_fr, DATE_FORMAT(end_date, '%a') AS wd_to, ( FLOOR(TIMESTAMPDIFF(DAY, start_date, end_date) / 7) * 2 + IF(DATE_FORMAT(start_date, '%w') IN…
SQL PHP last update database
I made a PHP website that use a SQL database for the information. Now I want to show the date of the last update of that database. My code works great, except, when I run the command in phpMyAdmin I…
MySQL Exclude values
I have two tables: TESTS: ID YEAR DESCRIPTION 0 2023 some text .. ... ... N 2024 some text test_prep_materials: ID PATH TID 0 /home/user/file0.pdf 0 .. ... ... M /home/user/fileM.pdf 0 So any test have prep materials. And some…
Mysql – Getting "Error converting data type varchar to numeric."
Basically I'm trying to insert 5 rows of data into my Book Table and it gives me an error saying that it can't convert the data type varchar to numeric. This is the query I was entering INSERT INTO Book…
Mysql – print only the student names who have performed better in their respective latest years
there is a table student|Year|Marks ABC|2023|98 XYZ|2021|100 MNO|2019|81 XYZ|2020|98 MNO|2018|98 ABC|2022|100 the result should just show XYZ|2021|100 i am trying to create the query as with cte as (select *, row_number() over (partition by name, year order by marks) as…
Aggregated rollup in PostgreSQL with avg and sum in different levels?
I have a PostgreSQL query that aggregates data from multiple tables and generates a report. Currently it works perfectly fine and it is quite simple. Here is the query I'm using: SELECT a.name AS account_name, c.name AS campaign_name, ad.name AS…