mysql subquery

kdelover · 12-03-2010, 09:25 AM

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

Guttorm · 12-03-2010, 10:59 AM

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';

thesnow · 12-03-2010, 12:49 PM

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;

kdelover · 12-03-2010, 02:28 PM

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.

kdelover · 12-04-2010, 04:14 AM

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)

thesnow · 12-04-2010, 07:59 AM

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?