I have been using this function for last few years that converts an input string into a valid date in decided format. The above query will return, 28-02-2002 00:58:15 However starting 29/02/2020, if the input string to the function changes to ‘20200229005947354241’, it returns a null! I am not sur…
Tag: sql
Improve query performance and maintainability
I have a query like this: The output of the query will be something like this, basically a CODE and the respective COUNT, for example: After this first query, I have a foreach that will iterate the result of the query above. Inside the foreach, for each result of the query above, I want to get some informatio…
Join one table with two other ones by id
I am trying to join one table with two others that are unrelated to each other but are linked to the first one by an id I have the following tables Then I inserted the following data Obs: The admin does not has an invite The group has an approved invitation with status 2 (because the member ‘other’…
JSON_CONTAINS for multiple attributes on object in array?
Currently using MYSQL 5.7, soon to be migrating to 8.0. We have a json field in our database which has values which look like this: [ { “first_name”:”bob”, “last_name”:”lisserman” }, {…
How to design tables with foreign key but value is NULL in MYSQL?
I am trying to design a database with 2 tables. Project and Student. Student table records what project each student selects, and the student could only select a project that exists in the project table. However, when a student first enrolls in the class, I want to insert his info into the student table but a…
get first row fo each group SQL
I have data like below. I would like to get the top choice for each gender from the following data I have tried mapping the choices by descending order as below : BUT, I would like to get the following table where i get the top choice for eachgender : Answer If you are runing MySQL 8.0, you can use
Query for Exam Score calculation
Problem Statement: I have an EXAM table which has following fields and sample data I want to write a SQL query to output the following fields My SQL Query: Can I achieve my result by writing a better SQL query? Answer You could indeed join the table with several aggregate subqueries that compute the relevant …
How to search for multiple words with one query?
I am trying to make a simple php search using the following query. This works perfectly however the query only gives results for the first word. Would there be a way to get a combined result for all …
how get random questions from database include Categories?
Hallo i try to make a simple quiz application in php based on math tests from many years. I have database with such columns: -id -pytanie -a -b -c -d -poprawna -rok_id -typ_id I use rand function to get random id and next i use this in sql answer to get random question. However id dont know how get radnom
MySQL Group By for sorted table
I’ve two tables with these structures: Table 1 (comments) Table 2 (posts) My goal is to get a list with the latest comment from each post of a specific category_id. So for example, if I have category 1 as input I need the comments with the id 2 and 1 because these are always the last comments for each p…