Category "sql"

Removing duplicates returned based on the column value

This SQL gives me the blog comments that contain just the banned words defined in my table. I only get the EXACT matches and it removes duplicate rows. It also

IF statement sub query optimisation in MySQL

select if(`num`>99, (select `value` from `sub` where `id`=`main`.`num`), "") as `result` from `main` This all works fine but MySQL is very inefficient as i

IF statement sub query optimisation in MySQL

select if(`num`>99, (select `value` from `sub` where `id`=`main`.`num`), "") as `result` from `main` This all works fine but MySQL is very inefficient as i

Display percentage of registered members that have not rated a Movie

I have the following three tables. See full db<>fiddle here members member_id first_name last_name 1 Roby Dauncey 2 Isa Garfoot 3 Sullivan Carletto 4 Jac

Error inserting values in SQL: Unknown column 'nan' in 'field list'

for row in dfp.itertuples(): cursor.execute(''' INSERT INTO players (PID, NAME) VALUES (%s,%s) ''', (row

Show Cost Data by Month based on Date Range

I have an existing table (jobCost) that has cost data by type and month as shown in the example here. Job Month Material Labor Equip 123 01/1/22 50.00 25.00 75

How to write a query using multiple 'IN' or any other keywords in SQL so that the condition being applied will be based upon the order defined

I want to fetch records from the database as : select * from table where column1 IN (aa, bb , cc...) and column2 IN (34, 55, 66...) and column3 IN(df, fg, hh...

How to execute an entire table and create a database diagram

When I run my query, only one of my rows is displayed. I also need to create a database diagram from these 3 tables Can someone please assist me on how to do th

Finding column name in table using SQL

I am currently using Advantage database. I have a table with hundreds of headers. How do I use query to find a specific column name? Example - I am looking for

Trouble inserting values into SQL table, values get mistaken for columns

I keep getting an exception that reads: "Invalid Column Name, 'IBM'." The error is happening at: "' + @ticker + '" even though @ticker is declared in VALUES.

Can I nest a select statement within an IF function in SQL?

Using Teradata.. I want to write a query that joins table 1 and table 2 on item code to the location in table 2. There are multiple locations per item code and

Logic Behind SQL Select Statement

Lets start with a basic SQL statement like this: SELECT CustomerName, City, Country FROM Customers; We get something like this: CustomerName City Country Alex

Unable to create a large json object from a postgreSQL query using json_build_object

I am trying to create a json object from my query using json_build_object as follows: Select json_agg(json_build_object('first_name',first_name,

PostgreSQL: Getting the sum of the difference in dates from two separate tables

The following code: SELECT ets.event_id, ets.event_date, dtn.don_date, dtn.don_date - ets.event_date AS date_diff FROM events ets, donation dtn GROUP BY ets.eve

Complex row manipulation based on column value in SQL or Power Query

I have a call dataset. Looks like this If a call about a certain member comes in within 30 days of an "original call", that call is considered a callback. I ne

add generated column with aggregated over a partion and sort

I am trying to add a calculated column that computes a rolling average of a sorted partition. I can make it work as a query but cannot seem to get the result to

Creating Custom Join in Django

I am struggling to create the correct prefetch behavior in Django. Here is the outline of the problem: Each Account has DailyQuotes, updated daily at different

MySQL correlated subquery very slow when range is included

DB Fiddle: https://www.db-fiddle.com/f/u1N4YYV3wmiWndnZNb95xz/5 For the following tables nodes and leaves, with nodes having just 9 records and leaves having a

PostgreSQL duplicate key value violates unique constraint while using on conflict do nothing on insert

Table def: CREATE SEQUENCE IF NOT EXISTS lazy_product_stock_id_seq; CREATE TABLE "public"."lazy_product_stock" ( "id" int4 NOT NULL DEFAULT nextval('lazy_pr

How can I solve this issue on Mac M1 Caused by: java.lang.Exception: No native library is found for os.name=Mac and os.arch=aarch64

I solved this issue with the code below in my build.gradle allprojects { configurations.all { resolutionStrategy { force 'org.xerial:sqlite-jdbc:3