MYSQL :: How To Join Results ?

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

Limit Inner Join Results ?
Is it possible to limit the results of an inner join or sub select ?
For example, let say that I have a table of countries and a talbe of cities:

Sql Returning Too Many Results On JOIN
I have three tables which I'm joining...

AGENTS
========
agent_id
commission
promo_code

AGENT_PAYMENTS
==============
payment_id
agent_id
amount

SALES
========
sale_id
promo_code
status

I need to select all the sales info where an agent's promo code has been used (and the status of the sale is "C" (complete)). THe trouble is, the SQL I've used is doubling, trebling etc the sum of the payments made to an agent - this depends on how many results are returned for the number of sales... eg: 4 sales means the SUM is being multiplied by 4.

here's the SQL Code:

Join Duplicated Results Issue
I have two tables, one is a catalogue of products and another one with media connected to the catalogue items. the media can be either 'main image' or an 'alternative product view'. I need to retrieve 4 products that are marked as live (c.status = Ƈ') and their 4 corresponding main images (m.category = 'main') with no repeats on the products.

I have the following query:

SELECT DISTINCT c.id, c.category_id, c.title, c.description, m.filename, m.description, m.copyright
FROM catalogue c
LEFT JOIN media m
ON m.parent_id = c.id
WHERE c.status = Ƈ'
AND m.category = 'main'
LIMIT 4
the problem I have is that some products have more than one 'main image' so this query is returning a duplicate product because it has two main images, even if the product only exists once on the catalogue..

Selecting Distinct Results In A Join
SELECT p.post_title
, p.post_name
, c.cat_name
, DATE_FORMAT(p.post_date, '%M %D, %Y') AS dateadded
FROM wp_posts AS p
LEFT JOIN wp_post2cat AS pc ON p.ID = pc.post_id
LEFT JOIN wp_categories AS c ON pc.category_id = c.cat_ID
WHERE MATCH(p.post_content, p.post_title)
AGAINST(TRIM('merchant') IN BOOLEAN MODE)
ORDER BY p.post_date DESC
I am doing a fulltext search on a Wordpress database. Some posts are in multiple categories. When using the above query I will get a result for every category a post is in. I don't want that. I have tried using distinct with no effect. The wp_categories has a row for each category a post is in and how I am joining it seems to be the culprit.

Join 3 Tables - Distinct Results
I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacturers have reported in the given month.

Join 3 Tables, Limit Results From 3rd Table
Yet again I have a query about joins and even though I keep reading I cannot find a solution for my problem that is relevant or that I understand.

I have 3 tables (I have shortened the number of fields to those that are relevant):

category:

Quote:

catid

product:

Quote:

prodid
catid
prodname

prodimgs:

Quote:

imgid
prodid
imgname

I can select all products per category and I can select all images per product, but where I am having the problem is selecting all products for one category and at the same time selecting all the images per product and limiting the number of images displayed to one without limiting the products to one.

I have tried various joins and tested a lot, getting different results but none of them are returning what I need.

Select all products for a category:

PHP

SELECT prodid, prodname FROM product WHERE catid='$this->catid'

Works fine.

Select all images per product per category and limit results of image to 1:

PHP

SELECT product.prodid, product.catid, product.prodname, prodimgs.imgname FROM product, prodimgs WHERE product.catid ='$this->catid' AND prodimgs.prodid = product.prodid LIMIT 1

This returns only one product and one image per category despite there being more.

Using the same code as above but removing the limit I get a display of all images per product with the product name repeated over and over.

I know I need to use joins somehow but I have tried various ways and tested the sql but am always getting an error on the joins and I don't know how or where to put the limit so that it is only applied to the prodimgs table.

So what I want is:

1. User click on a category

2. On the next page all product names linked to the category are displayed, but once only.

3. Next to each product name, one image related to that product is to be displayed if an image is stored in the prodimgs table

Combining Results From Querying Two Join Tables
I'm using two queries to pull data from a two join tables and am looking for a way to get them into one result.

The first query is:
select group_concat(x) as genre_id, group_concat(y) as genre_name from (
select genres.id as x, genres.g_name as y from genres where id in (
select genre_id from genres_movies where movie_id = 70000103
)
) as tbl

+---------------------+---------------------------------------------------------+
| genre_id | genre_name |
| 864,131,813,191,321 | Documentary,Indie Documentaries,Political Documentaries |
+---------------------+---------------------------------------------------------+
The second is:
select group_concat(a) as director_id, group_concat(b) as director_name from (
select director.id as a, director.p_name as b from directors where id in (
select director_id from directors_movies where movie_id = 70000103
)
) as tbl2

+----------------------------+------------------------------------+
| director_id | director_name |
| 20006021,20063045,20063046 | Chris Smith,Dan Ollman,Sarah Price |
+----------------------------+------------------------------------+
Does anyone see a way to get something like this?
+---------------------+---------------------------------------------------------+----------------------------+------------------------------------+
| genre_id | genre_name | director_id | director_name |
| 864,131,813,191,321 | Documentary,Indie Documentaries,Political Documentaries | 20006021,20063045,20063046 | Chris Smith,Dan Ollman,Sarah Price |
+---------------------+---------------------------------------------------------+----------------------------+------------------------------------+

Left Join Producing Unexpected Results
The following SQL query is producing undesirable results. What I am trying to do is get the ID of the people listed in the persons table, who are not on a given list, on the listtrack table. The listtrack table has an entry for each Person_ID<->List_ID pairing. I can find out easily who is on a given list, but it's finding who is not that is giving me bother.

SELECT people.PersonID
FROM people LEFT JOIN listtrack ON people.PersonID=listtrack.Person_ID
WHERE (listtrack.List_ID<>4 Or listtrack.List_ID IS NULL)
AND people.Company Like '(none)'

Join Results From Select Multiple Statment
how I can join results from multiple queries in one result without geting a prodict of the tables

ex:
table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
, , ...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
, , ... , , ...table3_Col1, table3_Col2, table3_Col3...
, , ... , , ...table3_Col1, table3_Col2, table3_Col3...

using the 4.0 sql so no subqueries supported.

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.

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:

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?

Need Help With Join And Values In The Join
I set up a join using two tables (description and product). I have multiple products that use the same description. I also have different material types for the products. The SQL is "SELECT descripID, subCategory, ProdID, subCat, materialType FROM products, descriptions WHERE subCategory = subCat".

from the description table:
- descripID
- subCategory

from the product table:
- ProdID
- subCat
- materialType

What I want to do is get the descripID and materialType into a new table. There is going to be multiple descripIDs that match multiple materialTypes. I only want one materialType to be with a particular DescripID.

for example:

descripID: 01
materialType: 01

descripID: 01
materialType: 02

descripID: 02
materialType: 01

descripID: 03
materialType: 03

I hope this helps.

Here is some of the output I currently have:

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00024
Prod Material: 01

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00025
Prod Material: 01

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00026
Prod Material: 03

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00027
Prod Material: 02

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00028
Prod Material: 02

To JOIN Or Not To JOIN... Or Am I Missing Something...?
Right, I was always under the impression that it was 'better' to use JOINs, partly because it is 'faster'.

I'm now wondering if that is simply a myth.

Take these two SQL statements:

SELECT DISTINCT p.ProductID, p.Image, p.Price
FROMproducts AS p
RIGHT JOINcategory_links AS c_l
ONc_l.ProductID= p.ProductID
INNER JOINcategories AS c
ONc.CategoryID= c_l.CategoryID
RIGHT JOINbrands AS b
ONp.BrandID= b.BrandID
RIGHT JOINsize_links AS s_l
ONs_l.ProductID= p.ProductID
INNER JOINsizes AS s
ONs.SizeID= s_l.SizeID
RIGHT JOINcolour_links AS co_l
ONco_l.ProductID= p.ProductID
INNER JOINcolours AS co
ONco.ColourID= co_l.ColourID

SELECT DISTINCT p.ProductID, p.Image, p.Price
FROMproducts AS p,
category_links AS c_l,
categories AS c,
brands AS b,
size_links
AS s_l,
sizes AS s,
colour_links AS co_l,
colours AS co
WHEREc_l.ProductID= p.ProductID
ANDc.CategoryID= c_l.CategoryID
ANDp.BrandID= b.BrandID
ANDs_l.ProductID= p.ProductID
ANDs.SizeID= s_l.SizeID
ANDco_l.ProductID= p.ProductID
ANDco.ColourID= co_l.ColourID

The first one uses JOINs and the second simply uses WHERE. As a matter of information, both have additional WHERE details added to refine the search.

I'm using a fast PC, and there are only 14 products in the database (and not all that much data in the other tables). However, I was getting REALLY slow script execution, and I traced it to the SQL query.

Running the first one takes an average of 7 seconds. Running the second query takes less than 1 second. It's almost instantaneous in fact.

This kinda tells me NOT to use JOINs... and to stick with WHERE for this. But in that case, I am left confused as to where it is appropriate to use JOINs and where it isn't...? I did a couple of hours of Googling and didn't clear the matter up. All the articles I found pointed towards using JOINs.

Obviously at the end of the day I'm going to use the faster method. Plus, after thinking about it, there's a lot more work being done with the JOINs, is there not...?

I'd love to know what some other people think about this, and whether I'm just totally out on my JOIN usage or if other people are using them in the same situations.

::] krycek [::

Inner Join Or Left Join?
What I am trying to do is this....

English Table:
Number Text Roman
1 One I
2 Two II
3 Three III
4 Four IV

Hindi Table:
Number Text
2 Do
3 Teen
4 Char
5 Panch

Expected Results where number is 2
Text Roman
Two II
Do II

JOIN Within LEFT JOIN
I am using MySQL 3.23.54. I have the following table structure.

FORMS
form_id (PK)
form_name

STAFF ASSIGNMENTS
staff_assignment_id (PK)
form_id (FK)
staff_id (FK)

STAFF
staff_id (PK)
first_name
last_name

For each record in FORMS there may be zero, one or multiple records in STAFF ASSIGNMENTS. I need to perform a left join from FORMS on STAFF_ASSIGNMENTS. When there is a record in STAFF ASSIGNMENTS, I need to perform a join with STAFF to retreive staff name. Here is my attempt at the query.

SELECT forms.form_id, forms.form_name, staff.first_name, staff.last_name
FROM forms LEFT JOIN staff_assignments ON forms.form_id =
staff_assignments.form_id (JOIN staff on staff_assignements.staff_id =
staff.staff_id)

How do I need to write the query?

Add Up Results
Not sure if I should ask this in PHP forum or this one?
I think this is a simple request. I have a table with names and dollar amounts of pledges. What I need to do is grab all the amounts for the same person and then add it up to a total amount that they owe.
ie: Table is like this.
Don $500
Joe $100
Don $1250
Don $1250
Fred $300
I need to pull all the info that are for Don and then add up the amounts, for example this one Don owes a total of $3000.

Getting Results
i use mysql 4.0.22 which means i cant use subquery's right?
Ive tried using them but it dident go well.
This is what im trying to accomplish

SELECT service FROM rate WHERE type = 'as'

service must be equal to se
SELECT se FROM autosurf where st = 0

Different Results
I have a nightmare of a problem, where in a Query I am attempting to calculate percentages. I had the query running on an earlier version of mysql and it worked fine, and it is now running on the latest version and doesn't return the correct value.

This is the query : Select DISTINCT std.student_f_name, std.student_m_name, std.student_l_name, lt.time_from, lt.time_to,
lt.day, TIME(rda.clockinTime),
cast(($variable1/$variable2)*100 as decimal(3,2))
from students std, record_attendance rda, lecture_times lt
where std.studentID = ?
AND rda.studentID = std.studentID
AND rda.lectureTime = lt.id
AND lt.id=?

This correctly returned 33.3 percent in the older version, however in the new version returns 9.9 %. Does anyone know how this can be fixed?

All Results
Anyone know how to Get all results not starting with a letter?

Unique Results
I am querying my mysql database from php like so:

$query = "
SELECT $wpdb->posts.*
FROM $wpdb->posts
LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID = $wpdb->post2cat.post_id)
WHERE $wpdb->post2cat.category_id NOT IN (2, 6)
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = 'post'
ORDER BY $wpdb->posts.post_date DESC";

Its joining a second table (post2cat) as the main table (posts) does not have a value (category_id) which I need to check is not 2 or 6. As a result if an item in posts has two category ids (and so is listed twice) the item goes into my mysql result twice which I don't need. Is there a way I can return only unique results (i.e. not twice)??

This is the Wordpress mysql structure if anyone is familiar with it.

Results Into One Cell
I need to query a table and have all the results show in one cell.

Example: Select last_name, age from costomer where last_name = "Franks";

If there are three members to the frank faimily, the result would be ...

Subquery With Many Results?
So i'm looking for a way to have a query with a subquery, but the subquery returns more than one item.

ex.
SELECT * FROM menu where parent_id = (SELECT menu_id FROM menu WHERE parent_id = 0)

the subquery will return [22,30,33]
I would like the top query to execute logically as follows:

SELECT * FROM menu where parent_id = 22 OR parent_id = 30 OR parent_id = 33

I can't predict the count of the subquery.

I can do this with loops in my php but I have a feeling that it is possible to do this with a single SQL statement (multi subqueries).

Specific Row On Top Of Results
Someone knows how I could list all the records of a table with the very first result being of a specified value that exists in the table?

For example :

column 1
a
b
c
d

Query result with c needed on top :

c
a
b
d

Query result with b needed on top :

b
a
c
d

Duplicate Results
I have a DB with a name column. Sometimes, the names might duplicated, so i was wondering how to write a query that says, show me 1 of the records instead of both...?

No Results When Just One Record
I'm using this query to retrieve news and comments:

$query = "SELECT SQL_CALC_FOUND_ROWS *,
DATE_FORMAT(n.date,'%d/%m/%y') AS date,
COUNT(c.news_id) AS comments,
n.news_id,
n.icon_id,
n.views,
n.name,
n.title,
n.news
FROM news AS n
LEFT JOIN comments AS c ON (n.news_id = c.news_id)
GROUP BY n.news_id
ORDER BY n.date DESC
LIMIT $offset, $limit";

The problem is that if both tables are empty and I insert one news, it won't show up until I insert another news or one comment. Why is that happening? Am I using the wrong Join?

Sorting My Results
This is the sql Im using in a recordset using dreamweaver:

SELECT racer_name,SUM(points)
FROM race_results
WHERE `class` = 'gas'
GROUP BY racer_name
ORDER BY race_results.points DESC

Im really new to this but the "SUM" is working fine, but im having a problem with
ORDER By function,...its order by "points" which in my DB is a samllint ---- It just
needs to sort by that number (points) by largest to smallest,

List Of Results
I have a table with data

User | Value
-------------
A | 1
A | 21
A | 33
B | 41
B | 56
C | 68

and I want to produce a query that will return as the result.

User | Values
--------------
A | 1,21,33
B | 41,56
C | 68

Is this possible? How so?

Offsets On Results
How do I produce only 10 results and specify an offset (where the results will begin)? I'm working on something like a search results page on PHP and I was wondering how to do this to reduce server load.

Not All Results Are Displayed
I have a page that by default will list a bunch of subjects. When the user clicks on a subject, that same page will reload and display a list of articles pertaining to that subject by passing the subject ID in the url. Same page, different info. However, I cannot get all of the results to display. Code:

Randomizing The Results.
(I am using PHP on a *nix box)

I would like to make the results returned from this query return in a random order. ie: 1 2 3 4 5 => 2 4 1 3 5 , and different each time the query is run.

CODEselect f.ID as fid, d.Title as denom, c.denom as did, c.ID as ID, c.name, c.imgw, c.bio, c.imgh, c.city, s.state as state, co.description as size FROM featured as f, churches as c, states as s, congregation as co, denomination as d where s.stateid = c.state AND co.ID = c.size AND c.ID = f.clientid AND f.type = 'c' AND d.ID = c.denom

Results Navigation
I have a results page and the results paging nav is pretty simple as it stands:

< | Previous 1 2 3 4 5 6 7 8 9 10 etc | Next >

The problem is that when there are alot of results there can be up to a few hundred of these page links.

So I need something like the nav that is used on coding forums and other sites:

< 1 2 3 4 5 > Last , First < 5 6 7 8 9 > Last

or similar...

Does anyone know where I can find a class/script etc to do this?

Retrieving Top 100 Results
I am trying to get an average value from my database but I only want to retrieve the average out of the top 100 results. The column I want to average contains values from 0.1 to 10.0.

So I want to get the average from the top 100 values in this column. The average syntax is clear but what do I have to add?

This is what I have now:

$query="select AVG(M_RATING) as rating FROM rate_members WHERE M_COUNTRY='SE'";
But this gives me an average based on all values in the column.

Grouping Results
I have an query that selects some records from a database what need to be grouped by an ID value. I can have multiple records with the same ID and they need to be grouped by it, an example:

an array from query would be:
1. 1 record one
2. 1 record two
3. 8 record one
4. 8 record two
5. 8 record three

and the final output that I need is

1
-------
record one
record two

8
--------
record one
record two
record three

Combining Results
I've a table as follows:

film_id | t_day | t_time
1 | 2006-06-10 | 12:30
1 | 2006-06-10 | 13:30
1 | 2006-06-11 | 11:45
1 | 2006-06-11 | 12:45

I want to select all the data where film_id = 1 and have the results in a form similar to this:

t_day | t_times
2006-06-10 | 12:30, 13:30
2006-06-11 | 11:45, 12:45

Is it at all possible through just a single MySQL query so that I can easily navigate the days and the times.

Block Results
If I wanted to list my results in blocks of 500 records how would I do this? I looked up cursors and found it confusing. I read up about limits but then it would only list the first 500 records and ignore the rest.

The main reason I'm asking is because my Java app is crashing because of too much data being sent to it from the MySql.

The way to control data is it through MySql or Java?

Grouping Results
I need to create a query that selects various rental properties from a table depending on certain criteria and to have them grouped according to their region.

The table I have (from another program that links to the dbase so I can't change it) has a field called regiunea where the area of town the property is in is recorded as a text entry, eg. centre or west or east etc.

Obviously I can query on a specific area in the query, but as we are looking at group bookings for multiple properties we may not have enough properties in one area to fulfill the clients needs.

So, I want to group the appartments by area so that if there are enough appartments free in say the north part of town it will display them and if not it will display a part of town that does have the correct number. The client does not have any say which area the appartments are in but they do get a choice of a different area too (they are given the price difference).

I have tried to use the mysql group by statement in my select but it doesn't seem to work as I envisaged.

Multiple Results From $row[#]
i have a script for an image gallery, but in the spots where all the images (in the database) are supposed to be displayed, i have $row[6] on all of them, which produces like 12 of the same image... how do i get $row[6] to have different results from newest to oldest and automated?

SQL Results As Variables
I hope someone here can help me figure this out. I was looking for a way to give each result from a SQL Query a variable. My reason for doing this is I'm trying to make each result an item in a drop down box to make searching the database easier for the users. Anyone have any ideas? I know how to sort & arrange the results, but how do you assign each one (no duplicates) a variable?

Results Of A SELECT SUM
I am trying to figure out how to use the results of a SELECT SUM using Java (which I know very little about)

rs = databaseStatement.executeQuery ("Select sum (unitcost) as total from subcomponents")

The query seems to run correctly

so, I now say
System.out.println (rs.getString("total"));
or
System.out.println (rs.getDouble("total"));

and the result is

java.SQL.SQLException: Before start of result set

What am I missing.

Ordering Results In Php
The code for displaying such holidays is:

<a href="availableproduct.php?region=fiji">

Region is one of the fields in our database. Now here comes my problem. We have been asked to order the holidays for one region - i.e. Fiji, so that when they are displayed anything to do with Mana Island comes up first. Mana Island is not a region on its own and therefore is not a field. It will only be text in the title and/or description fields. Is this at all possible or no because it is not a region.

Can I Specify Order Of Results [was: Is This Possible?]
I am trying to query a table with real estate listings. I have variables company, price, bedrooms.

I want to do a search of all listings between x and y price, and between b and c amount of bedrooms. I can do all that stuff just fine. However, I want the query to return listings from MY company first. So basically a query there company=mine and then goes through the search, and then company=any and goes through again. Any idea how to do this or what its' called?

Displaying Results By Day
How can I display results by highest count for each day? For instance, top ten searches by day sorted by count().

Here is the code I've come up with so far, it manages to understand the current date and display some of the searches for that day, yet, does not display from highest to lowest, though the count() output works just fine.

$sql = "SELECT keyword, count(keyword) AS keywordcount,curdate() as date FROM queries GROUP BY keyword ORDER BY date DESC LIMIT 0, 10";

Here is what the db table looks like
id (auto_increment)
ip
keyword
time (datetime)

I know you might suggest using DATE_FORMAT(time,%Y,%m,%d) but i just could not figure out how to get that work correctly, maybe a BETWEEN? I think maybe the datetime format is messing it up and I need to separate days and compare.

How To Join Results ?

Is it possible to join results of the select query ? So If I get data from two rows (firstname, secondname)Can I somehow join query results like this: firstname.secondname Example: QUERY: SELECT firstname,secondname FROM table; RESULTS: John.Doe Mike.Monroe Kate.Moss

Is it possible to join results of the select query ?

So If I get data from two rows (firstname, secondname)Can I somehow join query results like this: firstname.secondname

Example:

QUERY: SELECT firstname,secondname FROM table;

RESULTS:
John.Doe
Mike.Monroe
Kate.Moss