select name from person, author, article where name != “John Doe” AND person.pid = author.pid AND author.aid = article.aid AND title = select title from …
Tag: mysql
Query to check if MySQL database is connected
I am wondering if there is a best practice or standard query used to check if a MySQL database is still connected? I was originally thinking of performing a simple search in one of my tables, which will confirm if the database is still there. Just wondering if there is a better approach. Answer No need to act…
Compare Two tables in SQL and equals row set to ‘True’ in new Column
Can some one explain the SQL query for bellow outcomes in TableResult. There is two tables TableA and TableB, Function column in both tables are unique. Entire data in TableA should be there in ResultTable, If TableB Function equals in TableA function, Then ResultTable Compare column must be true otherwise it…
join two tables where unique id is 0 in other table
below are the two tables 1st is media_taxonomy and 2nd is media_taxonomy_map to join media_taxonomy: media_taxonomy_map: here is my sql query here is the result: BUT, I also want the rows in the joined table where media_taxonomy_id is 0 in media_taxonomy_map table is this possible? Answer I am guessing that y…
UPDATE and SET column row values by calculating values from antoher table
I have two tables: products – which has UnitsSold(total/cumulative amount of sold products/item), and sales – which has SoldQuantity (how many units sold per transaction) The same unit could be sold many times, so we need to calculate how many times it sold from sales table which have SoldQuantity…
How sum values in days intervals MySQL 5.7?
I have a server with MySQL 5.7. I have two tables. First one t contains creating dates for each id. Second table t0 contains profit records day by day for each id. I want to get columns with sums of profit for first and second 30 days for each id as well as for the first day. This code runs
Mysql Json extract with conditional filter
I’m trying to query some json data through a filter. Given a json array like this: I’d like to select all the rows which have as country name ‘France’ and a value greater than 10 as ‘people’ ONLY in objects which have the property name set to ‘France’. Would it …
How to change column charset, set as not null and add a default value?
The column contains some rules such as not nullable and character_set_name is latin1 and I should write a query to update only the character_set_name. field: name type: varchar(255) null: NO default: JACK character_set_name: latin1 Is there any way to do this in one query? if I run only the first query, ̵…
Use the value of an attribute from another table as a default value of an attribute from another table in MYSQL
I wanted to use the value on student_lastname in table tbl_student as a default value of sis_password in table tbl_sis_account but I am out of idea on how to do it. I tried putting “Select query” after the “Default” but it doesn’nt work, anyway here’s the sql: Answer I̵…
How to incrementally count the number of repeated instances
I want to incrementally count the number of repeated instances in a table, so if I have a table like this: Using the column “name” as reference, the output would be The DBMS implementation is MySQL, version 5.6 Answer In MySQL 5.x, where window functions are not available, one option uses a correl…