How Do I Rewrite A Slow Subquery Into A Fast Join? (Prolly Real Easy For A Non-noob To Answer)

Slow Join On Otherwise Fast Views?
select * from vw_fielddata

takes 16ms to return just four rows, why does

select
*
from
vw_fielddata firstname
join
vw_fielddata secondname
on
secondname.AccountID = firstname.AccountID

take over an hour? The views themselves contain joins on tables and further views, and although AccountID isn't a primary key from the tables it's gathered from it is a foreign key in them (I assume this makes it indexed?).

View Replies !

---

Slow Answer When Using ORDER BY
I have two tables and want to return artists that begins with A,B,C and D

If I use ORDER BY it takaes about 10 times longer to execute compare to not using ORDER BY.

SELECT medialib.guid, medialib.artist, medialib.title
FROM songstation
INNER JOIN medialib ON ( songstation.songguid = medialib.guid )
WHERE (
songstation.stationguid = 'ffeeaab6-9558-4c85-9fc8-27e962d47ec7'
)
AND (
medialib.artist LIKE 'A%'
OR
medialib.artist LIKE 'B%'
OR
medialib.artist LIKE 'C%'
OR
medialib.artist LIKE 'D%'
)

ORDER BY medialib.artist

LIMIT 0 , 30

I have tested BETWEEN, but the result is the same, slower when using ORDER BY.

View Replies !

---

How To Rewrite This Query Without A Subquery ?
For speed reasons, I would like to rewrite the following query without the subquery: ...

View Replies !

---

Innodb Fast, Then Very Slow
I have a massive table that I converted to innodb. It made a huge speed difference in my site because tables locks basically stopped happening.

However, after about 10 minutes mysql slows to a crawl. All queries seem to take forever to run, like 20-30 seconds each. If I reset mysql its all fast again for another 10-15 mins.

This only happens when I have that one table set to innodb. When everything is MyISAM the database speeds are consistent, I just get a lot of table locks (which I'm trying to reduce).

View Replies !

---

Slow Insert On My PC But Fast On My Laptop?
I'm just wondering what can be the cause of slow inserts on my PC but super fast on my Laptop?

I'm using MySql x64 on both PC. Operating System should be also identical as I mirrored it between these two PCs but PC would have more programs running.. Settings on my MySql should be identical unless there's more than just Server Instance Config Wizard.

My PC is definately faster in terms of CPU and Hard drive compared to my Laptop. But insertion on laptop was like 10x faster.


View Replies !

---

Fast In INTERNET, Slow In Wifi
- Internet connected to mysql: all ok and fast

- Wifi or lan connected to mysql: all fast, except UPDATE, INSERT, DELETE, that works in about 5-10 seconds.

How is possible that UPDATE, INSERT or DELETE registres is slow in network connection?

View Replies !

---

Fast SELECTs Versus Slow INSERTs With Indexes
I run a MySQL database using phpMyAdmin interfaced with an application written in C++, and most of my nine database tables have 500,000 - 2,000,000 records in them. These tables also have about five-to-six indexes each. This makes working with the C++ application extremely fast, which is nice. Any SELECT statements can be run in less than a second, and I'm very happy. The problem comes when I have to insert new records into the tables. Every morning, I am received a pipe-separated .txt file of the previous day's new records (about 500 - 1,000) that I have to insert into the tables using my C++ application. Well, this takes an excruciatingly long time (almost an hour, actually) and I need to find some way to make it run faster. I am thinking of several options here:

View Replies !

---

How Can I Rewrite This Join In Mysql 3.2?
I am trying to select all the fields from ACS and at the same time
check if in ACC there is a certain link to another table. This should
be expressed in the counter field. However, the query below doe not
work as it only selects ACS rows without any link in ACC (is null) or
ones with a link through to ACT.

Here is the sql:

SELECT `ACS` . * , COUNT( `ACC`.`ID` ) counter
FROM `ACS`
LEFT JOIN `ACC` ON `ACS`.`ID` = `ACC`.`ACS_ID`
WHERE
`ACC`.`ID` IS NULL OR
`ACC`.`ACT_ID` = "480"
GROUP BY `ACS`.`ID`
ORDER BY counter DESC , `NAM`

ACC has the following fields:
ACS_ID = link to ACS
ACT_ID = link to ACT

Right now, it only give me 13 rows - while ACS has about fifty rows in
it.

View Replies !

---

Slow Subquery
Can anyone tell me why the following query with sub-query takes forever to finish? (I've le it run for 20 minutes, and it still hasn't finished)

select date from temps where date in (select distinct date from observations where camera like "a")

The sub query returns 10 dates. The outer query is on a table that contains about 40,000 rows. What's the big deal here? All I'm trying to do is select rows from "temps" that match a small range of 10 dates. Is there another way to do this? Is a sub-query the wrong approach?

View Replies !

---

Another Easy(for You, Hard For Me) Select/join
Table 1 indexed on ID.
Table 2 is a chat log. Colums: From, To

I'd like to find out who is NOT chatting.
So, I'd like to select all ID in Table1 that are NOT found in either column From or To in Table2

So far I'm using two selects, but this doesn't seem right.

View Replies !

---

Subquery As Join
I have the following query, which works perfectly on my developpement computer:

UPDATE writings SET num_votes=(SELECT COUNT(vote) FROM votes WHERE writing_id=id), rating=(SELECT AVG(vote) FROM votes WHERE writing_id=id);

Unfortunately, my host doesn't seem to allow subqueries. Is there a way to do this with joins? I've heard that joins are faster anyhow.

View Replies !

---

Subquery To JOIN
SQL Code:

Original
- SQL Code

SELECT
COUNT(*)
FROM `cmn_group`
AS `g`
INNER JOIN `cmn_company`
AS `c`
ON g.cmn_company_id = c.cmn_company_id
WHERE g.cmn_group_id !=5
AND g.group_name = 'G4'
AND g.cmn_company_id =
(
SELECT cmn_company_id
FROM cmn_group
WHERE cmn_group_id =5
)

 SELECT COUNT(*)FROM `cmn_group` AS `g`INNER JOIN `cmn_company` AS `c` ON g.cmn_company_id = c.cmn_company_idWHERE g.cmn_group_id !=5AND g.group_name = 'G4'AND g.cmn_company_id =( SELECT cmn_company_id FROM cmn_group WHERE cmn_group_id =5)

View Replies !

---

INNER JOIN With SubQuery
explain of INNER JOIN (SELECT .....) as alias name ON t1.ID = t2.ID with example. How the INNER JOIN consider that SELECT?

View Replies !

---

Convert Subquery To JOIN
I have a working subquery:Code:

SELECT ID, company_name, logo_file_name FROM company ORDER BY (SELECT 1 FROM company as inside WHERE TRIM(logo_file_name) != '' AND ID = company.ID) DESC, company_name ASC

It grabs all of the company rows, and puts the ones that have a logo file name at the top.This query works fine on my testing machine (mysql 4.1.7).
However, my production machine only has mysql 4.0.18 and I have no way of upgrading it to 4.1 to get subquery support. I've been looking at trying to convert it using a JOIN statement, but I'm stumped.

View Replies !

---

Which One? JOIN, Subquery, UNION?
I am having trouble with a select statement. Here's what I want to do:

tbl1 is a list of photo albums, each with an 'album_id'.
tbl2 is a list of photos, with a column specifying the 'album_id' of the photo album it belongs to.

I want to SELECT a list of albums, and append to that list a column containing the number of photos in that particular album (determined by counting the number of photos that have an 'album_id' matching each album). How should I do this?

View Replies !

---

Help With LEFT JOIN & Subquery
It seems my original plan for this query doesn't work, and I'm not sure how to change it so that it would. Any ideas? I don't think much detail is needed about the tables (if so let me know and I'll post up more info) - I basically just want to do a left join of one table with a subquery (another table, but only rows with group_id=3).

Since I'm working with MySQL 4 views/stored procs aren't an option. Is my syntax just wrong, or do I need to approach this issue differently?

My original query:

SELECT * FROM modules LEFT JOIN (SELECT * FROM permissions WHERE permissions.group_id = 3) AS perm ON modules.id = perm.module_id

View Replies !

---

Need Help Converting Subquery Into Join
I need a bit help,

Table structure
Member
------
id

Registration
------------
id
meeting_id
member_id

Using subQuery:
Select m.* from member m where m.id NOT IN ( select r.member_id from Registration where meeting_id != XXX )

Since I'm using old version of mysql v4.0 which doesnt support subquery, so that query above wont work.
Is there anyway I can rewrite that query above using (JOIN?) .. and make it work?

I tried
Select m.* from member m left join registration r on m.id = r.member_id where
r.id is null and r.meeting_id != XXX
It doesnt work because of the meeting_id constraint.


View Replies !

---

Problem With Subquery And Join Between Two Tables
I have two tables, the first is the user table (the user is unique) and the second table of experience job (the user can have more than one experience job). I need to do a SQL query where it shows the data of the user (tabal of user) and single a experience job (the one last), my problem is that I have not been able to do the query, my query shows me all the experience job of the user, but I need the one last.

I have the following query, but the problem is that it shows null the experience job column from the second user in ahead.....

View Replies !

---

JOIN/Subquery Nightmare... Please Try Your Hand At It!
Let's say you have three tables - colors, items, and item_display:

colors:

+----+---------+
| id | name |
+----+---------+
| 1 | red |
| 2 | green |
| 3 | blue |
+----+---------+
items:

+----+-----------+-------+
| id | colors_id | name |
+----+-----------+-------+
| 1 | 2 | grass |
| 2 | 1 | apple |
| 3 | 2 | apple |
| 4 | 3 | sky |
| 5 | | glass |
+----+-----------+-------+
item_display:

+----+---------+----------+-----------------------------------+
| id | item_id | item_id2 | description |
+----+---------+----------+-----------------------------------+
| 1 | 1 | | The grass is green |
| 2 | 2 | 3 | Both apples, but different colors |
| 3 | 4 | | Sky is blue during the day |
| 4 | 5 | | We'll say the glass is colorless! |
+----+---------+----------+-----------------------------------+
.SQL file here: http://dev.soulpass.com/sample.sql

Here is the desired output from the final query:

+----+--------+--------+-------+-----------------------------------+
| id | color1 | color2 | name | description |
+----+--------+--------+-------+-----------------------------------+
| 1 | green | | grass | The grass is green |
| 2 | red | green | apple | Both apples, but different colors |
| 3 | blue | | sky | Sky is blue during the day |
| 4 | | | glass | We'll say the glass is colorless! |
+----+--------+--------+-------+-----------------------------------+
The fun stuff:

Since some items (e.g. - glass) will not have any color, there will need to be an outer join between colors and items. The following query takes care of that, but does not extract the second color for records that have a second color (e.g. - apples):

SELECT item_display.id,
colors.name AS color1,
items.name,
item_display.description
FROM item_display,
items LEFT OUTER JOIN colors ON items.colors_id = colors.id
WHERE item_display.item_id = items.id;
+----+--------+-------+-----------------------------------+
| id | color1 | name | description |
+----+--------+-------+-----------------------------------+
| 1 | green | grass | The grass is green |
| 2 | red | apple | Both apples, but different colors |
| 3 | blue | sky | Sky is blue during the day |
| 4 | | glass | We'll say the glass is colorless! |
+----+--------+-------+-----------------------------------+
I think that extracting the second color will require a subquery. Here is an example of what *looks* right to me, but does not work:

SELECT item_display.id,
colors.name AS color1,
(SELECT colors.name FROM colors, items, item_display WHERE items.colors_id = colors.id AND item_display.item_id = items.id) AS color2,
items.name,
item_display.description
FROM item_display,
items LEFT OUTER JOIN colors ON items.colors_id = colors.id
WHERE item_display.item_id = items.id;
The reason it doesn't work is because the "Subquery returns more than 1 row" (to quote the error). Sooo, I think the only way to get it to work would be to somehow "tell" the subquery what the current item.id is for the parent query.

Hopefully this whole thing makes sense, but more examples and information can be provided. I think the example tables are set up the way the need to be, but I am certainly open to structural suggestions. The item_display table is purposefully linking items together rather than directly linking colors. For the sake of this example, a "red apple" is totally different from a "green apple," but they are similar enough to warrant a link when displaying all of the items along with descriptions.

View Replies !

---

Can You Left Join A Subquery In The WHERE Clause?
I am having difficulty with a fairly complex query. I want to LEFT JOIN a subquery in the where clause like this GREATLY simplified example:

SELECT t1.field1, t2.field2
FROM table1 t1,
LEFT JOIN
(select field2
FROM table2
WHERE field3=22) as t2
on t1.id = t2.t1_id

Using MySQL 4.1


View Replies !

---

Join Too Slow
I'm creating a query that use Join clause. I tested it in MySQL 4.0.24 and with MS-ACCESS. . . . in MySQL is slow!!! any suggestion ?

View Replies !

---

Can 2 Queries Be Condensed Into 1 Query Using A Subquery Or Join?
I am developing an image gallery, and storing information about the hierarchical folder structure in which the images are organised using a modified preorder tree traversal method. I have 2 main tables:

View Replies !

---

Slow Join On Large Tables
I have two tables:

D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:

SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00).

key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

View Replies !

---

Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below:

It involves two tables, products and vendors.

Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description:

CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`vendor_id` smallint(6) NOT NULL default Ɔ',
`product_code` varchar(255) NOT NULL default '',
`internal_name` varchar(255) NOT NULL default '',
`lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),
UNIQUE KEY `product_code` (`product_code`),
KEY `vendor_id` (`vendor_id`)
) ENGINE=MyISAM;
Vendors are much more straightforward:



CREATE TABLE `vendors` (
`id` smallint(6) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
The following query executes in no MORE than 0.01 seconds:


SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
FROM products as p
ORDER BY p.product_code ASC
LIMIT 0, 30;
And has the following attributes:

+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds:



SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
, v.name as vendor_name
FROM products as p
LEFT JOIN vendors as v ON v.id=p.vendor_id
ORDER BY p.product_code ASC
LIMIT 0, 30;
It has the following characteristics:

+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me.

Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so:



mysql> explain SELECT DISTINCT p.id
-> , p.product_code
-> , unix_timestamp(p.lastmodified) as lastmodified
-> , p.internal_name
-> , v.name as vendor_name
-> FROM products as p
-> LEFT JOIN vendors as v ON v.id=p.vendor_id
-> LIMIT 0,30;
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,

View Replies !

---

Slow Execution For A Left Outer Join Query
Whats likely to be the cause of slow execution for a left outer join query?

The original query joins three tables but even if I narrow it down to one it still takes a long time to execute.

$query = "select distinct materials.* from materials";
$query .= " left outer join materials_products on materials.material_id = materials_products.material_id";

There's 914 rows in the materials table and 1348 row in the materials_products table

Is it likely to take a long time for this amount of data or is there likely to be a problem in the table(s) set up or query?

View Replies !

---

Can The System Send Mail To User After Getting An Answer
Can the system send mail to user after getting an answer automatically?

View Replies !

---

Multiple WHERE X='$x' In A Query. (Quick Answer Needed)
My query works when I only use one WHERE instance, but not with both, and i know I have data that matches both criteria. Is this the proper way to pull results that match 2 criteria?

$query = "SELECT * FROM Cartridges WHERE TYPE='$TYPE_Query' & PRINTER='$PRINTER_Query' ORDER BY ID ASC";

View Replies !

---

Rewrite
How can I write this query so it works on mysql 3.54:
++++++++++++++++++++
select groupmembers.memberid,users.name from groupmembers,users where
(not users.name='root')
and groupmembers.groupid=(select groups.id from
groups where groups.type='Privileged')
and groupmembers.memberid=users.id
order by name
++++++++++++++++++++

And, it it possible to remove the case sensetivity of table names

View Replies !

---

How To Rewrite This Without Subqueries?
i have a table that contains the fields order_id, canceled, and
captured. the same order_id can appear many times in the table. i
want to retrieve all the order_ids for which NO record exists that has
both canceled and captured = N.

i am not able to upgrade my version of mysql at the moment, so i would
like to write a query that does that with no subqueries.

View Replies !

---

Redesigned Table...can't Rewrite Query.
I've just altered the structure of a table in my database to make it clearer, and to remove duplicate records that would have been stored in it. The problem is I now want to query it, and can't work out how to do it in one query. I could do it in 3, but I'd rather be efficient if possible.
The table structure is

class
classid - int(3) - auto increment - primary key
classname - varchar(25)
classparent - int(3)

The idea being that a record from another table is linked to a classid, from which we can extract the parents of that particular class. There could be varying number of levels, but the top level has a classparent of 0.
Is there a way of writing this into one query, ie. using a classid, select the values for that row, and its parent, grandparent etc.?

View Replies !

---

Noob
I have this table called "PLAYER":

num name

4 Adam
6 George
2 Grymse
1 Lorangrym
3 Michael

Nevermind the data, it's just random--I'm just self-teaching myself how to do things. I would like to change this table so that the rows display in numberical order (1,2,3,4,6) (not sure why 5 isn't there, but that's ok). Is there a function where I can swap the rows?

View Replies !

---

SQL Noob Here
I have a script, a link exchange script I coded, and it inserts a new row for each new website that submits a link. There is a field called 'hits' and it tracks all hits received from the website.

I want to like, empty all of the rows 'hits', like say ive got

Site Hits
nah.com 3447
bob.com 28
james.com 2293

How can I make it so all of the hits set to 0?

I want to reset it each week, to make it fair for people, is there a query I can write to make all hits set back to 0?

Like:

Site Hits
nah.com 0
bob.com 0
james.com 0

View Replies !

---

Noob Question
I'm working on this website and we are converting it from HTML to PHP. With that we are using MySQL to be able to automatically incorporate new media content into the website.

Here is the problem.

There are over 70k items that need an entry into a table(s) for the URL of the content (for example an image). My question is: Are there any programs out there that will simplify the process of adding this data into a table or data base with out having to hand enter tens of thousands lines of code/queries?

((Forgive me if this is posted in the wrong form, and if it is, I'll be more than happy to take it down)).

View Replies !

---

Complete Noob
I'm am an absolute beginner at this... I have done some SQL scripting for Microsoft SQL server, but beyond that have no idea what I am doing, so please bear with me. I am designing a website where users enter in votes each week for a range of categories, and then each of those votes are counted and added to a ladder on the website. I need a form for the votes to be entered in to, and then I'd like the script to update the ladder automatically. Any ideas on where I should start

View Replies !

---

Semi-noob At SQL Sorting
I am a php programmer working on a project, and i know some SQL, basically just enough to save data into one and access said data via SELECT commands.

I have a table (newsTech) and it has fields "news_id" (type = int, autoincrementing id number/ primary key), "date" (type = timestamp), "Title" (type = text), and "refid" (type = int).

As stories are added to the news table the end up at the bottom of the list. I want to sort the table in decending order by timestamp. Like i said, i know very little sql, so if somebody could please help me with the querry, that would help me out so much.

View Replies !

---

Good Noob Tutorial
I have just installed it on a Mac. I know it's somewhere (nowhere that I can see) on the system, and that's it. I don't have any (and I mean any) experience with using Terminal. I've spent half a day scouring forums, MySQL documentation, various articles, you name it, but have got to the stage (it's now after 1am) of abject despair. Everywhere it says MySQL is easy to learn, and I'm sure I can if I can just find out how to get started. All the tutorials I've looked at seem to assume you've already got it installed and configured.
I haven't been able to find a single article/user guide/tutorial that speaks to my level of knowledge (or rather, lack of it). All seem to assume you know what you're doing in Terminal and that you know what the terminology means. The MySQL documentation is a nightmare in that respect. I know I have to do something to configure the setup, and have found some articles describing what I need to enter to do that, but my problem is getting off first base. I've got the MacBook-OSX:~ username$ prompt in Terminal and no idea what to do next.

View Replies !

---

MySQL Query To Read Field, Split And Rewrite Fields
I need help reading a field from a table, splitting the contents of the field and rewriting the contents to two other fields. ....

View Replies !

---

Noob Sql: Find Related Posts
post
id
user
title
description
created_at

tag
id
post_id
tag_name
user

"post" 1:M relationship with "tag"

I don't think this is the right sql:

select post_id from tag where user=user_id and tag_name in (tag1,tag2,tag3)
inner join post
on post.id = tag.post_id

tag records:
post_id: 1
tag_name: tag1

post_id: 1
tag_name: tag2

tag would have two records for post_id=1, where as post only has 1 record with id=1.

Given a list of tags, I need to find the post that have those tags. Anyone know how I can do this? Basically what I am trying to do is, display related post.

View Replies !

---

Noob: Select Large Query
If I had to select large number of records, say 1 million from the mysql server. What problems would I be facing? MySQL connection timeout, network latency?

Anyone has done a large simple mysql select query and what problem did you face? I am not doing any joins.

View Replies !

---

Noob: Sql To Select Part Of A Text Field
I have a text field in mysql database.

record1:
id:1
text_field: apple, oranges, grapes

record2:
id:2
text_field: grapes

record3:
id:3
text_field: oranges

I need a sql to select records 1,2,3 for word "apple or oranges or grapes" in text_field.

View Replies !

---

Noob Issue With Cursor, Only Fetching 1 Row Of Data ;)
I have recently started using stored procedures in our application, and today, I have tried to create a cursor to fetch me some data, but for some reason it only fetches 1 row of data. Its a really simple cursor (and procedure) at the moment, so im really stumpped.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`task_get_task_list` $$
CREATE PROCEDURE `test`.`task_get_task_list` ()
BEGIN
DECLARE field_value VARCHAR(20);
DECLARE value_value VARCHAR(255);

DECLARE task_user_filter_cursor CURSOR FOR SELECT field , value
FROM task_user_filter_settings;

OPEN task_user_filter_cursor;
REPEAT
FETCH task_user_filter_cursor INTO field_value , value_value;
IF NOT done THEN
SELECT field_value , value_value;
END IF;
UNTIL done END REPEAT;

CLOSE task_user_filter_cursor;

END $$
DELIMITER ;
The table looks like this (well here are some SQL statments to create the table and add some dummy data)

CREATE TABLE `task_user_filter_settings` (
`task_user_filter_settings_id` int(10) unsigned NOT NULL auto_increment,
`task_user_id` int(10) unsigned NOT NULL,
`field` enum('pro','sub_pro','prior','cat','dead','status','assig_user','assig_group','dep') collate latin1_general_ci default 'assig_user',
`value` varchar(255) collate latin1_general_ci default NULL,
PRIMARY KEY (`task_user_filter_settings_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4 ;

INSERT INTO `task_user_filter_settings` VALUES (1, 1, 'assig_user', Ƈ');
INSERT INTO `task_user_filter_settings` VALUES (2, 2, 'cat', Ɖ');
INSERT INTO `task_user_filter_settings` VALUES (3, 1, 'prior', Ƌ');

View Replies !

---

How To Get The SUM Value From Two Tables? Join Tables? Subquery?
Hi,
I have two tables:

table A

Id. | id_result | value
1 | 1| 10
2 | 1| 11
3 | 2| 7
4 | 2| 13

table B

Id. | id_result | value
1 | 1| 4
2 | 1| 1
3 | 2| 5
4 | 2| 6


How can I get sum of unique keys from table A and B (id_result) like this?:

id_result | sum_table_A | sum_table_B
1 | 21| 5
2 | 20| 11

I can do it with UNION or 2 separate SQL statement, but how to make it in 1 query or using subquery?

View Replies !

---

Real Datatype
I am setting up a table and want to have a real data type for one of my column.

I have read the manual and it seems I need the decimal type, however this does not work, if I insert 37.5 or 37,5 it inserts as just 37.

There also seems to be a numeric datatype in the manual but myphpadmin does not offer this as an option when I modify the table.

View Replies !

---

CMS For Real Estate
How do I create a content managment system so a site owner can go through and decide which db entries to display on the home page? The entries will be real estate and there may be as many as 10 available at a time; sometimes, there might not be any! I want some kind of check box for "Display on home page? Yes or No."

Of course, the next trouble is how to get every other property listed to have a different background color, but one obstacle at a time.

View Replies !

---

Detect Real Character Set
Is there any way to detect the real character set use in columns? I've
just seen a mysql 4.0 database where the default character set was
latin1 but greek was being inserted. When that database was upgraded to
4.1 the greek became unreadable. So I guess I'm asking how can I detect
this so I can run a process on suspected databases and ALTER them to
the correct character set.

View Replies !

---

Real-time Search
Does anyone know if its possible with mysql (and probably php) to create a web-based search of a list of 1,000 items that is "real-time." By this I mean:

I have a list of about 1,000 products each with a unique product ID.
The IDs have a format like A12345 or B21755 or B514178
I would like an input box that a user can begin to enter values and then see a list of the matches as they type

For example:
1) The screen starts with 2 areas an input text box; and a dynamically generated list of all known products

2) a user types "A" into an input box but does not click submit or any other button or change mouse focus

3) the list below dynamically changes from all items to just those begining with A as the user continues typing a product number the list gets smaller and smaller until only a few items remain

4) a user will then select the items with they want

This is similar to how a combo box would work if u were to begin typing rather than scrolling for a slection option.

View Replies !

---

Real Basic Question
Table Log (
transid varchar(12) NOT NULL default '',
ref varchar(24) NOT NULL default '',
date datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (transid)
) TYPE=MyISAM CHARACTER SET utf8;

This is being populated fine, I need to get a query right. Example data:

1 Apples 10-Oct-2005 10:27
2 Apples 10-Oct-2005 10:28
3 Oranges 10-Oct-2005 10:28
4 Pears 10-Oct-2005 10:28
5 Apples 10-Oct-2005 10:29
6 Banannas 10-Oct-2005 10:30

I need to produce a summary which hows how many refs in total there are along with the last date. The result would be

Apples 3 10-Oct-2005 10:29
Oranges 1 10-Oct-2005 10:28
Pears 1 10-Oct-2005 10:28
Banannas 1 10-Oct-2005 10:30

View Replies !

---

Real Time Update
We're looking to evaluate MySql for a new web project. I have a hopefully simple requirement which we're looking to acheive.

We will have a locally hosted application using MS Sql Server at the back end. The customer will then have an externally hosted website which uses MySql as it's backend.

We're looking to automatically update the MySql database in realtime whenever certain changes are made to the local Sql Server db. I was thinking of either a remote call to the MySql database to update the relevant tables (will only be a case of adding single records to a specific table) or whether I could create/upload xml information which could then in some way be imported into the web db by some automated process.

View Replies !

---