Skip to content

Tag: mysql

How to return multiple rows in stored procedure?

I have a procedure that returns multiple rows, but separately. Please take a look at its result: I causes some issues when I want to fetch the result in the code (backend side). Now I want to create a temporary table and insert all rows inside it and then return that temp table as the result of the stored procedure.

Time difference between start/stop interactions

I have two tables in my database that looks like this learner_lessons learner_lesson_logs Basically, when a user clicks on a video it starts playing and the interaction is recorded as ‘Start’ and a timestamp is created accordingly. Now when a user pauses the video another interaction ‘Pause’ is created and the timestamp is recorded. The user might come back later

How to pass an SQL function on bulk insert query in node

I have to execute an INSERT INTO query using mysql or mysql2 in node.js The values passed into the query utilizes a spatial geometry function ST_GeomFromGeoJSON() on geoMap column which is of type GEOMETRY. Here is the simplified code: The above code does not work and throws the error Cannot get geometry object from data you send to the GEOMETRY

Insert with Foreign Key Contraints

In the above code, I want to add the distinct values from column make from vehicles into column makeID in makeModel but I get the error INSERT INTO makeModel (makeID) SELECT DISTINCT (make) FROM vehicle Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`dealership`.`makemodel`, CONSTRAINT `makemodel_ibfk_2` FOREIGN KEY (`modelID`) REFERENCES `model` (`ID`)) Answer

strapi with mysql running simple raw sql

Need to run this query in my test and unable to find the right syntax. This is no postgresql and mysql does not like hyphens in table names. Please suggest. Answer Simply don’t use not allowed characters, it makes the life much more easy. The backticks around the String, where implemented, so that theuser dind’t need to escapee single and

SQL query optimization for speed

So I was working on the problem of optimizing the following query I have already optimized this to the fullest from my side can this be further optimized? Answer Your query although joined ok, is an overall bloat. You are using the dim_ad_type table on the outside, just to make sure it exists on the inside as well. You have

How to select a nested item of json datatype?

I have a MySQL table named computers with a column named details that is json data type formatted. I’ve inserted a value like this in that column: I can simple get Chrome value by the following query: Now I want to know, how can I get 1680 value (which is the value of x) ? Answer You can alter your

How to use INNER LEFT instead of EXCEPT?

I read that MySQL does not support EXCEPT, and the workaround is to use LEFT JOIN. THIS IS MY QUERY: Basically: Trying to find out the manufacturers that sell PCs but not laptops. How can I convert this query with the LEFT JOIN? I got confused.. Table Computers: Table Manufacturers Computers: So since Manufacturer ID number 1 sells Laptop the

SQL count() not showing values of 0

i just started my journey with SQL, and made some tables of Cyclists, and Cycling Teams. Cyclist’s table contains columns: ID, Name, Team (which is foreign key of TEAMS ID) Team’s table contains columns: ID, Name, Number of Cyclists I want to Count number of Cyclists in each team, by using count() function ( Or basically any function, i just