subquery multiple rows
hi all... i have a bit of a problem with this: table products: -- prod | category | -| boots | winter| boots | summer | boots | spring | shoes | spring | shoes | winter| shoes | fall | shoes | summer | -- when i do this: select distinct prod as m, (select category from products where email = m) as n from products; i get: ERROR 1242 (21000): Subquery returns more than 1 row i know that the subquery returns more than one rows. i hope so... what i'd like to see as result is: - m | n | - boots | winter, summer, spring | shoes | spring, winter, fall , summer | - or at least: --- m | n | --- boots | 3 | shoes | 4 | thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: subquery multiple rows
On Wed, 2010-04-07 at 11:37 -0400, kalin m wrote: hi all... i have a bit of a problem with this: table products: -- prod | category | -| boots | winter| boots | summer | boots | spring | shoes | spring | shoes | winter| shoes | fall | shoes | summer | -- when i do this: select distinct prod as m, (select category from products where email = m) as n from products; i get: ERROR 1242 (21000): Subquery returns more than 1 row i know that the subquery returns more than one rows. i hope so... what i'd like to see as result is: - m | n | - boots | winter, summer, spring | shoes | spring, winter, fall , summer | - I think you want: select prod as m, group_concat(category) as n from products group by prod; hth Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: subquery multiple rows
I think you want to do something like this: select prod, group_concat(category separator ', ') from products group by prod; Hope this helps. On Wed, Apr 07, 2010 at 08:37:04AM -0700, kalin m wrote: hi all... i have a bit of a problem with this: table products: -- prod | category | -| boots | winter| boots | summer | boots | spring | shoes | spring | shoes | winter| shoes | fall | shoes | summer | -- when i do this: select distinct prod as m, (select category from products where email = m) as n from products; i get: ERROR 1242 (21000): Subquery returns more than 1 row i know that the subquery returns more than one rows. i hope so... what i'd like to see as result is: - m | n | - boots | winter, summer, spring | shoes | spring, winter, fall , summer | - or at least: --- m | n | --- boots | 3 | shoes | 4 | thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=nsulli...@cappex.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: subquery multiple rows
yea.. almost. but it helped a lot. now i know about those functions too. thank you... Nathan Sullivan wrote: I think you want to do something like this: select prod, group_concat(category separator ', ') from products group by prod; Hope this helps. On Wed, Apr 07, 2010 at 08:37:04AM -0700, kalin m wrote: hi all... i have a bit of a problem with this: table products: -- prod | category | -| boots | winter| boots | summer | boots | spring | shoes | spring | shoes | winter| shoes | fall | shoes | summer | -- when i do this: select distinct prod as m, (select category from products where email = m) as n from products; i get: ERROR 1242 (21000): Subquery returns more than 1 row i know that the subquery returns more than one rows. i hope so... what i'd like to see as result is: - m | n | - boots | winter, summer, spring | shoes | spring, winter, fall , summer | - or at least: --- m | n | --- boots | 3 | shoes | 4 | thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=nsulli...@cappex.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SHOW TABLE STATUS
hi all,
in MySQL 4.1.x i want to obtain the status of more tables with one only query.
In 5.x i use SHOW TABLE STATUS WHERE Name IN ('tab_1', tab_2, 'tab_3')
In 4.1.x i tried to use but it doesn't works: how to set the query?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SHOW TABLE STATUS
AFAIR, MySQL 4.x supports LIKE, e.g.
SHOW TABLE STATUS LIKE 'tab_%'
/ Carsten
spacemarc skrev:
hi all,
in MySQL 4.1.x i want to obtain the status of more tables with one only query.
In 5.x i use SHOW TABLE STATUS WHERE Name IN ('tab_1', tab_2, 'tab_3')
In 4.1.x i tried to use but it doesn't works: how to set the query?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to View MySQL Temp Files and Temp Tables in Linux
Below is my MySQL Server's status and configuration. But I can not see anything under /tmpfs/, it is showing empty to me. here is the result of ls -al drwxrwxrwx 2 mysql mysql60 Apr 7 17:43 tmpfs Could anyone tell me how to check the status of temp files and temp tables on disk? Thanks. mysql SHOW global STATUS LIKE 'created_tmp%'; +-+---+ | Variable_name | Value | +-+---+ | Created_tmp_disk_tables | 4682 | | Created_tmp_files | 291 | | Created_tmp_tables | 10997 | +-+---+ mysql SHOW variables LIKE '%tmp%'; +---+--+ | Variable_name | Value| +---+--+ | bdb_tmpdir| /tmpfs/ | | max_tmp_tables| 32 | | slave_load_tmpdir | /tmpfs/ | | tmp_table_size| 67108864 | | tmpdir| /tmpfs/ | +---+--+
Re: MyISAM better than innodb for large files?
Also depends on your data access pattern as well. If you can take advantage of clustering my primary key for your selects, then InnoDB could do it for you. My suggestion would be to write some queries based on projected workload, build 2 tables with lots and lots of data, and do some isolated testing. For work, I do a lot of query profiling using maatkit. Be sure to clear out as much of the caching as possible including the OS cache. On Mon, Apr 5, 2010 at 7:25 AM, Jan Steinman j...@bytesmiths.com wrote: From: Gavin Towey gto...@ffn.com InnoDB should be your default for all tables, unless you have specific requirements that need myisam. One specific example of an appropriate task for myisam is where you need very high insert throughput, and you're not doing any updates/deletes concurrently. A couple other things: InnoDB does relations better, MyISAM does search of text fields. If we can control fuel we can control the masses; if we can control food we can control individuals. -- Henry Kissinger Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=kykim...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
problem reading array data
I need to read an array's content using a while loop and inside the loop
read another array's content. Using the variables from the two arrays I need
to execute a query. My problem is the inner while loop reads all records of
the array whereas the outer while loop exits after reading the first record.
That is after execution of the inner while loop finishes, the control does
not move to the outer while loop to read the next array element.
I am appending my code below. Please help me solve this problem
$arrdata = mysql_query(SELECT OldDeptCode, MajorCode FROM
tblolddeptcodemajorcode);
$result2 = mysql_query(SELECT SSN, DeptCode, ActCode FROM
tblapprovedactivitydetail);
while($info = mysql_fetch_assoc($arrdata))
{
$OldDeptCode = $info['OldDeptCode'];
$MajorCode = $info['MajorCode'];
while($row2 = mysql_fetch_assoc($result2))
{
$SSN = $row2['SSN'];
$DeptCode = $row2['DeptCode'];
$ActCode = $row2['ActCode'];
$query = INSERT INTO test1 (SSN, MajorCode, ActCode) VALUES
('$SSN', '$MajorCode', '$ActCode');
if($OldDeptCode != 'COAS' $OldDeptCode != 'CSS' $OldDeptCode
!= 'EC' $OldDeptCode != 'EECS' $OldDeptCode != 'FW' $OldDeptCode !=
'GEO' $OldDeptCode != 'SED' $OldDeptCode != 'VM' $OldDeptCode ==
$DeptCode)
{
mysql_query($query);
}
}
echo done;
}
echo all done;
Thank you
Mysql - Tables Export to Excel!
Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? Please guide me. Thank you VIKRAM A
