MYSQL :: Subquery Result Returning Many Rows As Single String

Returning A Random Result
I have the need to return a random record out of a subset of records.

For example I'd like to return a random record out of the resulting records from a query such as: select * from test where category="4";

The query could return 1-many results, but I only want a single random result from that subset.

Returning Result Sets
Hi, I need a stored procedure that returns a result set so I can fill a DataGrid in VB 6. I'm using mySQL 5 and I'm having big problems making this work. What I did at first was to stick to the basic structure of the procedure:

BEGIN
SELECT * FROM table;
END;

When I entered it using the mySQL client, it worked fine. However, when i try to use it in my VB application and get that result into a ADODB.Recordset object (so I can fill the DataGrid control), I get an error message : "SELECT in an stored procedure must have INTO". Does anyone know what's going wrong? I'd really like to use stored procedures instead of just sending the SELECT from the client for performance reasons,

Mysql Is Returning Empty Result Set Even When It Should Not
I recently moved a small access database to mysql. It appeared that everything was OK, but here is a problem I have just noticed.

Few of the SELECT queries (depending on their WHERE conditions) are returning empty result set even when they should not. Very strange, wondering if anyone of you have faced a similar situation, where mysql returned empty result sets even when they shouldn't.

Retrieving Single Result.
Wanted to know what is the most efficient way of doing a select query
for mysql that only returns one value. For example:

$mysqli->query('select count(*) from log');
$temprec = $result->fetch_assoc();
$count = $temprec['count'];

That doesn't seem efficient. How should I do it? Or is this as efficient
as it gets?

MYSQL SELECT Query Not Returning Wanted Result
MS SQL Server has support for cascading update or delete. Does MySQL have anything like that?

How Can Do A Single Pass, String Clean Up?
situation:

have full names that could have

1. franklin, joe r.
2. franklin, joe r
3. franklin jr, joe
4. franklin PhD, joe
5. frankin-ross, joe

so far i came up with:

SELECT distinctrow
CASE WHEN (substr(instr_name,-1,1)=".") THEN substr(instr_name,1,length(instr_name)-3)
WHEN (substr(instr_name,-2,1)=" ") THEN substr(instr_name,1,length(instr_name)-2)
WHEN ((locate("-",instr_name)>0)) THEN REPLACE(instr_name,"-"," ")
WHEN ((locate("Jr",instr_name)>0)) THEN REPLACE(instr_name," Jr","")
WHEN ((locate("Sr",instr_name)>0)) THEN REPLACE(instr_name," Sr","")
WHEN ((locate("III",instr_name)>0)) THEN REPLACE(instr_name," III","")
WHEN ((locate("PhD",instr_name)>0)) THEN REPLACE(instr_name," PhD","")

else instr_name
end as instr_name, instr_name
from results
where instr_name is not null
i want to just end up with

franklin, joe
or
franklin ross, joe

meaning, i want to rip out any middle initials at the end of the name, including if there is a period. also, rip out any last name suffix (i.e. Jr, Sr, III, PhD, etc). and remove the hyphens from any person with a hyphenated last names.

In A SELECT, Can I Replace Contents Of A Result String?
What I mean is this:

If I do

SELECT my_string FROM my_table LIMIT 1

and my_string is

"hi, this is a string",

how do I change all s:s to

"hi, thi# i# a #tring"?

Get Multiple Rows Using Subquery?
I have a query similar to the following, however i'd like to get another row from the subquerys - currency.

SELECT *,

(SELECT xml_result_value FROM lodging_links_allocation
INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id
AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 19 WHERE lod_link_alloc_lod_id = lod_id
ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_1,

(SELECT xml_result_value FROM lodging_links_allocation
INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id
AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 13 WHERE lod_link_alloc_lod_id = lod_id
ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_2,

(SELECT xml_result_value FROM lodging_links_allocation
INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id
AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 12 WHERE lod_link_alloc_lod_id = lod_id
ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_3

FROM (

SELECT *
FROM
[..snip..]
GROUP BY lodging_master.lod_id
ORDER BY RAND(&#55614;&#57159;-12-17')

) as foo
HAVING (price_from_1 > 0) && (price_from_2 > 0) && (price_from_3 > 0)
LIMIT 0 , 30

Returning Of Delete Rows
Is there any way to return deleted rows?
I mean I've delete a rows in mysql5 with " delete from ... " query is there any way to restore those rows?

Returning Limited Sequential Rows
I have a query that returns to me all the seats that are available,
which is great but the ideal solution would be to return X number of
seats based on a seatnum (Integer).

For example, if seats 10, 11, 12 and 15-20 are available and I need 4
of them, I should get back seats 15,16,17,18.

I'm pretty sure that some sort of IF statement with a variable in the
query is the answer, but I can't seem to push through on it.

NOTE: seatnum is NOT a primary key and is not autoincrement. An
autoincrement col wouldn't work for this project as the sections, rows
and seats might get edited as things change in the hall. So I'm doing
the seat numbering myself.

If anyone could give me shove in the right direction, I'd appreciate
it. After many Google searches for "sequential" rows or records and
the like, all I get are people wanting to return record counts.

Returning Rows Older Than 6 Months.
I have a DB packed with orders, and am trying to do a query that returns all the results that are older than 6 months, I wrote a query to find all results between now and six months ago but don't know how to return results older than six months and nothing more..

the date part of the query i had was...

DATE_SUB(CURDATE(),INTERVAL 6 MONTH) <= orderDat

Simple Select Returning More Than One Rows
I would like to ask if it's possible to do a select 1,2,3 and to be returned 3 rows and 1 column with values 1 for the first row,2 for the second etc. select 1,2,3 returns me a row with 3 columns.

Returning Rows Where Field's Value Starts With A, B Or C
I'm trying to pull all the last names that start with A-C, D-F, G-H, etc.  I've tried to use

   select * from mytable where LastName like '[ABC]%'

...but while it makes perfect sense to me, MySQL is having none of it.  I'm sure the syntax is off, but the manual isn't offering anything clear-cut as an alternative.

Anyone have any ideas?

MySQL Returning Redundant Rows
I have 36 records in three tables, each row is related to one row in each table. Up until I add the last where condition (staff_descr.certs LIKE ...), it's fine. But with that last condition, it suddenly returns 108 rows, each row repeated 3 or 4 times.

PHP Code:

SELECT staff.obj_id , staff.timestamp , staff.full_name , staff.city , staff.state FROM `staff` , `staff_descr` LEFT JOIN `user` ON user.username = staff.username WHERE user.is_confirmed = 1 && staff_descr.certs LIKE '%Basic Lifeguarding%'

Returning Multiple Rows From A Stored Procedure..
I have a country table with code and name columns and create a stored
procedure 'get_countries()' but have no idea what is the syntax to
return multiple rows.

I have searched the newsgroups and understand that the solution is to
use cursors. Searching for the mySQL solution is to no avail.

Multi-table Queries Returning 0 Rows
I'm working on a Windows client program in C++, using Qt and OleDbPro (for
database access). Everything was working great until I had to use a query
for the first time in the code that pulled data from two tables. The query
executes, but returns 0 rows. I verified that the query should return rows
by running the query both through another client using an ODBC DSN, and
using phpMyAdmin; both times, I got the result I expected.
I'm using the following connection string:
Driver={MySQL ODBC 3.51
Driver};server=2ksvr;database=mydb;uid=user;pwd=pa sswd;option=16386
I tried with and without using the JOIN format, someone suggested some ODBC
drivers don't work well with JOINs, but didn't make any difference
So, the question I have is, is there something special I have to do for a
DSN-less connection using an ODBC provider?

Problem With Left Join And Count, Returning More Rows Than What It Should
Am having a problem with a query, strangely ...

PHP

SELECT *
FROM table1 AS mt
LEFT JOIN table2 AS pt ON mt.p_id = pt.p_id
WHERE my_field = 'somevalue'

Is returning a much bigger number (12 rows) for me, then what it should.

PHP

SELECT *
FROM table2
WHERE my_field = 'somevalue'

Is returning only 2 rows

Prepared Statement In Stored Procedure Not Returning Rows To App
I have a stored procedure that I'm using to allow users to perform a search on the database. I'm compiling the select statement in the stored procedure and then using PREPARE, EXECUTE, DEALLOCATE.

When I run just the stored procedure it works. When I call it from the app it returns 0 rows. If I just put the SQL statement without using PREPARE it returns rows. Here is the DDL for my stored procedure:

Insert Into Single Row Vs Multiple Rows
My question is: is it possible to add data separated by commas into an existing row that already contains some data?

Getting Total For All The Rows From A Single Column
How can I get the total of all the rows from a single column.

Returning Rows In Left Table Based On Mutliple Criteria In Right
This is starting to get to me. I'm sure there's a simple way of handling what i'm trying to do, but someone might be able to help out quicker than spending another hour searching and testing for this. Here's the problem:
Simplified tables:
ARCADES
=======
ID,
name
GAMES
======
ID,
name
ARCADES_GAMES
=============
ID,
arcade_iD
Games_ID
Straightforward enough so far right? I'm trying to get all arcades that have ALL games in a passed in set of game_id's. So for instance I might want all arcades that have the games 11 and 14, but it must have both those.
I can do soemthing like...

SELECT a.name
FROM arcades a
WHERE EXISTS(SELECT 1 FROM arcades_games
WHERE arcade_id=a.id AND game_id IN (11,14))
But that'll return all rows that match ANY of (11,14) rather than ALL of 11,14.

It all comes down to the simple thing of getting rows in a table where all criteria from a list is met, but any advice on how i would do something like this?

Multi-table Queries Returning 0 Rows With DSN-less Connection ViaOLEDB
I'm working on a Windows client program in C++, using Qt and OleDbPro (for
database access). Everything was working great until I had to use a query
for the first time in the code that pulled data from two tables. The query
executes, but returns 0 rows. I verified that the query should return rows
by running the query both through another client using an ODBC DSN, and
using phpMyAdmin; both times, I got the result I expected.

I'm using the following connection string:
Driver={MySQL ODBC 3.51
Driver};server=2ksvr;database=mydb;uid=user;pwd=pa sswd;option=16386

I tried with and without using the JOIN format, someone suggested some ODBC
drivers don't work well with JOINs, but didn't make any difference

So, the question I have is, is there something special I have to do for a
DSN-less connection using an ODBC provider?

How Do I Add Simular Rows In Result?
I am new to SQL but I heard that much is possible, so I dare ask if there is a solution to this:

I have a db with rows like this:

species: aa count 10 areacode 11
species: bb count 2 areacode 6
species: aa count 6 areacode 11
species: c count 7 areacode 11

now is there a query that returns this to me?

species: aa count 16 areacode 11
species: bb count 2 areacode 6
species: c count 7 areacode 11

Join Rows Into One Result
I'm trying to select letters in a word from a table with the ascii representations. I am selecting the letters successfully, in the correct order. The rows returned give me a letter in each row. I would like it to return one row with the joined word.

aka. It is returning
a
r
d
v
a
r
k

I would like
ardvark

This probably has to do with GROUP BY, but I don't know any functions to join characters into a string.

Distinct Result Rows
but I could not find it.how can I get the following query to return just one record rather than all of them...i.e., just list each 'category' and the corresponding 'cat' # rather then all the records

PHP Code:

SELECT category, cat FROM bus_list

String To Rows
Is there any quick way to update the table rows with the comma separated values?
For e.g.
'amar', 'akbar', 'anthony'

The above string values should be inserted into a table in rows

Turn Rows Into A String
I want to run a query which returns one column (ignore errors here - I've written it quickly - I'm ok with the basic stuff)

SELECT `name`
FROM `company_does_sport`, `sports`
WHERE `company_does_sport`.`sportId` = `sports`.`sportId`
AND `company_does_sport`.`companyId` = ?cId

How To Select Rows Which Contains Certain String
How can I select rows which contains certain strings. Using "Like" clause we can do but its not giving exact results.

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

Show Rows With Result 0 For Aggregate Functions
have a query like this:
SELECT C.idc, C.name, count(CM.idm)
from C, CM
where C.IDC=CM.IDC and CM.idci is null and C.type='class' GROUP BY C.IDC

The result table only contains the elements of table C where the count() is >0.

How can I obtain a result table that contains all elements of table C with their count (either 0 or >0) ?

Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful!

Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better.

I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns.

For example, I'd want this:
1-1,1-2,1-3,1-4,1-5
2-1,2-2,2-3,2-4,2-5

To become:
1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5

The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like:

SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345

This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).

Lock Wait Timeout Exceeded; Try Restarting Transaction Even In Single Db Single User
I am updating few databases all which I keep as one transaction using .net connector. The best part is that I can still run into this error of "Lock wait timeout exceeded; try restarting transaction" even though I am the only single soul using my database at that time. Any idea how to over come this problem.

Subquery Or Correlated Subquery Help
I need to develop a sql that uses the results from the first Query to find data in the second Query. Then the results of the second query to find the final results of the third Query. I’m also wondering if I should try to just link all these tables together instead of Subqueries or Correlated Query.

First Query
select

ACCOUNT_ID,
ACCOUNT_TYPE_C,
PAT_ID

from PAT_ACCT_CVG

where ACCOUNT_TYPE_C in (120103,120104,120101)

Second Query

SELECT

PAT_CVG_FILE_ORDER.PAT_ID,
PAT_CVG_FILE_ORDER.LINE,
COVERAGE.COVERAGE_ID,
COVERAGE.CVG_EFF_DT,
COVERAGE.CVG_TERM_DT

FROMPAT_CVG_FILE_ORDER
LEFT OUTER JOIN COVERAGE
ONCOVERAGE.COVERAGE_ID = PAT_CVG_FILE_ORDER.COVERAGE_ID

Where coverage.payor_id = ?'

Third Query

select

TRAN.ORIG_SERVICE_DATE
TRAN.TRAN_TYPE,
TRAN.INSURANCE_AMOUNT

from Tran

where TRAN.TRAN_TYPE = 1
and TRAN.INSURANCE_AMOUNT > 0
and TRAN.proc_ID in 1008,1009

(now I need to compare the dates on this query to make sure that the TRAN.ORIG_SERVICE_DATE is within the COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT ( dates of the second query)

How Can I Determine The Offset Of A Result In An Ordered Result Set?
How can I determine the offset of a result in an ordered result set?

I would like to pass the calculated offset into the limit half of and ordered select statement.

E.g. I have a table that records a id and datetime for captioned photographs. I'd like to show the five photos that were taken after the photo with id=23.

To do that I need to find the offset of photo with id=23 in

select id, datetime, caption from photos order by datetime;

Then I could get the result I want by doing....

select id, datetime, caption from photos order by datetime limit $offset, 5;

I've spent several hours scouring around and found some people with similar problems, but no solutions yet.

Format Function In SQL String To Return From Numeric Field In Table To String
How would i form a format function in SQL string to return from numeric field in table to string:

1 --> '001'
5 --> '005'
57 --> '057'
125 --> '125'

How Do You Replace A String With Another String
How do I write a query in myPHP admin to search through a particular field in a particular table for a string and replace it with another string? The string is only a part of the value of that field. For example, how would I change "Hello there" to "Hi there"?

Subquery Help
I am using mysql 4.0 which does not support subquerys.How can i rewrite the below query using joins for mysql 4.0
select * from t1 where t1.eid not in(select eid from t2)

Subquery Help.
I am using the following but then I rembered I am getting the readyPrinted_id from the select statement!! How can I fix it so it gets the readyPrinted_id and then performs the AND with that same ID?

SELECT
`sub_name`
, `readyPrinted_name`
, `readyPrinted_id`
FROM sub s,
readyPrinted r
WHERE s.sub_id = $sub_id
AND s.readyPrinted_id = r.readyPrinted_id
AND r.readyPrinted_id = readyPrinted_id

Using NOT IN In A Subquery
I am using MySQL version 4.0.27-standard

everytime i use "not in" in the subquery:

LIKE ANY + Subquery
I'm trying to get the following statement to work:

SELECT * FROM discountItems di
WHERE di.name LIKE ANY
(SELECT lsw.word
FROM ifDefinedSearchWords dsw
join ifLinkedSearchWords lsw on lsw.fIFEntityID = dsw.fIFEntityID
WHERE dsw.word like 'schoggi')

It is supposed to find some words in a subquery as one row, and then search another table for records matching any of those words. I get the following error message:
#1064 - 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 .....

Subquery
Hi, wanted to use a subquery, as in:

SELECT * FROM pending WHERE username = (SELECT username FROM ratings);

But have learned subqueries are only available in MySQL 4.21 or later...

So...

I've tried to use the following query:

Code:

SELECT ratings.username,pending.username FROM
ratings,pending WHERE ratings.username="jjfjunk" OR
pending.username="jjfjunk";

But this returns an empty set, even though I know that the username exists in the ratings table.

Subquery
Can help to find any sytanx error from the following command?

select IMG_ID
FROM image
WHERE WORM_Name IN (SELECT WORM_Name FROM worm WHERE WORM_Age='adult')

I don't know where is wrong of above, but always got the sytanx error messange after I type in the above commands.

Subquery Help
I am pretty sure I have the syntax totally wrong.. but you can probably get an idea of what I want to do.

Code:

INSERT INTO library (tape_id,number) VALUES('DDFA3','433145')
WHERE DDFA3 NOT IN (select tape_id from library where used = '1')";

I'm basically wanting to make sure that no record for that tape_id exists that is set to "used" before I insert another record.

Subquery
I'm having trouble getting the results I need from an sql query. I believe I can accomplish my task with a subquery however I can't get it to work. I have a table of book titles and a one to many table of books read (that holds many people via ID and the book via ID they read). Now I need to get a list of books left to read by a particular person from my join. So the query starts with a join between the books table and the books read table to get the book titles. I use a left join to get all the book titles even if they haven't been read. However I need it to filter out the books that the particular person has read and leave me with a list of books left to be read by the particular person. Since I'm using a join I need it to ignore the records of the other people so that it will include does books in the results while excluding the books that the particular person has read.

Sum() And Subquery
SELECT domainTable.name as domain, sum(NetworkTraffic.upload) as Upload, sum(NetworkTraffic.download) as Download, sum(NetworkTraffic.upload) as Upload2 sum(NetworkTraffic.download) as Download2
FROM `NetworkTraffic`
LEFT JOIN domainTable ON domainTable.domainId = NetworkTraffic.domainId
WHERE NetworkTraffic.protId=2 AND date between "2005-10-11" AND "2005-10-18" GROUP BY domainTable.name

I am using the left-join above to merge two tables (NetworkTraffic and domaintable) on domainId to find which domain to present. By doing this i summarize the download and upload from NetworkTraffic between 2005-10-11 and 2005-10-18, however i want to be able to summarize the traffic from one day, say 2005-10-12 in column 3 and 4 (Download2, Upload2). Is it possible to make a subquery in the sum-function or should i take another approach? I am using mysql server 4.0.18-max-debug

Returning More That One Row From SP
I have 2 simple questions on stored procedures:

1) how do I return multiple rows?
DELIMITER //
CREATE PROCEDURE join_nary_relation() BEGIN
DECLARE c INT;

SELECT clanak_id FROM vidi_clanak_hardver INTO c;
END;
//
CALL join_nary_relation();

This fails:
ERROR 1172 (42000): Result consisted of more than one row

2) how do I return multiple columns?

As you see, I'd like to warp a SP around a SELECT in general case: a SELECT which returns a table with few rows and few columns. Is that possbile?

Subquery Result Returning Many Rows As Single String

Is it possible to return multiple rows from a select statement as a single string? Example:

Is it possible to return multiple rows from a select statement as a single string?

Example: