Hot answers tagged

20

Typically when using UNION and you need to know what table a specific row comes from, you'd use a hard-coded value stored in a column similar to: SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source FROM tablename1 where Active =1 union SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as ...


3

You can try this with UNION ALL as probably you need duplicate entry too as will adding table name it will be unique and performance will be also improved SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as TableName FROM tablename1 where Active =1 UNION ALL SELECT Id, productName, Largeimagepath, Discount, Price, Image, '...


3

The GROUP_CONCAT allows this form of transformation: SELECT register_cr.id, register_cr.first_name, register_cr.father_name, register_cr.surname, register_cr.address, GROUP_CONCAT(DISTINCT cr_record.charge SEPARATOR "\n"), GROUP_CONCAT(DISTINCT cr_record....


3

The column that you have is a VARCHAR so the ordering applied by the RDBMS will be alphabetical instead of numeric. What is the smallest possible unit of storage for a HDD these days? GB? One way to solve your problem would be to create another column that transforms the storage into GB (or some other unit). Make sure that this column is integer or numeric ...


3

Perhaps this can lead to some SELECTs to solve your problem? For the 'ana_index': mysql> SHOW GRANTS FOR ana_index@localhost; | GRANT USAGE ON *.* TO 'ana_index'@'localhost' IDENTIFIED BY PASSWORD '*...' | GRANT ALL PRIVILEGES ON `test`.* TO 'ana_index'@'localhost' These tables were populated: mysql> SELECT * FROM mysql.user WHERE user = 'ana_index'...


3

SHOW GRANTS only gives you back whatever you are connected as, which was root@localhost. root@localhost and root@'%' and completely different users. Just do SHOW GRANTS FOR root@localhost; SHOW GRANTS FOR root@'%'; root@localhost lets you connect from the DB Server via mysql.sock (the socket file) root@'%' lets you connect via TCP/IP, but you must ...


3

Something like this would give you the total across all three of those days: SELECT COUNT(attendance.AttendanceID) as 'Total Attendance' FROM student INNER JOIN attendance ON attendance.StudentID = student.StudentID AND attendance.Date IN ('01/04/2015', '02/04/2015', '05/04/2015') WHERE student.WorkshopID = '101' If you wanted the total separately for ...


3

Wrap the selects into parentheses (SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) = EXTRACT(month FROM (NOW())) ORDER BY MONTH(`Date`) ASC ) UNION (SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) ORDER BY MONTH(`Date`) ASC)


2

SELECT p.*, (SELECT count(*) FROM Comment c WHERE c.p_ID = p.p_ID) CommentCount FROM Post p WHERE p.p_ID = 8; In this case a function isn't necessary because the nested query will always return a number even if the number is zero. If you want to see the output for all accounts simply remove the WHERE clause.


2

This will get all posts and their comment counts SELECT p.*,IFNULL(c.comcount,0) comment_count FROM post p LEFT JOIN (SELECT p_id,COUNT(1) comcount FROM comment GROUP BY p_id) c USING (p_id); This will get post for p_id=8 and its comment count SELECT p.*,IFNULL(c.comcount,0) comment_count FROM (SELECT * FROM post WHERE p_id=8) p LEFT JOIN (SELECT p_id,...


2

1GB, 100TB, etc are just strings of text with no arithmetic meaning regarding which is greater. I'm assuming your column only has ONE value, like 1GB or 500GB, and not all of those values I think you'd have to attempt something like using REPLACE(hdd,'GB',000000) or REPLACE(hdd,'TB',000000000) and using numeric comparisons in your between. It's definitely ...


2

MAMP seems to handle the root username differently than WAMP. I had to include the root password as 'root' in my PHP script, instead of leaving it blank, as such: $link = mysqli_connect('localhost', 'root', 'root');


2

You are getting this error because the root account on your local machine doesn't have a password. That's why you can login locally with using password: NO Probably your server on godaddy has a password set, or the root account disabled. You need to fix the credentials in your application (and probably use a login other than root)


2

There is few ways to do it as per below- 1) partitioning but you don't want it. Also need to remember that partitioning does not support with foreign keys. 2) You can normalize your table if there is duplicate values in table. 3) If this table contains transactional data or logs then you can move old data in historical tables year wise etc if possible in ...


2

You're getting all the students at once and just displaying the table, without any subselection. Your second SQL Statement here: <?php $sql1 = "SELECT std_result.id,std_result.SubjectId,std_subject.Subject,std_result.TermOneWritten,std_result.TermOneOral,std_result.TermTwoWritten,std_result.TermTwoOral FROM std_result INNER JOIN std_subject ON ...


1

You need to change the COUNT() so it only counts the child records (and not always the master). SELECT Activity.name, COUNT(Notifications.ActId) from Activity LEFT JOIN Notifications on Notifications.ActId = Activity.ActId GROUP BY Activity.name COUNT() won't count NULL values (from your LEFT JOIN), so you will only count records from the Notifications ...


1

As explained in the documentation, there are rules on what makes a valid identifier. This rule is what affects your case: Identifiers may begin with a digit but unless quoted may not consist solely of digits. So, as @ypercube says, you need to quote your table name, either with double quotes or backticks: SELECT * FROM "2521118" ORDER BY id DESC LIMIT ...


1

If you want to DELETE all the information in table1.*: DELETE table1.* FROM test.table1 JOIN test.table2 ON (table2.id = table1.id) WHERE table1.price < (table2.price*2); This will DELETE all rows from table1 that match with the WHERE criteria. If you want to DELETE all the rows that match against the WHERE criteria in table2, just replace DELETE table1....


1

No. Don't put multiple values in a column; make a new table: Mapping table -id -- same as the `id` in transaction_tbl -ref_number PRIMARY KEY(id, ref_number) For your example, there will be 3 rows in this table for the 1 row in transaction_tbl. INSERT INTO transaction_tbl (name, descripton) VALUES (..., ...) $id = LAST_INSERT_ID() INSERT INTO mapping (id,...


1

You can do it by running the following query: SELECT a.sender,a.status,a.type,c.p_id,c.p_name,c.st FROM a INNER JOIN b ON a.sender=b.sender INNER JOIN c ON b.p_id = c.p_id WHERE a.status='approved' AND c.st='current';


1

SET foreign_key_checks = 0; //import data SET foreign_key_checks = 1;


1

You could write a simple query using OR in WHERE clause and passing your search string. SELECT school_name FROM schools WHERE Subject1 = 'Physics' OR Subject2 = 'Physics' OR Subject3 = 'Physics' OR Subject4 = 'Physics' You can parameterize the search string and pass on to this query.


1

Use your application code to extract "phrases" and store them in a table. Then do SELECT ... GROUP BY ... to generate the counts in question. No, it is not practical to do this entirely in SQL. SQL is limited in its text parsing ability.


1

Here is the table structure CREATE TABLE `users` ( `userid` INT NOT NULL AUTO_INCREMENT, `user_name` VARCHAR(50) NOT NULL, user_type int 1, `email` VARCHAR(100) NOT NULL, PRIMARY KEY (`userid`) ); CREATE TABLE `user_type` ( `user_typeid` INT NOT NULL AUTO_INCREMENT, `user_type` VARCHAR(50), PRIMARY KEY (`user_typeid`) ); CREATE TABLE `...


1

Going off the links that you provided, I would suggest looking into PHP's PDO (PHP Data Object). The reason for this is because the mysqli way is getting pretty old at this point. PDO is relatively simple to use and it has different drivers for different database vendors. So if you want to switch out your database system for something else, then it is ...


1

In theory it's possible. What you call a table in InnoDB is a B+tree index named PRIMARY. The key in the PRIMARY index is the primary key field(s). All secondary indexes are B+tree indexes, too. The key would be the secondary index field(s). The value is the primary key. (See my slides to get more details http://www.slideshare.net/akuzminsky/indexes-in-my-...


1

Since you said you can only use the Command prompt, my suggestion is to shift the character set to one that supports Arabic within the mysql client: I just ran these commands on my laptop at home (Windows 8.1) mysql> select * from information_schema.character_sets where description like '%arabic%'; +--------------------+----------------------+-----------...


1

First backup the database. Sometimes character conversions can give unexpected results. I have had good success using these instructions: SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') INTO OUTFILE '/tmp/altertablesstatements.sql' FROM information_schema.tables WHERE table_schema='<database-...


1

Ok, i 've found the solution and i want to share it with you. SELECT * FROM tbl_name WHERE dt_column < DATE_SUB(NOW(), INTERVAL 20 MINUTE) This brings all entries were last login was more than 20 mins in the past.


Only top voted, non community-wiki answers of a minimum length are eligible