Skip to content

Tag: mariadb

SQL query that returns the primary key if found, NULL otherwise

Given a table like I am looking for a query that searches for a given name, returns the id if found, and NULL otherwise. Sort of what left join can do, except here there is no left table. Answer Use aggregation with MAX() (or MIN()): An aggregation query like this always returns exactly 1 row with 1 column and if

Get data from same row with MAX()?

[MariaDB 10.4, PHP8.0] How can I rewrite this so it works so it get weight_date from the row with MAX(total_weight) and list it AS highest_weight_date? I have read that we can’t use MAX() among with WHERE? I have tested to rewrite several examples, but I give up and embarrassed show my latest try: I try to add it to this

Order of CAST() and COALESCE() matters in MariaDB

I have a strange problem: There is a price in a JSON column in a table and the following statements give different results while they should give the same thing: Just to check I also added a JSON_EXTRACT(item.price_details, “$.shipping.price”) AS shipprice Result: MariaDB version: mariadb Ver 15.1 Distrib 10.3.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 DB Fiddle (I couldn’t use

MariaDB Case statement

I have the following SQL-Code in a Mariadb-Database: (1) I need the following result: If Matrix=’AW’ => I need the field “FaktorAW” else => I need the field “FaktorGW” is it possible to formulate Statement (1) with a “case statement”? Answer Of course, this is possible. Basically you can do this: You have to take care if this is really

Can’t remove duplicates from MariaDB 10

I’m having hard time removing duplicates from database. It’s MariaDB (protocol version: 10, 10.3.34-MariaDB Server). I need to remove rows where three columns are equal. I was trying to use WITH clause but database throws error that it can’t recognize ‘WITH’, so I focused on traditional way. I need to remove rows where foreignId, column1 and column2 are equal. I’m

Why concatenation does not work in Dbeaver?

Using Dbeaver, the || concatenation does not work. Had to use CONCAT() instead. Got output error message: Truncated incorrect DOUBLE value. Anyone else had this issue? Skill level: Beginner Editor: Dbeaver OS: Windows 7 Answer MariaDB expects the PIPES_AS_CONCAT to be set with the sql_mode to use || as a concat operator. One can check what is set for sql_mode

Cannot create trigger if-delete-clause

Here is the code I have which checks if there is more than 5000 entries after an insert, then deletes all entries which are greater than 2 hours old I am getting you have an error in your mysql syntax near IF (SELECT … DELETE FROM … I am using MariaDB, can someone help me understand where is the error

How do I find the number of video games with more than 5 developers. (SQL query)

Lets say i have 2 tables which are videogames and developers. Under the videogames table, we have the following attributes: videogameid(PK) title dateOfRelease Under the developer table, we have attributes: developerid(PK) name position I tried finding the total number of videogames that had more than 5 developers. Here is my attempt to resolve the issue. While I did not get