spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
  Home » MYSQL » Page 1
MySql: Concatenate 2 columns if condition is met
Here's a very literal solution... SELECT CONCAT_WS(' ',p.product,CASE WHEN total > 1 THEN d.division ELSE NULL END) name FROM product p LEFT JOIN division d ON d.id = p.divisionid JOIN ( SELECT product , COUNT(*) total FROM product GROUP BY product ) x ON x.product = p.product ORDER BY p.product,d.division;

Categories : Mysql

Select several max types for each datatype per distinct value in mysql
You will not be able to build a query with a dynamic number of columns. You can do this query if you already know the game list, which I guess is not what you need. BUT you can always post-process your results with any programming language, so you only have to retrieve the data. The SQL query would look like this: SELECT userid AS User, data_type AS Game, max(timespentaday) AS TimeSpentAD

Categories : Mysql

Best Datatype For A Collection
This violates the first normal form: First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain. Your User.Events column is not atomic because it contains multiple values in a business sens. The solutio

Categories : Mysql

How to insert Lat and Lng to point type column in mysql with laravel?
GeomFromText is actually a MySQL function that is executed in SQL. If you need to use it you will have to use DB::raw("GeomFromText('POINT('.$lat .' '.$lng.')')") I have never tried using DB::raw() on a model yet, but, in theory it could work. But, this may mean you will need to use a Query builder query to do this instead of Eloquent. Also, be VERY CAREFUL about including variables directly whe

Categories : Mysql

mysql wont install in CentOS due to conflict with mariadb
Following worked for me. $yum shell remove mariadb-libs run exit and then install MYSQL RPM package. Got idea from following post http://serverfault.com/questions/613366/install-mysql-on-centos7-with-rpm

Categories : Mysql

Group by hour showing breakdown for each day within date range MYSQL
Group by date as well: SELECT DATE(received), HOUR(received), ... ... GROUP BY DATE(received), HOUR(received) Since you're grouping by hour() only, you'll essentialy be getting Hour 1 sum of leads in hour 1 of jan 1, jan 2, jan 3, ... dec 31 2 sum of leads in hour 2 of jan 1, jan 2, jan 3, ... dec 31 Rather than Jan 1 Hour 1 sum of leads for just that one hour on the one day Jan

Categories : Mysql

MySQL - extract number from data field using SUBSTRING
You can use a combination of SUBSTRING_INDEX functions: SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(field, 'NUMBER=', -1), ';', 1) FROM tablename Please see an example fiddle here. The inner SUBSTRING_INDEX will return everything after the NUMBER= string, while the second will return everything before the ; returned by the inner function.

Categories : Mysql

SQL Query Subquery structure
Could be something like this: SELECT a.employee_id, b.customer_id, a.supervisor_id FROM Employee a INNER JOIN Orders b ON a.employee_id = b.employee_id INNER JOIN Orders c ON b.customer_id = c.customer_id AND c.employee_id = a.supervisor_id

Categories : Mysql

Mysql query to get addresses
One option would be to add a predicate on one of the queries, to omit rows where alt_address matches primary_address_street. For example, append something like this to the end of your query: WHERE NOT ( alt_address_street <=> primary_address_street ) appending that to your existing query gives: select primary_address_street as address from contacts UNION ALL select alt_address_street a

Categories : Mysql

Create mysql function
Try: DELIMITER $$ DROP FUNCTION IF EXISTS getOnlineUserTraffic$$ CREATE FUNCTION getOnlineUserTraffic(x1 VARCHAR(32)) RETURNS FLOAT DETERMINISTIC BEGIN DECLARE dist FLOAT; SET dist = (SELECT UsedCreditTraffic FROM uonlineuser WHERE UserName=x1); RETURN dist; END$$ DELIMITER ; SQL Fiddle demo

Categories : Mysql

Running SQL queries with JOINs on large datasets
I believe that answer has already been given in this post: The total number of locks exceeds the lock table size ie. use a table lock to avoid InnoDB default row by row lock mode

Categories : Mysql

Query that returns only values when all its products are in a specific condition
As the comments pointed out, you are looking for the greatest N items per group. In this case, You want to get the earliest item in each group, which you can do like this: SELECT * FROM myTable m WHERE( SELECT COUNT(*) FROM myTable mt WHERE mt.list = m.list AND mt.created <= m.created) <= 1; Then, all you will have to do is filter for the rows where the location starts with A. You c

Categories : Mysql

Interpret Symfony's ACL's schema
There is only one column that contains the actual permissions, and that is mask. This column contains a bitmask, which is called the "permission mask", which is stored as an integer, which represents the cumulative permissions in the ACE. Other columns can influence if permission is granted: ace_order is used to determine which ACE is checked first. If the ACE's is applicable, it will be used to

Categories : Mysql

INSERT INTO...ON DUPLICATE KEY UPDATE Increment Syntax
The expressions in the ON UPDATE clause cannot refer to columns from the SELECT. But they can refer to VALUES(amount) to get the value that you tried to insert on a given row. INSERT INTO points_1_day (nick, amount) (SELECT nick, SUM(amount) as increment FROM points_log WHERE dt >= NOW()-INTERVAL 1 day GROUP BY nick) ON DUPLICATE KEY UPDATE points_1_day.amount=points_1_day.amount+VALUES(

Categories : Mysql

MySQL returns all rows when field=0 from SECOND Select query
I believe your issue is with implicit datatype conversions. You can make those datatype conversions explicit, to gain control. (The "trick" with wrapping a literal 0 in single quotes, that makes the literal a string literal, rather than a numeric.) In the more general case, you can use a CAST or CONVERT function to explicitly specify a datatype conversion. You can use an expression in place of a

Categories : Mysql

How to find which is the really slow query?
The best free tool to use to work with the slow query log is pt-query-digest. By default, this produces a report showing you where your aggregate response time is going, broken down by query fingerprint. I downloaded your log and ran it through pt-query-digest. Here's part of the output, which is a ranked list of queries and some statistics about how much time they take: # Profile # Rank Query I

Categories : Mysql

Create a new user without root password
You don't have to be the root user, but you must have rights to create a user. You can get those rights with the grant create user statement. Apparently, you don't have those privileges, so if you need them, you should ask your administrator. So the answer is: There is no workaround. You don't have to be root, but you must have certain privileges. In practice, if you need an extra user, the adm

Categories : Mysql

Match Against Left Outer Join
You're missing the WHERE keyword before conditions that aren't part of the join: SELECT p.product_id,p.account_id,i.image_id,a.email,p.title,p.price FROM products AS p LEFT OUTER JOIN products_images AS i ON p.product_id = i.product_id AND i.featured=1 AND i.deleted=0 INNER JOIN accounts AS a ON p.account_id = a.account_id WHERE MATCH(p.title) AGAINST('+images')

Categories : Mysql

Insert values into SQL column with mysql-python
Try This: vals_list = ['aa','bb','cc','dd','ee'] for i, j in enumerate(vals_list): self.cursor.execute(("UPDATE test.name SET TopAdd = '%s' WHERE nameid = %s" % (str(j),int(i+1))

Categories : Mysql

How to count total id that answering a quiz in sql
You need a left join of Quiz to studentassessment to make sure that all data from Quiz is shown along with the count. select q.quizNum, q.quizName, count(sa.stuNum) as attempedstudent from Quiz q left join studentassessment sa on sa.quizNum = q.quizNum group by q.quizNum;

Categories : Mysql

How do I populate timezones for Google Cloud SQL (from Windows)?
Two options. Option 1: You can use mysql_tzinfo_to_sql /usr/share/zoneinfo to get the file. The file just contains a bunch of sql statements to populate the timezone table. Then you can pipe it to your windows mysql remotely. Option 2: You can launch a local mysql server on your local windows machine. Download the prepackaged mysql time zone tables and populate your local mysql server. After tha

Categories : Mysql

Select news, join on multiple tags
You can use aggregation with a having clause: SELECT n.id FROM news INNER JOIN news_tag_cross ntc ON ntc.newsid = n.id INNER JOIN tags t ON t.id = ntc.tagid GROUP BY n.id HAVING SUM(t.name = 'Dealer') > 0 AND SUM(t.name = 'Client') > 0; There are other ways to express this (notably using joins). I like this method because the having clause can be quite flexib

Categories : Mysql

SQL On Server: Saving Query Statements to a File
You can use SET profiling = 1; to turn the mySql profiler on Then you can use SHOW PROFILES to list recently run SQL Statements http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

Categories : Mysql

Rails: retrieve data from multiple tables without association
A very simple approach is to add a method in your Trip model that returns all the trip's parts. Something like this: def parts (flights + lodgings + rentals + activities).to_a end and then in your view iterate over this parts array: @trip.parts.each do |part| # your code here end UPDATE: In that case, you can create a hash that has all the information you want for every part: def par

Categories : Mysql

Laravel: Querying based on Input. If input is empty, get all
You can build the query on forehand, store it in a variable and use it once its build. $query = isset($var) ? $var : ''; $query .= isset($othervar) ? $othervar : ''; whereBetween(*)->orderBy(*)->whereRaw($query) Only thing you need to keep in mind is to insert the 'OR's in the right place . So have like a check for wether it is the first thing to be inserted or not, if not then put 'OR'

Categories : Mysql

MYSQL Workbech connection to local network database?
I don't think that you can connect to a computer of your network with its name only in MySQL Workbench. So, if you can't allocate a fixed IP with DHCP to your computer running MySQL maybe you could use port forwarding in your router config (localhost:port -> computer2_ip:mysql_port) so you will be able to connect to localhost:port in MySQL Workbench. You could also create a tunnel between the two

Categories : Mysql

Delete a record from table2 after fetched into table1 in sas
Try this. Use left join to get the result and Isnull to replace missing values with dot(.) SELECT a.[Key], a.p1, a.calls, Isnull(b.p2, '.') -- For MYSQL change ISNULL to IFNULL() FROM table1 a LEFT JOIN table2 b ON a.calls = b.calls AND a.p1 <> b.p2

Categories : Mysql

Remove garbage from database
It's not clear, what you're asking. This is probably the best answer i can give you without more infos. Remember it's mostly bad approach to identify rows on comparing long strings. The best action would be to create md5 of this string and then find it in row and then delete it. $string_hash = md5('string i dont want to'); DELETE FROM table WHERE MD5(item) = $string_hash OR alternatively (n

Categories : Mysql

Can't get load_file() working in mysql
Typical ... been looking for 2 days and 5 minutes after posting it here i figured it out. The file to be loaded, not just the folder it's located in must have execute ... EVERY folder in it's path up to "/" must have execute!

Categories : Mysql

Select average from join MYSQL
Consider the following. How does this result differ from the desired result? +-----+------------+-------------+----------+------------+--------------+-----+-------+-----+--------+ | uid | name | description | url | picurl | mapurl | pid | price | rid | rating | +-----+------------+-------------+----------+------------+--------------+-----+-------+-----+--------+ | 5 | Havan

Categories : Mysql

Simulating lag function on MySQL
Ok, so after some more digging, and the suggestion by @Marc B, I finally got to where I wanted. In case anyone else needs help, here's my answer: set @price = (select preconormal from precos where codigowine='10088' order by timestamp asc limit 1); On the line above I'm setting @price to the first record instead of zero, to avoid getting a lag_price of 65.60 on the first row. Below is the actu

Categories : Mysql

Authentication mysql to .Net
This is a common error when some kind of deadlock occurs on the database more commonly a connection has been open a long time and gets shut down at the database end even though it is still in-use in your web application. In my code, I used a suggestion from Microsoft, that retries the database command if it fails. The link is here: http://social.technet.microsoft.com/wiki/contents/articles/4235.

Categories : Mysql

Why does this MySQL XOR query return 0?
SELECT CONVERT((CONV('b4124b0d195b2507', 16, 10)), SIGNED) ^ CONVERT((CONV('eae26aebf1f139f9', 16, 10)), SIGNED) is what you want The conv as per the docs http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv Returns a string representation of the number N, converted from base from_base to base to_base You need to convert back to numbers to xor

Categories : Mysql

PHP update query with syntax error
This is your query: update apiStreetCheckGeneral set (BBAverageSpeed, BBSuperFastBBAvailable, BBCommentary) values ('$averagespeed', '$superfast', '$bbcommentary') where PostCode='".$values['PostCode']. I am not aware of update syntax that uses a column list or values. Set each one individually: update apiStreetCheckGeneral set BBAverageSpeed = '$averagespeed', BBSuper

Categories : Mysql

Wordpress wpdb->query problems with importing data
Try reworking the logic, something like: profile.php <?php global $wpdb; $user_ID = get_current_user_id(); echo $user_ID; //First DB query $row1 = $wpdb->get_results("SELECT * FROM ziekbeter WHERE person = $user_ID AND healthy IS NULL"); //Second DB query $row2 = $wpdb->get_results("SELECT healthy FROM ziekbeter WHERE person = $user_ID AND healthy IS NOT NULL"); // I.e. greater than

Categories : Mysql

Sql- how to use relational field in condition
$command = Yii::app()->db->createCommand() ->select(*) ->from('paymentDetails') ->where('payment_date=date') ->andWhere('employee.designation.desig_id=2')

Categories : Mysql

Importing XML into phpMyAdmin using SQL
One thing I notice is missing is the ROWS IDENTIFIED BY command. MySQL assumes an XML file uses the Node for row and the Name attribute of Field for a column. By putting the column names in parenthesis, you're defining the columns, but MySql does not know where one row ends and the next one starts. If you add ROWS IDENTIFIED BY '<record>' Then it should work.

Categories : Mysql

Group_Concat with left join is slowing down. I need all rows in left table as well
This is your query: SELECT a.id, a.firstname, a.lastname, a.state, a.mobile, a.lead_description, GROUP_CONCAT(n.note SEPARATOR ', ') as notes FROM applicant a LEFT JOIN note n ON a.id = n.applicant_id WHERE delete_type = 0 AND a.type = 0 GROUP BY a.id ORDER BY a.id DESC You should start with indexes. I would recommend applicant(type, id) and note(applicant_id). And including

Categories : Mysql

MySQL: Strange AUTO_INCREMENT
When you change the auto increment it is set to greatest(your_value,max(column)+ 1) though I cant find the part in the docs which mention it, it is in the comments http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html ALTER TABLE text AUTO_INCREMENT = 1; then check the result of SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DatabaseName' AND T

Categories : Mysql


Recently Add
find original mysql query
MySql: Concatenate 2 columns if condition is met
Select several max types for each datatype per distinct value in mysql
Best Datatype For A Collection
How to insert Lat and Lng to point type column in mysql with laravel?
mysql wont install in CentOS due to conflict with mariadb
Group by hour showing breakdown for each day within date range MYSQL
MySQL - extract number from data field using SUBSTRING
SQL Query Subquery structure
Mysql query to get addresses
Create mysql function
Running SQL queries with JOINs on large datasets
Query that returns only values when all its products are in a specific condition
Interpret Symfony's ACL's schema
INSERT INTO...ON DUPLICATE KEY UPDATE Increment Syntax
MySQL returns all rows when field=0 from SECOND Select query
How to find which is the really slow query?
Create a new user without root password
Match Against Left Outer Join
Insert values into SQL column with mysql-python
How to count total id that answering a quiz in sql
How do I populate timezones for Google Cloud SQL (from Windows)?
Select news, join on multiple tags
SQL On Server: Saving Query Statements to a File
Rails: retrieve data from multiple tables without association
Laravel: Querying based on Input. If input is empty, get all
MYSQL Workbech connection to local network database?
Delete a record from table2 after fetched into table1 in sas
Remove garbage from database
Can't get load_file() working in mysql
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.