I have 2 tables: Table class: Table class_event, where I store events related to classes, such as “started” and “ended”. I need a query the amount of times each class has been started and ended. This works: But when I do exactly the same to get the amount of ended classes it shows inco…
Tag: sql
Select with joins in a unique table system
I am trying to build an SQL query on an omekaS database to extract books with a specific title and date. In this system, the table holding the values is called value, and the relevant attributes are as so : The resource_Id is the identifier through the db Value is one field; like the book “Moby DickR…
Create database scripts where tables have prepopulated data across environment
I have a table named, ‘[UserTypes]’ where Primary id is [uniqueidentifier]. and I have a reference in another table called ‘[Users]’. Users table has UserTypeId as foreignKey. This ’80D1EEE7-0BCC-48A7-A741-29A1D8B6E580′ is the userTypeId of ‘ADMI’ from the userT…
How to Get only number from string mysql
I have a column called Equipment in my table that contains something like this: store(2) I need to create a new column and select only the number from the text otherwise we put 1. Example: Thanks. Answer You can use the REGEXP_SUBSTR function for this.
Update and renew data based on data in other tables
There are 3 tables student, course, and takes as following tot_cred column data in the student table now is assigned with random values (not correct), I want to perform the query that updates and renews those data based on the course’s grade each student has taken. For those students who received F grad…
Full Outer Join failing to return all records from both tables
I have a pair of tables I need to join, I want to return any record that’s in tableA, tableB or both. I think I need a FULL OUTER JOIN This query return 1164 records And this one return 3339 records And this one returns 3369 records (so there must be 30 records in tableA that aren’t in tableB) I
How can I include strings which contain special characters in my WHERE clause?
I am using SQL Server 2014 and I am faced with the following problem in the WHERE clause of my T-SQL query. My WHERE clause will list the names of towns. However some of the names contain characters like an apostrophe in them. Example: ST JULIEN D’HOTMAN My T-SQL looks as follows: The above does not wor…
Input/output parameters are not visible when executing procedure in SQL navigator
When I execute my stored procedure in SQL Navigator 7.2.0, the input parameters are not shown. There is nothing wrong with the stored procedure. Is there any configuration to add up? An answer would be really helpful. Below is how it shows when I execute the stored procedure, where I can’t add any param…
MySQL alternative way to `CASE` Statement
May I know, is there any alternative way or short way to write this case statement shown in the below: I tried using mysql built in funciton ifnull as below: ifnull(p.our_price,p.sales_price) But it doesn’t work for me. Answer You could stick with a CASE expression but use COALESCE to make it more succi…
Does the column order matter in a WHERE SQL statement
I’m working on a system with a good amount of indexes. Some indexes are simpler than others. I.e. they’re INT, VARCHAR, DATETIME and in some cases ENUMS(maybe 5~25 variations). Does the WHERE order matter? In other words, would placing the easier to search columns first increase speed/performance?…