MYSQL :: Join Rows Into One Result

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

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

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) ?

How To Join Two Result Sets In Version 3.23?
I have two queries that are identical except for the joined table - all retrieved fields are the same.

SELECT r.tbl_ID, r.date, r.field
FROM table1 r
INNER JOIN table2 t2
ON r.tbl2_ID = t2.tbl_ID
WHERE t.field3 = 'Y'

and

SELECT r.tbl_ID, r.date, r.field
FROM table1 r
INNER JOIN table3 t3
ON r.tbl2_ID = t3.tbl_ID
WHERE t.field3 = 'Y'

You can't use UNION in version 3.23 so I am left with guessing you output to an array then add to that array.

Join Query Result Difference Between 3.23.49 And 4.0.13
I have what seems to me a very common operation i'm performing. I need to find the balance on an invoice. i was not having any problems until the production server was upgraded to mysql v4.0.13-standard for pc-linux. There must be a better way to query for this information than the method i'm using, since the result with v4.0 is not what I expected, nor what I received with v3.23. I'm including sample data and queries with
my results.....

Subquery Result Returning Many Rows As Single String
Is it possible to return multiple rows from a select statement as a single string?

Example:

How Can I Use A Join With Possibly Nonexistent Rows?
here's the situation: I'm using mysql to house a database of quotes, complete with ratings by users. So in one query, I need to get everything with a certain rating from the "quotes" table, get the submitter's username from the "users" table and finally check the "ratings" table to see if the user viewing the quotes has already rated it.

The "ratings" table consists of the user id and the quote id. So ideally, I'll be able to to get the first 25 quotes and end up with each in a row something like this:

submitter's id - quote id - quote text - user id - quote id 2

where "user id" and "quote id 2" come from the ratings table and will be NULL if the quote hasn't been rated by the current user, and will be equal to the current user's id and the quote id if it has already been rated.

So far, I think the closest I've come to success was was using this subquery (which would probably be terribly slow even if it did work):

SELECT quotes.*, user.username, ratings.quoteid AS votedid, ratings.userid AS votedby
FROM quoteratings AS ratings
INNER JOIN quotes AS quotes
LEFT JOIN user AS user ON user.userid = quotes.userid
WHERE quotes.quoteid IN (SELECT quoteid FROM quotes WHERE average >= 0 AND approved = 1)

I hope that explanation makes sense, I know there has to be a fairly easy way to do this, but I just can't find it.

Join To Find Rows Not In Second Table
I'm trying to write a SELECT for MySQL 4.0 using a JOIN. I can get it to work in v4.1 using a subquery, but my ISP provides v4.0 only.

I've got 2 tables:
- group: Describes groups that exist, key is group_id
- usergroup: Members of groups - has user_id and group_id

I want to find which groups a user **doesn't** belong to (say user_id=3).

In MySQL 4.1+ I can do this using a subquery:

SELECT group_id FROM group
WHERE group_id NOT IN (
SELECT group_id
FROM group g,usergroup ug
WHERE ug.group_id = g.group_id
AND ug.user_id = 3
)

This query doesn't work in MySQL 4.0, no I need to use JOIN (I think). I've been searching forums and trying things out, but I cannot figure out how to make it work.

Listing Multiple Rows In One Row With JOIN
I have assets and tasks in my table. There are multiple assets assigned to each task. What I want to do is to list the asset followed by all the tasks linked to it in one row.

SELECT * FROM asset
LEFT JOIN task ON (asset.id = task.asset_id)
WHERE asset.id=task.asset_id
GROUP BY asset.name

The above is close, but it will only join the first matching entry from the task table, the result I was hoping to get was something like.

asset1.id asset1.name task1.name task1.status task2.name task2.status

Is there an easy way to do this in SQL?

Remove Duplicate Rows - MySQL4 JOIN
I am looking to remove duplicate rows from a table.
I have limited knowledge of MySQL - so please bear with me.
The following code correctly displays the duplicate rows:

SELECT firmname, address1, custom_2, MIN( selector ) AS min_sel, count( * ) AS issimilar
FROM my_table
GROUP BY firmname, address1, custom_2
HAVING issimilar > 1;
Now I try to run an inner join to show the rows, so that I can then delete them (delete is not included yet)

select bad_rows.*
from my_table as bad_rows
inner join (

SELECT firmname,address1,custom_2,MIN(selector) AS min_sel,count(*) AS issimilar
FROM my_table
GROUP BY firmname,address1,custom_2
HAVING issimilar > 1

) as good_rows on good_rows.firmname = bad_rows.firmname
AND good_rows.address1 = bad_rows.address1
AND good_rows.custom_2 = bad_rows.custom_2
AND good_rows.min_sel <> bad_rows.selector;
The problem is that I can't use a select inside a JOIN in MySQL4.

I don't know enough about MySQL to rewite the above so that it will work in MySQL4 - I know its something like:

SELECT t1.firmname, t2.firmname, count(*) AS issimilar
FROM my_table AS t1 INNER JOIN pmd_listings AS t2
ON t1.firmname = t2.firmname
GROUP BY t1.firmname, t2.firmname
HAVING issimilar > 0
ORDER BY issimilar DESC;
Hoping someone can help. Also have a question on comparing data from other tables - but I need the above to work first.

Getting Number Of Rows From Left Join Table
PHP

SELECT bulletin.id AS bid, bulletin.bulletinname AS bname, bulletin.bulletindesc AS bdesc, bulletin_post.id AS postid, DATE_FORMAT(bulletin_post.postingtime,'%M %d, %Y %l:%i%p') AS postdate, bulletin_post.bulletintitle AS ptitle, member.screenname AS mname, member.id AS posterid FROM bulletin LEFT OUTER JOIN bulletin_post ON bulletin.id = bulletin_post.bulletinid AND bulletin_post.postingtime = (SELECT MAX(postingtime) FROM bulletin_post WHERE bulletinid = bid) LEFT JOIN member ON member.id = bulletin_post.memberid WHERE bulletin.active = 1 ORDER BY bulletin.bulletinname ASC

My question is how would I get the count of rows if any from the bulletin_post table?

Creating Non-existent Rows In Query With Join
I want to make report using PivotTable/CrossTab and I used an application to create it.
The problem is, I want to so show NULL value to the temp table that will be the source of my report.

I'm using this query:

Can't Use JOIN And SUM To Roll Up Rows Into Multiple Columns
Let's say I've got the following table "Eats" that contains row after
row of Calories consumed by children eating lunch at the school
cafeteria. The "ID" column is an autoincrement field I added, but it
doesn't seem to be doing me any good.

mysql> SELECT * FROM Eats;
+------+------+------+----------+----+
| dow | who | sex | Calories | ID |
+------+------+------+----------+----+
| Mon | John | Boy | 2600 | 1 |
| Mon | Tom | Boy | 1900 | 2 |
| Mon | Jane | Girl | 1200 | 3 |
| Tue | Tom | Boy | 1600 | 4 |
| Tue | Jane | Girl | 1300 | 5 |
+------+------+------+----------+----+

The output I WANT is a table with total calories broken down by gender
and day, i.e., one (and only one) table with a "Boys" total and a
"Girls" total. In this case I want to see:

+------+------+-------+
| Day | Boys | Girls |
+------+------+-------+
| Mon | 4500 | 1200 |
| Tue | 1600 | 1300 |
+------+------+-------+
It's important to have One table with Two headers, not the other way around.

Not knowing any better I tried a JOIN and came closest with:
mysql> SELECT a.dow AS "Day",
-> SUM(a.Calories) AS "Boys",
-> SUM(b.Calories) AS "Girls"
-> FROM Eats AS a
-> JOIN Eats AS b
-> ON (a.sex="Boy" AND b.sex="Girl" AND a.dow=b.dow) GROUP BY a.dow;

Which produces:
+------+------+-------+
| Day | Boys | Girls |
+------+------+-------+
| Mon | 4500 | 2400 |
| Tue | 1600 | 1300 |
+------+------+-------+

Everything is right except Mon-Girls, which is twice the real value.
I figure this is due to the pairing of Jane-John and Jane-Tom, causing
Jane's meal to get counted twice. But that pairing is necessary to make
sure we count all the boys' Calories too.

Am I overlooking something obvious? Are JOINs and SUMs just a bad idea?
Most importantly, is there some other way to accomplish the task? I've tried
many, many variations on the above code (with and without using the ID field)
and gotten nowhere.

For this simple example I could do SELECTs INTO variables, but that solution
doesn't generalize to hundreds of rows, nor cases where the children are
further broken down by age. Code:

LEFT JOIN Produces Extra Rows
The `suggestions` table contains 2,265 rows. The `ALL_PHS_SCHEDULES` table contains over 22,000 rows. I have done my homework and I read that the Left Join return ALL rows from the first table and the matching ones from the other table. I am expecting exactly 2,265 but the query is returning 2,734 rows.

Quote: SELECT *
FROM `suggestions`
LEFT JOIN `ALL_PHS_SCHEDULES` ON `suggestions`.course_number = `ALL_PHS_SCHEDULES`.course_number
AND `suggestions`.section = `ALL_PHS_SCHEDULES`.section AND `suggestions`.id = `ALL_PHS_SCHEDULES`.id
ORDER BY `ALL_PHS_SCHEDULES`.`course_number` , `ALL_PHS_SCHEDULES`.section ASC

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

Agregate Count Return All Rows, Left Join
I have 2 tables on a lyric discussion site

Titles
   title_id
   title_name
   title_lyricist
   title_lyrics
   title_artist
   title_entered

And POSTS
   post_id
   title_id
   post_author
   post_text

I want to be able to list all the titles, and count the # of posts for each title. The problem is some titles have zero posts so my query ignores thos titles, butI syill want them returnd where Ill add a ')' for count.

This is the best i could come up with, but still only those that have posts are returned. I still want the titles that don't have a match in the posts table to be returned with a total_count of zero.

Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being

PHP

SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT
            FROM grps
            RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid)
            GROUP BY grps_c.catid
            ORDER BY grps_c.title

which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add

PHP

LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ')

however adding that removes the rows that have a 'count' or NULL or Zero.

Adding Missing Rows In Table In 1 Select (+ Join) Command.
Hi all! Here's what I need to do :

I have two tables :
A B
a b c d
---- ----
1 z 1 k
2 x 5 l
3 c 6 j

I need a SELECT with JOIN that would give me :
A
a b
----
1 z
2 x
3 c
5 NULL
6 NULL

so I need to add the missing rows from the A.a and B.c JOIN,
how can I do that ?

I can't use a Union 'cause I can't use MySQL version 4.

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.

LEFT JOIN? RIGHT JOIN? Multiple JOIN?
Simplifying this down to its basics, I'm using LEFT JOIN in a query but I'm not getting the results I want.

The tables are:
table services
service_id
service_name

table services_provided
service_id
service_date (date field)
cust_id
service_quantity

I need to select ALL services from the services table, and the number of services provided (by a specific customer, in a specific time frame) from the services_provided table, so that I can generate a list that shows services provided by that customer in the specified period of time

The query:

SELECT service_date, service_name, service_quantity
FROM services
LEFT JOIN services_provided ON services_provided.service_id = services.service_id
WHERE cust_id = $cust_id
AND MONTH(service_date) = 10
AND YEAR(service_date) = 2007
GROUP BY service_id
ORDER BY service_id
(Aside: The date to be selected varies - it may be the whole year, or may be a selection of months,such as 1, 2 or 3. This is determined dynamically in the script. The cust_id is determined by which customer is logged in.)

I'm pretty sure that the left join as I have it should return all services, even if there's no corresponding entry in the services_provided table.

But because of the WHERE clause, I don't get a complete list of all services -- if the customer doesn't have any entries for a particular service, that service doesn't come up in my results.

Do I need to change how I'm joining the tables, or join them twice? I'm sure I could do this with a nested query, but I'm trying to avoid that.

Join Vs. Inner Join Vs. Implied Join = Different Results ??
I SUM() only on the order table in all queries below. Here's a set of queries that I thought would/should yield the exact same results:

QUERY 1:
SELECT COUNT( o.orderID )
FROM order o
WHERE DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 161

QUERY 2:
SELECT COUNT( o.orderID )
FROM order o
LEFT OUTER JOIN credit_card cc ON o.orderID = cc.orderID
WHERE DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 175

QUERY 3:
SELECT COUNT( o.orderID )
FROM order o, credit_card cc
WHERE o.orderID = cc.orderID
AND DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 157

Searching For Rows That Depend On Other Rows In Some Fashion...
Maybe this is a very stupid question. I'll try anayway. I'm using
MySQL 3.23.52 on RedHat 8.0.

I have a very big table (several millions of rows). Each entry
actually constitute a word in a big text. Among other fields we have
an ID for each word which represents the postition in the text. Now I
want to search for short phrases.

For example "welcome to sweden". This means i want to find all
occurences of the word "welcome" with ID x, where we have the word
"to" with ID x+1 and the word "sweden" with ID x+2. I've tried doing
this recursively with temporary tables - but a find myself hitting a
wall as i'm not allowed to refer to 2 different temporary tables in
the same query...

Resorting to another type of data strucutre, full text index etc.. is
unfortunately not an option. We only need this as an add on feature if
we can do it. We believe that indexes, data structures etc.. are near
optimal as is.

Please use my email for further conversion since I'm not a frequent
usenet reader.

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables:

Week (with columns day and hour)
Activity (with columns day, hour, activityid and ac_text)
Person (with columns name and activityid)

I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement:
Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour

I can then make a loop (I am usin asp.net) that writes the activities.
My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid).

I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.

Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's
the explain gives the best join order, and it completes in 1.5 secs

I add a single ORDER BY (a calculated column) and the join orders all shift
and the query takes 85secs!

So I read the docs and it suggests STRAIGHT_JOIN to force join order.

now I was using:

JOIN myTable ON xyx=abc

but in the docs it seems the ON condition is not permisible here, though it
does work.
Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there
is no difference.

However the only way I can force the join order is to use STRAIGHT_JOIN that
does not accept an ON condition, so I have shifted the clauses to the WHERE
and it works fine.

Is there any syntax I can use to keep the ON conditions, I prefer this
approach it makes the code clearer regarding intent. Code:

Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's the explain gives the best join order, and it completes in 1.5 secs

I add a single ORDER BY (a calculated column) and the join orders all shift and the query takes 85secs!

So I read the docs and it suggests STRAIGHT_JOIN to force join order. now I was using:

JOIN myTable ON xyx=abc

but in the docs it seems the ON condition is not permisible here, though it does work. Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there is no difference.

However the only way I can force the join order is to use STRAIGHT_JOIN that
does not accept an ON condition, so I have shifted the clauses to the WHERE
and it works fine.

Is there any syntax I can use to keep the ON conditions, I prefer this
approach it makes the code clearer regarding intent. Code:

Getting Rows That Are Related To Other Rows In The Same Table
I use a table to save a map using the following structure:

id, x, y, owner

Every occupied map filed has an owner id != 0. The owner id is = 0 for vacant fields.

now the problem:

New registered users need a vacant field on the map. Moreover the mapfields around this field need to be vacant as well! (sqrt((t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y)) <= 5.25)

What I need is a query that gets those fields that have vacant fields around them.

So far, all my tries to solve this problem with Joins/Suvqueries failed.

Joining Data (inner Join / Self Join?)
I am relatively new to php/mysql and I am having a problem figuring out how to do a join. I have a database with a person's name and each person has an ID. I want to be able to add their relatives by typing only their ID.

For example if person 1's descendant was person 37, I want to be able to enter that in the DB and then run a query on person 1's page so that when I have 37 entered as his descendant it will query the DB for his name and print his name but not the ID.

Using LEFT JOIN Instead Of A Equi-JOIN
I have a SQL statement in some code I'm trying to get my head around.. I havent used SQL that much so I assume this is a newbie question: Why would someone use LEFT JOIN if they can simply construct the statement with equi-JOIN? The first statement uses left joins and the 2nd is my reconstruction using equi-JOINs.. so far they produce the same results (however it could be I dont have the right kind of test data) So to summarize my questions: Why do it using LEFT JOINS which I personally find harder to read over the equi-JOIN, 2nd Do they acutally produce the same result everytime?
1st (LEFT JOIN)
-------------------------------------------------------------
SELECT action.action,
summary.gatekeepercl,
branch.branch
FROM summary
LEFT JOIN action ON summary.action=action.id
LEFT JOIN branch ON summary.branch=branch.id
WHERE summary.gatekeepercl IN (506100,506101)
2nd (equi-JOIN)
---------------------------------------------------------------

SELECT action.action,
summary.gatekeepercl,
branch.branch
FROM summary, action, branch
WHERE summary.action=action.id
AND summary.branch=branch.id
AND summary.gatekeepercl IN (506100,506101)

JOIN Syntax (INNER JOIN), Self Join
I want to find the most recent purchase for each customer(see below) and I tried:

SELECT A.*
FROM mytable A INNER JOIN (
SELECT full_name, Max(entered_when) AS entered_when FROM mytable GROUP BY full_name
) M ON A.full_name=M.full_name AND A.entered_when=M.entered_when
;

I got a syntax error:
ERROR 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 'SELECT full_name, Max(entered_when) AS entered_when FROM mytable

My data looks like:
full_name  product_purchased  entered_when
phil          shoes             20050227121012
mary          purse             20020212000000
phil          socks             20021226101423
mary          bra               20020111000000

(I just want to get phil's shoes and mary's purse.)

What should I be doing?

Getting The Last Result
I have following table:
product| ordered_by | date
n1 | A | 2006-01-01
n1 | A | 2006-02-01
n1 | D | 2006-03-01
n1 | B | 2006-05-01
n2 | B | 2006-01-01
n2 | C | 2006-04-01
....

As a result I only want one set per product with the latest "ordered_by".
In this example:
n1, B and
n2, C

So far I solved it within the executing program but I was wondering if there
is an efficient query to do the same.

Result Set
I have a SELECT that returns a set of data

Code:

SELECT stats_d.link_id
FROM stats_d
LEFT JOIN links ON stats_d.link_id = links.id
WHERE links.id IS NULL AND stats_d.link_id IS NOT NULL

Now, i want to update stats_d.link_id (same value for all) in the result set.

Result Set Of Ids
I'm selecting data where the id is in a list (ie. SELECT * FROM x_table WHERE id IN (3,4,7,8,9)).
Is there a way to maintain the order of the result set based on the order of the IN list?
For example, if I constructed my IN list to be (3,2,7,6,4), my result set will be returned in that order instead of (2,3,4,6,7).

First Query Result Used For Second?
Is this possible / Practical?

Function1
I have a form that takes a users single field input and queries the database returning two values as result of what was entered, valueA and valueB. It then calls function2 passing it valueA and valueB.

Function2
takes valueA & valueB and queries the database again using these values within the query.

Im still learning mysql so the code is a real mess and not working.

Could someone give a simplified example of how this could / should be done?

Invert Result Set
SELECT `table1_id` FROM `table1` INNER JOIN `table2` ON `table1_id` = `table2_id` GROUP BY `table1_id`
This will return a unique list of table1_id's where there is relational data from table2.

What I want to do is invert this entire result set aka get a list of table1_id's that have NO current relation to table2.

I've done this years ago in mysql 3.23 but i'm currently having a metal blank...

Result From Insert
I want to insert a record and read the index value created in a single query.

It is not sufficient to insert a record with one query then read the last record with another query as a different user running the same application has a chance of inserting another record before the 2nd query (to find info on the latest record) has a chance to run.

Location Of A Row In Result
here's the problem let's say that I have the following table (msgs):

id, cid, text
1, 1, text1
2, 1, text2
3, 2, text3
4, 1, text4
5, 2, text5
6, 2, text6
7, 1, text7

now if i executed the following query:

SELECT id, text FROM msgs WHERE cid='1';

here's what I'll get

id, text
1, text1
2, text2
4, text4
7, text7

but here's what i want to get

counter, id, text
1, 1, text1
2, 2, text2
3, 4, text4
4, 7, text7

and then I want to know the location of the row that holds the id 4, which is 3 in this example.

1st: I have mysql 3.23 on my pc, and the new version of mysql is about 17-36 MB so it's hard for me to download it on a slow intenet connection, plus I don't know whats the version of mysql on my site server, so please suggest a solution that works for version 3.23.

2nd: I don't want to create temporary tables to find the results as many users at the same time could execute the same query, so the code might create thousends of tables at the same time.

3rd: I don't wanna use PHP code for this, as the result could return thousends of results, and I don't wanna go through loop for thousends of records.

please any ideas? oh btw I want to display only 5 records but I want to know what 5 records to display depending on the location the certain row that i'm looking for.

No Result For Zero COUNT
The following query

SELECT COUNT(Id) FROM References
WHERE refereeId='3'
GROUP BY targetId;

returns the desired result if there are several rows that contain the
specified refereeId, which is great.

But if there are no matching rows, nothing is returned, and I was
expecting a return value of zero.

Am I using COUNT wrong?

Get The Row Number From A Result Set
How can I know the row number of a result?

For example i have a query like this:
"SELECT id FROM table WHERE id = 3"

So is there a way to get the row number where id = 3? I tried using 'mysql_fetch_row()' and 'mysql_affected_rows()' but just cant get what i want.

Large Result Set
I have a question regarding working w/ an extremely large result set - or maybe better yet how I could possibly avoid it. I have a table w/ several million records and I need to loop thru and check each record against an entry in another table and if a match is found write most of the current row's fields out to a file. My problem is when I query for the first set the result set is too large and I blow my memory.

Empty Result Set
I'm running a perl script that accesses a mysql database. I'm trying to run a simple select statement that gives me an empty result set. The wierd thing is that if I simply replace the table name with another table name - the statement returns stuff. Could the one table have some sort of issue with it?

Display More Than One Result From DB
why this:

mysql_select_db($database_MARTIN, $MARTIN);
$query_CallsAssignedCount = "SELECT StaffID, count(CallID) FROM callinfo WHERE callinfo.StaffID = '$row_Leader1[StaffID]' GROUP BY StaffID ORDER BY CallID ASC";
$CallsAssignedCount = mysql_query($query_CallsAssignedCount, $MARTIN) or die(mysql_error());
$row_CallsAssignedCount = mysql_fetch_assoc($CallsAssignedCount);
$totalRows_CallsAssignedCount = mysql_num_rows($CallsAssignedCount);

only displays one result and not all StaffIDs? Its meant to list all StaffIDs and the number of calls closed by that staff member. '$row_Leader1[StaffID]' is the coloumn that lists all the client IDs on another form. This lists all StaffIDs fine, however, this SQL command (above)only displays ONE StaffIDs result.