So i have 2 tables, one its “Movies”(movie,type,lenght) and one “Cast”(title_movie,id_actor) and i have to count in how many “SF” Movies every actor played. I tried this: and it only shows me the actors that has at least 1 SF movie, the ones with 0 movies are not displayed.…
Tag: mysql
MYSQL – Cannot update a parent row: a foreign key constraint fails
db-fiddle link SCHEMA QUERY UPDATE QUERY ERROR If I change the ID of FIELD_TEST, To change the GRAPH_FIELD_INFO_TEST ID referring to the ID of FIELD_TEST, I set it as cascade in GRAPH_FIELD_INFO_TEST. However, it fails due to the following error. If I change the ID of GRAPH, it works normally, but it doesn…
How to optimize datetime comparisons in mysql in where clause
CONTEXT I have a large table full of “documents” that are updated by outside sources. When I notice the updates are more recent than my last touchpoint I need to address these documents. I’m having some serious performance issues though. EXAMPLE CODE gets me back 212,494,397 documents in 1 m…
Where clause with multi AND & OR conditions
I got a table agenda in which the admin can make a reservation for him self or for someone else (another user). If the admin make the reservation for him self in agenda.user_id will be stored the id of admin. In case that admin make a reservation for another person (another user) in agenda.user_id will be sto…
MySQL Update inner Join with MAX Date
In MYSQL 8.0 I have two tables with following structure: I am trying to update a value of EnromentStatus in child table by joining in on Enrolment table using the TSQL below: Problem is Enrolment has multiple entries for the child enrolment so I need to do MAX(DateUpdated) to get just a single record to updat…
LIKE group_concat with where clause
i am using Like in group concat & i want to get all modules attached to client even if it does not match to LIKE, i know that that i can do that with HAVING but i don’t want to use HAVING, any solution with WHERE or in join condition ? Any suggestions on how to get my expected result??
How to get the average of a column in MySQL
I’m trying to find a way to calculate the average of a column (as a double/float or decimal). I’ve found the AVG function in MySQL documentation but I can’t get it to work. Currently I’ve got the following code: Now for some reason this does not work. It returns “Unable to cast o…
Removing near identical values from mysql table
Is there a way of removing near identical values from a table in mysql? My table has records more than 10K out of which one of the company looks like this: on using describe tablename I get this: the names of the company are same however I would like to delete the second instance from table, thereby keeping j…
How to cast this integer value to date in MySQL
i have airbnb data and i want to cast column last_reviews (which datatype is int) to date this is my sql code http://sqlfiddle.com/#!9/b5ea42/31 how do i cast int > date? or how do i create last_reviews column as datatype date? Answer The last_review date seems to be the number of days since 1900-01-01 so …
Altering a query to show the overall sum of the numbers combined
I was wondering if there was a way to alter the below query to show the overall results This is what it currently shows Is there a way to alter the query to show the sum of just the A, AA, B, Drop, N, NA, NC, and PDROP so when I run the query it will just give me all