I have a column in my SQL database (using mySQL) ‘lastname’, which has any number of employee’s last names in it. I want to write a query that handles a search for last names using a comma delimited list. So the user will type: And that gets stored in a String, lastNameQuery, which then gets…
Tag: mysql
How can I combine two columns and sort the values alphabetically?
I’m new to programming languages and am trying to get the customers with the longest and shortest names, combine them in a single column, and sort them by longest then shortest names. Here’s what I have. How can I query to get my desired result in MySQL? Table is orders customer_id longest lowest John N…
Is it possible to further filter on MAX results?
I have a table like so A) As you can see, the same ID can have multiple Revision IDs Now, I can grab all the latest revision for each ID by using MAX with GROUP BY like so: B) As you can see here, the Revision ID of 47141 is the latest revision for ID 3282 So here is my
Lost connection to MySQL server during query error when I try to use function
I am trying to make a function which checks if any date between say Date-range A and B lies between the Date-range C and D. Whenever I try running the function MySQL Workbench always throws an Error Code:2013 Lost connection to MySQL Server during query. I am assuming this error is arising due to the loop run…
MySQL – Join 2 tables and count number of entries
I’m trying to join 2 tables and count the number of entries for unique variables in one of the columns. In this case I’m trying to join 2 tables – patients and trials (patients has a FK to trials) and count the number of patients that show up in each trial. This is the code i have so far: Th…
SQL how to create a view with 2 sums of diferent tables, when those tables have diferent number of rows
I have 3 tables, “accounts” “charges” and “payments” I want to create a view with the account data the sum of the charges, and the sum of the payments, but payments have usually more records than charges for the same account when I create the view and GROUP BY charges end u…
Alter Table Foreign Key
I’m trying to add barista_grade_id to the baristas table as a foreign key and I’ve looked every where on what syntax to use and it seems to be unanimous that this is the correct way. I did it before this and it worked. but because of some mistakes I deleted it and redo it again, but for whatever r…
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 pro…
Mysql JSON_OBJECT aggregate json objects to json array where id matches
As mentioned in the title, i’ve been trying to aggregate json objects to a json array where the id is the same. I’ve got a main table “kurs” that has a unique id and is connected to the table “kompetenz” over a link table. For each “kurs” there can be multiple &…
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&…