MYSQL :: Join 3 Tables, Limit Results From 3rd Table

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.

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 |
+---------------------+---------------------------------------------------------+----------------------------+------------------------------------+

Use A Mapping Table To Get Query Results From Several Tables
Ok, so I have 4 tables...

Projects -> id, project_name, project_type
Variables -> id, variable_name
Symbols -> id, symbol_name
Mapped -> project_id, variable_id, symbol_id

if I had to find

(project_id=5, variable_id=2, symbol_id=3) AND
(project_id=2, variable_id=14, symbol_id=1) AND
(project_id=34, variable_id=78, symbol_id=44)

How could I write a query that would return the results for these all at once?

I would like project_name, project_type, variable_name and symbol_name returned from their respective tables for each. Is that possible? I've been looking at inner joins and managed to make it work for 2 ids but not 3

Returning 1 Tables Results From Two Table Query
I am executing a query which finds a criteria from certain records in table A and returns all values from table B if the criteria in table A is true. I want to return only the data from table B but can't find an easy way to do that? E.g the MySQL 'From' expression contains both tables.

Use A Mapping Table To Get Query Results From Several Tables
Hi all,

Ok, so I have 4 tables...

Projects -> id, project_name, project_type
Variables -> id, variable_name
Symbols -> id, symbol_name
Mapped -> project_id, variable_id, symbol_id

if I had to find

(project_id=5, variable_id=2, symbol_id=3) AND
(project_id=2, variable_id=14, symbol_id=1) AND
(project_id=34, variable_id=78, symbol_id=44)

How could I write a query that would return the results for these all at once?

I would like project_name, project_type, variable_name and symbol_name returned from their respective tables for each. Is that possible? I've been looking at inner joins and managed to make it work for 2 ids but not 3 ....

Limit Results
Is there any way to limit the results of a query to the number of rows found minus 1? I'm trying to effectively emulate a logical or in a union query. I have three union segments, and I want either the X number of rows that the first two return, or the one row that the last segment returns (or nothing). So, I was hoping to be able to basically do something like select <...> limit (if(NUM_ROWS > 1, NUM_ROWS - 1, NUM_ROWS)).

For more of the backstory, I'm setting up alias resolution in postfix. What I need is to return the recipients (unknown number of rows) of a mail group (first segment), the owner of an alias (second segment), *or* a catch-all address for the domain if nothing else is found. I may be able to rework my postfix setup somehow, but I figured I'd ask about this first.

Results W/ A LIMIT BY
I have a table where I need to pull out the 5 latest records only. There is an INT 10 field called category. I only want one record from each of the following items in category (1,3,4,5,7). I can't do a Distinct because the other fields are not distinct. If I do a LIMIT BY, I can't guarantee that I'll have one of each. How do I get out the data?Here is my not working query:

SELECT username, title, category, price
FROM mister
WHERE category IN(1,3,4,5,7)
ORDER BY laststamp DESC
LIMIT 5

Limit On Matching Results Only?
Is there a way to do a limit with a starting and ending value that works like a:

LIMIT 10, 20

only it pulls the 10 resulting rows that matched the where clause of the query vs. just showing the 10 rows regardless of the where clause?

Example:

select product_name from products where product_count > 0 limit $start, $end;

The desired result is that the query returns however many rows fall between the value of $start and $end, lets say 10 rows, but the 10 rows returned are the first 10 rows that matched the where clause of the query and not just the next 10 rows of the table.

Limit Return Results
I am using perl. I am looking for ideas on how to return 10 rows at a time and then have the number of pages on the bottom of the web page so the user can view the first 10 rows then click on page 2 and view the next 10 rows, and so on..

LIMIT And The Potential Amount Of Results
I want to know how many results I WOULD have had if I didn't set a limit.

SELECT SQL_CALC_FOUND_ROWS * FROM tablename LIMIT 0,5;
SELECT FOUND_ROWS();

Get Total Number Of Results But Still Use LIMIT?
Is there a (better) way to get the toal number of results from a query
but still limit them with a LIMIT clause. For example, in a Google
search result, it displays 1-100 of 10,000,000 results. Do they hit
the database once for results, and again for a total count or is it
all in one query?

Limit Search Results For A User
I am having some problems setting up user permissions on my MySQL database. I would like to limit the search results (rows) when a user submits a query.

I know this can be done on the programming side when you conduct the query, but i was wondering if it is possible to limit the user's results from the permissions available in the MySQL user's properties? Purely for a more secure setup for that user.

Also, another question I had was if it is possible to limit the number of queries for that user per IP address in a given time frame?

Select Data From 2 Tables (join) REGARDLESS Of One Table Not Containing A Row
SQL
SELECT o.title, o.quantity, o.price, o.product, i.supplierFROM store_order_inv o, store_inventory iWHERE o.product = i.productAND o.cart_order_id = ?-195509-3867'

my only problem is if a row with Product ID doesnt exist in inventory, no result is returned, EVEN if a row exists in order_inve table with that product.

So my question is, is it possible to select data from 2 tables, but where the presence of a row with matching PRODUCT field in the inventory table is OPTIONAL

How To Use LIMIT And Show Results In Opposite Direction?
I've got a table like this:

-------------------
| id | data | exp |
-------------------
1 ab 123
2 ve 156
3 fe 204
4 fg 256
... .. ...
n dc 486

in a database that every 15 minutes add some lines at the end of table. The exp field is the only one that increase gradually. Suppose I've got 2800 lines at a certain moment of the day and I want to take just the last 96 from the min value to max, how can I do? I've tried with "ORDER BY exp DESC LIMIT 96" but I need last 96 lines in ASC order.

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

How To Create A Join/relationship Table That Maps To Many Tables
Let's say I had the following table structure:

CREATE TABLE customer
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service1
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service2
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service3
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE subscription
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service
(
subscription_id INT UNSIGNED NOT NULL,
service_type TINYINT UNSIGNED NOT NULL,
service_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service_type, service_id)
) ENGINE = INNODB;

CREATE TABLE service_type
(
id TINYINT UNSIGNED NOT NULL,
service_table_name VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;
INSERT INTO service_type (id, service_table_name) VALUES
(1, 'service1'),
(2, 'service2'),
(3, 'service3');
I need a way to map Customers to Subscriptions to Services.

Please keep in mind that one subscription record can be related to many services: even services of the same type.

The above structure would work, but I wouldn't be able to make it transactional. The problem lies with the subscription_2_service table's service_id column. That column doesn't actually reference any other table. It's conditional, meaning its value changes depending on what the service type column value is.

This makes it impossible to implement transactions because the service_id column references nothing concrete.

I considered another way of doing the above by sticking a subscription_id column in all three service tables. I don't like this approach because not all services will require a subscription. This would result in many NULL values and tight coupling between the subscription and service tables. It also seems sloppy/scattered.

I also considered a third way of creating subscription_2_service tables, such as:

CREATE TABLE subscription_2_service1
(
subscription_id INT UNSIGNED NOT NULL,
service1_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service1_id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service2
(
subscription_id INT UNSIGNED NOT NULL,
service3_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service3_id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service3
(
subscription_id INT UNSIGNED NOT NULL,
service3_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service3_id)
) ENGINE = INNODB;
This third method above seems ridiculous.

The third approach does decouple the subscriptions and services from one another. Services aren't aware that they're part of a subscription and subscriptions aren't aware of what services have subscribed to them. This method still seems bloated and wrong though.

Ideal Way To Preserve All The Parent Table Entries In A Join With Many Child Tables
The problem is: How do I join a huge parent table with many child
tables (more than 5 child tables) preserving all of the parent table
entries. Lets say there is the parent table parentTable and three
child tables childTable1, childTable2, childTable3. In order to get
the data after joining these tables the query that I have been using
was:

select parent.field1, parent.field2, parent.field3, child1.field4,
child1.field5, child2.field6, child3.field7 from ParentTable parent,
childTable1 child1, childTable1 child2, childTable3 child3 where
parent.fielda = child1.fieldb and parent.fieldc = child.fieldd and
parent.fielde = child.fieldf.

Although the tables are huge (more than 100,000 entries), this query
is very fast, however those parent table entries which do not have
child entries are lost. I know that I can left join a parent table
with a child table and then with the next child table and then with
the next child table and continue. Isn't there a simple solution for
this commonly happening problem?

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

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.

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.

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.

Joining Tables With A Limit
I have two tables, they are

ARTICLES and ARTICLE_IMAGES

Each article can have up to 5 images, the images are displayed on the "VIEW ARTICLE" page. This works fine.

The problem is the "ARTICLE LISTINGS" page. The page displays a summary of each article, the issue i am having is that i also needs to display the first image (image with the lowest id) alongside each summarised article. Is this possible? At the moment it is displaying ALL images, i only want it to display jsut the one, i.e. the one with the lowest id.

Limit On Number Of Tables In Mysql
In the mysql cluster docs it says 1600.

but is that something different from mysql? I am just interested in plain mysql.

Distinct, Order By And Limit From Multiple Tables
(I am slowly going insane over this problem)

I want to fetch the "items" that received the latest 5 comments, without showing the same item multiple times (when it has, for example, 3 comments that fall into the "latest 5 overall" category)

When I run the SQL it uses the first comment on each distinct items.id (while it should show the last/latest), and the results are therefore rather useless.

I'm using MySql 4.1.9

(I did search and see some similarish threads, but I'm too braindead right now to extract a solution on my own)

SELECT items.id, items.name, comments.id AS comment_id, comments.created AS thetime
FROM comments, items
WHERE
comments.parent_id = items.id AND
items.status = 0
GROUP BY items.id
ORDER BY thetime DESC
LIMIT 5

CREATE TABLE comments (
id INTEGER(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(255) DEFAULT NULL,
created DATETIME DEFAULT NULL,
parent_id INTEGER(11) DEFAULT NULL,

UNIQUE KEY id (id)
)TYPE=ISAM ;

CREATE TABLE items (
id INTEGER(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
status INTEGER(11) DEFAULT Ɔ',

UNIQUE KEY id (id)
)TYPE=ISAM ;
--

Cannot Alias Locked Tables / Join To Unlocked Tables ?
If you manually issue a table lock then query that table, aliasing the table generates an error. If you try to join the table to another table that is not locked, you will receive an error. What is the reason for this?

Getting Results From 3 Tables
I have three tables: Person with key nId plus other fields, volchar with fields nVolCharId, nId, nActivityNum and activitynames with two fields nActivityNum and txtActivityName.
One person can be signed up for many activity so I want my output to look like: LastName, FirstName, etc plus a list of txtActivityNames (eg. Smith, Bob (activities: golf, swimming, basketball)).
Is there a way to structure a query to get those kind of results so they can be displayed in table form?

Merging Results From Two Tables
I would like to ask for some help fixing this query:

How To Combine Results From 2 Tables ?
This is for a website for classified Ads. The Ads can be posted by registered users or the Ads can come from external partner sites.

Therefore, I have kept 2 user tables - one for registered users and one for non-registered users, and 1 table for Classified Ads. The Ads table holds Ads from both registered users and external sites, with a column which indicates the source of Ad, i.e. it has two values internal and external.

I want to display all Ads on a page. The user info is to be read from the 2 user tables depending on the source of the Ad. Most of the fields of the 2 user tables are same. However, since we ask registered users to provide more information, the table for registered users has more fields.

Sorting Results From 2 Tables
I have 2 tables with movies and directors. In a third table the movies are connected with their director(s). A movie may have several directors. I need to have a list with movie titles and their director(s):

Title 1 (Director 1)
Title 2 (Director 2)
Title 3 (Director 3)
Title 4 (Director 1, Director 2)
Title 5 (Director 2)
etcetera...

Is there a way to put the directors with their movie without getting several results per movie? Or should I filter the results afterwards in PHP?

Ordering Results From Two Different Tables
I have a two tables as below:

Table: Invoices
id | InvNum | InvDate | CustNum | etc....
---------------------------------------------
1 | 100332 |2005-04-03| 1 |
2 | 100333 |2005-04-01| 2 |

Table: Customers
id | Name | Address | etc.....
--------------------------------------
1 | Bill's, Inc | 95 Lane |
2 | Abe's, LLC| |

Where Invoices.Custnum = Customers.id

I am obviously keeping two tables because the multiple invoices may have the same customer.

Anyway, I have a search tool for the invoices allowing the user to search by multiple variables (Such as invoice number, date, etc.) and order the ensuing results. One of the sorting options I need to have is by customers.name asc, invoices.invdate desc. What I need is a query that will give a result set of all the invoices matching the search parameters sorted first alphabetically by the customer's name and then by the date. I am sure that there is a way to do this but I am not very familiar with JOINS. So first does anyone have any ideas? Secondly, can anyone recommend to me a good tutorial on JOINS?

Sort With Limit But Sort ALL Results.
I have a query that limits by 50 results at a time and I also have an order on a int field. The issue I'm having is it's only ordering by the 50 it returns, how can I have it order by the entire results set and then give me back only 50 results?

Querying From Two Tables - Multiple Results?
I have two tables, A and B. A has 10 rows and B has 5 rows. I make a query like:

SELECT a.name, a.address
FROM A a, B b
WHERE a.zipcode!=b.zipcode

The problem is that since there are 5 rows in B, I get back 5x the results. I can use LIMIT except the number of rows in A is not known, it may not be known. How can I fix this?

Combining Results From Two Pairs Of Tables
Is there a way in Query Browser to view a trigger? What about in MySQL Administrator? I can't seem to find a way to view a trigger after it has been created.

Also, wil the triggers be saved in the backup?

Querying From 2 Tables With Strange Results
What I want is to run a query that select the complete.a2 field and echos the value, where dn_records.number='5553331111'

Whats happening is all the values for complete.a2 are echoing back, even if dn_records.number isn't equal to '5553331111'

I am getting same results using the below php script as I am usnig the MySQL Query Browser.

<?php
mysql_connect( 'localhost', 'user', 'pass' )
or die( "Error! Could not connect to database: " . mysql_error() );

mysql_select_db( 'customers' )
or die( "Error! Could not select the database: " . mysql_error() );

$result = mysql_query("select complete.a2, dn_records.number from complete, dn_records where dn_records.number='5553331111'")
or die(mysql_error());

while($row = mysql_fetch_array( $result )) {
 echo "<tr><td>";
 echo $row["a2"];
 echo "</td></tr>";
}

?>

Joining Tables & Grouping Results
I am working with two tables, one listing members and another listing transactions they have made. I would like to create a list that has member details (from the member table) and total value of transactions each member has made based on how many entries in the transaction table that member has.

I guess I can run a query like this:
select * from member, transaction where member.id=transaction.member_id
and then go thru each row to produce a summary of results im after...

Merging The Results Of Multiple Tables
I have two tables. The first table is "users", the second table is "songs". The idea is that each user (one record in the users table) can upload several songs (several records in the songs table).

I have it so that each new Song added, has a foreign key that points to a user record. So dozens of Songs could all point to the same user.

My question is, I want to run a query that returns information about a user, including all their songs. So, the return table would look like:

username alias songname1 songname2 songname3
username alias songname1 songname2
username alias songname1 songname2 songname3 songname4

etc.. The songnames come from the SONGS table, and username/alias come from the USERS table, but the final result should be one table. What I've managed to get working is returning all the songs of a user, but as different rows in the table:

username alias songname
Bob bob Happy Song
Bob bob Sad Song
Bob bob Super Song

But I really want that to be returned as:

username alias songname1 songname2 songname3
Bob bob Happy Song Sad Song Super Song

So I want to merge n number of sub records, into a single user record, and return the results as 1 per user. How do I perform this feat?

Looking For Help In Getting Proper Search Results From Two MySQL Tables
I am building a site where there are several travel related products in a MySQL database. Each product has "x" amount of items. For example, Harrison Hot Springs Hotel has 3 items - Cash price, Points, and Points plus Cash.

I need to do a search on "items" that are equal to or less than the number of Points (item_points) and just can't seem to get it right. Their are two tables involved "products" and "prod_items".

This is the code I am using to get search results by product category and it works just fine. I'm sorry I've tried to indent my code properly so it's easier to read but don't know how to do that here.

//get product info and show
$get_prods = "select id, prod_name, city, prod_brief, valid_m, valid_d, valid_y, expiry_m, expiry_d, expiry_y from products where category='$category' order by city, prod_name";
$get_prods_res = mysql_query($get_prods) or die(mysql_error());

while ($prods = mysql_fetch_array($get_prods_res)) {
$prod_id = $prods[id];
$prod_name = stripslashes($prods[prod_name]);
$city = stripslashes($prods[city]);
$prod_brief = stripslashes($prods[prod_brief]);
$valid_m = $prods[valid_m];
$valid_d = $prods[valid_d];
$valid_y = $prods[valid_y];
$expiry_m = $prods[expiry_m];
$expiry_d = $prods[expiry_d];
$expiry_y = $prods[expiry_y];

echo "$prod_name - $city - <a href="prod_details.php?prod_id=$prod_id">Details</a> 
$prod_brief 
Purchase options for this product are valid until $expiry_m $expiry_d, $expiry_y ";

$prod_name = addslashes($prods[prod_name]);

//get item codes
$get_codes = "select item_code, item_desc, item_points, item_cash from prod_items where prod_name = '$prod_name' order by item_code";
$get_codes_res = mysql_query($get_codes) or die(mysql_error());

if (mysql_num_rows($get_codes_res) > 0) {
while ($codes = mysql_fetch_array($get_codes_res)) {
$prod_name = stripslashes($prods[prod_name]);
$item_code = $codes['item_code'];
$item_desc = $codes['item_desc'];
$item_desc = stripslashes($codes['item_desc']);
$item_points = $codes['item_points'];
$item_points = number_format($item_points);
$item_cash = $codes['item_cash'];
$item_cash = number_format($item_cash, 2);

if ($item_points == 0) {
echo "$item_code - $item_desc - $$item_cash ";
}
elseif ($item_cash == 0.00) {
echo "$item_code - $item_desc - $item_points points ";
}
else {
echo " $item_code - $item_desc - $item_points points plus $$item_cash ";
}
}

}

}

Most Recent Results, Without Using Subqueries Or Temp Tables.
I have a table whose schema is essentially as follows:

Results(name varchar(20), version varchar(20), configuration varchar(20), status varchar(20), datetime timestamp(14))

Essentially, what I would like to accomplish is to get the most recent record for name, and also for the combination of name and version.

I'm using MySQL 4.0.12 and therefore do not have access to subqueries, and would like to avoid using a temp table, which I am doing right now with limited success and considerable overhead.

I've also tried using a group by statement and using max(datetime) to get the most recent timestamp, but I end up getting the right timestamp, but the incorrect values in all of the other columns.

Table Row Limit
Is it possible to create a table which cannot contain more than X rows?

Joining Two Tables And Getting Only Those Results, Where No Matching Entires Have Been Found
I have two tables, "fantasybets" and "fantasybets_placed".

What I want to achieve, is to query "fantasybets" and "fantasybets_placed" to only display those bets, where a given user has paced no bets yet.

Here are examples for my tables:

BETS
______
bet_id
bet_name

BETS_PLACED
______
bet_placed_id
bet_placed_username

SELECT * FROM bets
LEFT JOIN bets_placed
ON bets.bet_id=bets_placed.bet_placed_id
WHERE ((bets_placed.bet_placed_id IS NULL)
OR (bet_placed_username <> "given_username"))

However, this doesn't work as expected, as i get all those bets for a given user, where some other user has placed bets on too. However, I only want those bets, where a defined user "given_username" has NOT placed a bet yet.