LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 12-03-2010, 09:25 AM   #1
kdelover
Member
 
Registered: Aug 2009
Posts: 311

Rep: Reputation: 36
mysql subquery


Hi guys,

I need to get some info from DB,where the column name would be the name of a day. Please see this:

Code:
mysql> select Friday from home where user='alex';
+---------+
| Friday  |
+---------+
| 205.44 |
+---------+
1 row in set (0.00 sec)

The above query returns the home directory size of user alex for friday. Now if i do this:

Code:
mysql> select (select dayname(curdate())) from home where user='alex';
+--------+
| DATE   |
+--------+
| Friday |
+--------+
1 row in set (0.00 sec)
I was expecting the above query to return the same value as above since id have to do something like this in a script. Thanks
 
Old 12-03-2010, 10:59 AM   #2
Guttorm
Senior Member
 
Registered: Dec 2003
Location: Trondheim, Norway
Distribution: Debian and Ubuntu
Posts: 1,353

Rep: Reputation: 367Reputation: 367Reputation: 367Reputation: 367
Hi

select dayname(curdate()) returns "Friday" (on Fridays).

So you are basically doing:

select 'Friday' from home where user='alex';

It's valid, but doesn't make much sense, it will always be the same as:

select 'Friday';
 
Old 12-03-2010, 12:49 PM   #3
thesnow
Member
 
Registered: Nov 2010
Location: Minneapolis, MN
Distribution: Ubuntu, Red Hat, Mint
Posts: 172

Rep: Reputation: 56
Something like...?

Sample data (guessed):
Code:
+--------+----------+--------+------+
| Friday | Saturday | sunday | user |
+--------+----------+--------+------+
| fri    | sat      | sun    | alex |
+--------+----------+--------+------+
Query definition:
Code:
set @qry2 = concat('select ',dayname(curdate()),' from home where user=\'alex\'');
prepare stmt2 from @qry2;
execute stmt2;
Output:
Code:
+--------+
| Friday |
+--------+
| fri    |
+--------+
Cleanup:
Code:
deallocate prepare stmt2;
 
Old 12-03-2010, 02:28 PM   #4
kdelover
Member
 
Registered: Aug 2009
Posts: 311

Original Poster
Rep: Reputation: 36
Thanks i'll try that out.But i'd like to know how can i get the output with our using CONCAT? i believe what i did in my previous post using subquery should work.
 
Old 12-04-2010, 04:14 AM   #5
kdelover
Member
 
Registered: Aug 2009
Posts: 311

Original Poster
Rep: Reputation: 36
Dont work mate

Code:
mysql> select CONCAT('',dayname(curdate())) from home where user='alex';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT('select','dayname(curdate())') from home where user='alex'' at line 1
mysql>

Code:
mysql> select CONCAT('',dayname(curdate())) from home where user='alex';
+-------------------------------+
| CONCAT('',dayname(curdate())) |
+-------------------------------+
| Saturday                      |
+-------------------------------+
1 row in set (0.00 sec)
 
Old 12-04-2010, 07:59 AM   #6
thesnow
Member
 
Registered: Nov 2010
Location: Minneapolis, MN
Distribution: Ubuntu, Red Hat, Mint
Posts: 172

Rep: Reputation: 56
Yeah, it wouldn't work, that's not the solution I posted and gives exactly what you started with. Is there a reason you need to do it with a subquery when other methods work?
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL: Help with using join to simulate subquery MicahCarrick Programming 1 09-30-2006 07:17 AM
MySQL delete with subquery firemankurt Programming 2 09-30-2006 06:02 AM
Subquery mysql dont work cuencano General 1 08-03-2005 12:30 AM
MySQL: Subquery in HAVING clause ugenn Programming 2 08-11-2002 11:49 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 11:41 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration