Complex Select (Possible Subselect Needed?)

Two (rather Complex) Problems. Help Needed!
Problem 1

I am developing a system where members of a site can submit graphic designs to a themed pack. Members can then view and rate the submissions from 1-5. This has all been accomplished, but I have encountered one problem.

When listing the themed packs currently in progress, I want to be able to list how many submissions there are, how many of these submissions have a rating above 3.0, and how many of these submissions are above 4.0. I've been able to flawlessly count how many submissions there are without referring to their ratings with this query:

select
p.id as id,
p.title as title,
p.body as body,
p.reltime as reltime,
p.quota as quota,
count(s1.id) as subcount_ns,
from
packs as p
left outer join
submissions as s1
on s1.pack = p.id
where
s1.state > -1
and p.released = 0
group by
s1.pack
order by
p.id asc;
How can I also, within this query, count how many submissions have a rating above 3.0 and count how many submissions have a rating above 4.0? I'm not sure how to do this, since I have already joined the submissions table to the packs table.

The ratings table (sub_ratings) looks like this:



id | subid | userid | rating | time
The subid column refers to the submission's id, the userid column refers to the id of user who rated the submission, and the rating is an integer between 0 and 5.

--------------------------------------------

Problem 2

For the submission system, some users choose to collaborate with other users on one submission. In this case, both of their userids are grouped together in the "creators" column of the submission, being separated by commas. Here is an example:

1,43,65

This would be an example of three users having collaborated on one design.

Is there any possible way that I could, within one mysql query, determine the usernames of each of those users and return it as a column with the requested submission? I'm looking for something along the lines of php's "explode" function, but in mysql.


Thanks in advance for the help that anyone offers.

---

SQL SELECT Statement Help Needed
I have three databases, like so:

1. Visitors: vid(int) | vname(varchar)
2. Events: eid(int) | ename(varchar)
3. Participation: pid(int) | visitor(int from vid) | event(int from eid)

The participation table is a criss cross reference table to associate visitors with events.

Given the vid(from visitor table) how go i query the database to get all the events a given visitor is participating in?

---

Select Statement Needed
The sending page allows the user to choose which search criteria he
wants, so this (and many other) pieces of the select statement may or
may not be present. The statement always starts with

SELECT * from logdata where driver = '$_COOKIE[username]'

If the user has selected them and supplied the proper data for a search,
other elements will be added to the query string. For example, it may
look like

SELECT * from logdata where driver = '$_COOKIE[username]' AND amount >=
$_POST[txtLowAmount] AND amount <= $_POST[txtHighAmount] AND restaurant
regexp???

I'm not even sure if that is where a regexp belongs or if that is how it
should be used.
Ultimately, I would prefer it be non case sensitive.
Here are some examples of what I want:

If the user string is "CHIL"
Chili'strue
Charlie'sfalse
CHILtrue
chfalse

---

Complex Select
This is something I need to do with one select. Here is my select:

SELECT SUM(value1) FROM mytable WHERE rec_time BETWEEN '00:00:00' AND
'00:30:00' AND YEAR(rec_date) = YEAR(NOW()) GROUP BY MONTH(rec_date)

or

SELECT SUM(value1) FROM mytable WHERE rec_time BETWEEN '00:00:00' AND
'00:30:00' AND YEAR(rec_date) = YEAR(NOW()) GROUP BY LEFT(rec_date,7)

rec_time = time
rec_date = date

as you can see, I'm collecting summary of daily record around midnight and
building an monthly array like: 0,0,0,6.5,0,23.7,2,0,0,5.5,0,0

BUT! my function call requires a constant-length list. So if any months in
my selection range have no data, I need to fill the space with an empy
value. How can I do this? Perhaps with an helper table, but how?

---

Complex Select
This is something I need to do with one select. Here is my select:

SELECT SUM(value1) FROM mytable WHERE rec_time BETWEEN '00:00:00' AND
'00:30:00' AND YEAR(rec_date) = YEAR(NOW()) GROUP BY MONTH(rec_date)

or

SELECT SUM(value1) FROM mytable WHERE rec_time BETWEEN '00:00:00' AND
'00:30:00' AND YEAR(rec_date) = YEAR(NOW()) GROUP BY LEFT(rec_date,7)

rec_time = time
rec_date = date

as you can see, I'm collecting summary of daily record around midnight and
building an monthly array like: 0,0,0,6.5,0,23.7,2,0,0,5.5,0,0
BUT! my function call requires a constant-length list. So if any months in
my selection range have no data, I need to fill the space with an empy
value. How can I do this? Perhaps with an helper table, but how?

---

Complex Select
I have a table with 6 fields: id, project, section, division, subdivision, title.
Data like:
0 10 1 1 1 'Title111'
1 10 1 1 2 'Title112'
2 10 1 2 1 'Title121'
3 10 1 2 2 'Title122'
4 10 1 3 1 'Title131'
5 15 1 1 1 'Title111'
6 15 1 1 2 'Title112'
7 15 1 1 3 'Title113'
8 15 1 2 1 'Title111'
9 15 1 2 2 'Title112'

As you can see project 10 has:
4 10 1 3 1 'Title131'
and project 15 has:
7 15 1 1 3 'Title113'

And there are 4 records that match section, division, subdivision and title in each project.
1 1 1 'Title111'
1 1 2 'Title112'
1 2 1 'Title121'
1 2 2 'Title122'

I am looking for a select that will give me the following data:
1 1 1 'Title111'
1 1 2 'Title112'
1 1 3 'Title113'
1 2 1 'Title121'
1 2 2 'Title122'
1 3 1 'Title131'

Basically, I want all records from both projects but only 1 of the records that have similar data.

---

More Complex Select Query
i tried to do this also looking at documentation from mysql site, but keep getting error messages:
basically i got data on a table, there is a field 'views', and a field 'published_on', related to articles.
what i need is a query that selects the articles and orders them according to views/day.
so, i'd need some operation that first figures how many days the articles have been up:
DATEDIFF(NOW(), published_on)
then, i gotta divide that by published_on, and make the query return first the articles that have had the most views a day.
any idea how i can accomplish this?

---

Complex Select Statement
need help with this statement, that does'nt seem to be working.


PHP Code:

 SELECT a.user_id, a.bandname, b.*
FROM gig_users a
LEFT OUTER JOIN gig_album b
ON a.user_id = b.user_id
WHERE a.bandname LIKE 't%' OR bandname LIKE 'u%' OR bandname LIKE 'v%' OR bandname LIKE 'w%' OR bandname LIKE 'x%' OR bandname LIKE 'y%' OR bandname LIKE 'z%'
GROUP BY bandname
ORDER BY bandname ASC 

for the end result I need to display all the details from the gig_users table, only if the user_id exisits in the gig_album table, and starts with the letters t,u,v,x,y,z

Although the statement might look ok, it does'nt seem to work.

---

Complex Select Statement
My database has 43K rows and three columns. The primary key starts at one and counts up. I would like to select a row then use the data for some work and then select the next row.... all till I get to the last row. Could I just select a row at a time in sequential order or do I need to push results into an array to work? I'm using Perl. Should I save a select from my mysql database to an array... is this the best method?
I think I should use the code below but I'm not sure where it goes.
An array is returned that contains the requested names without any further information.

@arr = $sth->fetchrow;

---

Getting Rid Of Subselect
I have a table, which -- simplified -- looks like this:


create table access_logs (
session_id varchar(32),
request_uri varchar(32)
);
Each pageview logs the users session-id + the request-uri. Now, to determine how many visitors followed a specific path, I need to select the number of sessions, which have a row including specific request_uri.

This is my own feeble attempt, but I have a feeling that this could be rewritten to get rid of the subselects:

select session_id
from access_logs
where session_id in (select session_id
from access_logs
where request_uri = "landing-page.php")
and session_id in (select session_id
from access_logs
where request_uri = "exit-page.php")
group by session_id;

---

Getting Around Subselect
My knowledge of SQL is basic so I need some help developing a query. Suppose we have a table called BID where each row is a bid on an item up for auction. The relevant columns are BID_ID which is the primary key, ITEM_ID which identifies the item, and a BID_DATE which records the datetime of the bid.

I would like to find the most recent bid for each distinct ITEM_ID in the table. I've worked out the query below which seems to do the job. However, I need to find a query that will work on a pre-4.1 server which does not support subqueries. Is there a way to re-state this query without using a subselect? Perhaps using some kind of join?


SELECT *
FROM BID, (SELECT ITEM_ID AS IID, max(BID_DATE) as MAXDATE
FROM BID
GROUP BY ITEM_ID) as MAXDATES
WHERE
(ITEM_ID=IID) and (BID_DATE=MAXDATE);

---

Subselect
I had some SQL calls which worked fine on a v4.1 server and now I've moved to another one which is 4.0.24 and certain subselects no longer work. Is there any basic way to convert statements such as this:

SELECT a.name, (SELECT COUNT(*) FROM table2 AS b WHERE b.id=a.id) as count FROM table1 AS a So that it conforms to the 4.0.x standard?

---

Subselect
i am trying to remove values from a list menu if the join table
doesnt have keys when a key is selected for instance:

locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN
locations_join lj ON l.locationID = lj.locationID WHERE l.locationID NOT IN
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER BY
l.location ASC

so when shotlistID is selected all the keys from the locations_join joined
to the shotlistID would be remove from the locations list please help, i'm
trying to do this in one query saving from getting all the keys into an
array then checking if the values arent in the array when generating the
list. Code:

---

Subselect
Let's say i got this query:
select user.id, (select count(*) as posts_number from posts where posts.user_id = user.id), and some other fields, and a lot of joins here.Is it any way to *say* to mysql that the current user.id selected, is the one in the subselect ?
(the one from select "user.id" and the one in where ... = "user.id")

---

Subselect
Unless I'm wrong, here's a way to do a subquery (inner join two tables, then inner join the resulting table with a third table). It takes advantage of the two different ways of expressing an inner join ("INNER JOIN", and "t1, t2 WHERE...") to express two separate inner joins within a single statement.
SELECT p.p_id, v2.v_name FROM t_project p, t_volunteer v INNER JOIN t_volunteer v2 ON p.p_id=v2.p_id WHERE p.p_id=v.p_id AND v.v_name LIKE "%mike%";
Is this a technique that people use often? I couldn't see it documented in my SQL book ("MySQL", by Paul DuBois), even though it seems like a useful technique for what is effectively a subselect.

---

Subselect
I have been held up long enough on the query time to ask for help. Its basically a subselect that never returns.

SELECT id, it.org_id FROM import_temp3 AS it WHERE it.org_id IN ( SELECT p.org_id FROM join_to_person AS j, person AS p WHERE p.id = j.person_id AND j.value = '15' ORDER BY p.org_id ASC ) ORDER BY it.org_id ASC

If I break it up into 2 seperate

SELECT id, it.org_id FROM import_temp3 AS it WHERE it.org_id = 09238323 ORDER BY it.org_id ASC

SELECT p.org_id FROM join_to_person AS j, person AS p WHERE p.id = j.person_id AND j.value = '15' ORDER BY p.org_id ASC

They both return expected values.

---

Subselect / AS
SELECT
a.id
(SELECT width, height, filename FROM photos WHERE user_id = a.id LIMIT 0,1) AS (width, height, filename)
FROM users a

ORDER BY a.datestamp DESC

you can see my example, using with AS (example).
How can i extract values from subselects?

---

Slow Subselect
I've got two tables:
lo_users: nickname|id|...
lo_friends: from|to|...

The following query takes < 0.01 sec:
SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1'

...but if I use it in a subselect, the whole thing takes about 0.54 sec:
SELECT u.nickname FROM (SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1') f LEFT JOIN lo_users u ON u.id = f.userid

What can I do to make the query faster? "from" and "to" are indexed and lo_users.id is the primary key.

---

Subselect In 4.0.12-max With -- New Option
I was reading the manual and it said that the subselect is only
available in 4.1 or using the 4.0.12 with the mysqld =96new command line
to start it.

But it doesn=92t working!! So I downloaded the 4.1 alpha version with =
the
same problem. The error is:

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 =85.

Any ideas? I need subselect working.

---

Delete Subselect
I know that MySQL 3.23.nnn did not support a delete subselect, just wondering
what the best/most efficient way to do the following is:

delete from table_a where table_a.column_1 in ( select column_1 from table_b);

Assuming that column_1 is the same data type and size in both table_a and table_b.

---

Update Self Subselect
I have a log table that creates a row for every page view. I have a field called "flagged" which defaults to 0. I'm trying to update the flagged field to 1 when the ip count is greater then 30... This is what I have but I get the error "You can't specify target table 'ip_log' for update in FROM clause".

UPDATE `ip_log` SET `flagged` = 1
WHERE `ip` IN(
SELECT `ip`
FROM `ip_log`
GROUP BY `ip` HAVING COUNT(*) > 30
)

---

Subselect Wierdness
I am trying to get 3 active article IDs from the table ARTICLES for a random active feed from table FEEDS. Here is the query I have:

CODEselect AID, a.fid as FID from ARTICLES a where active='Y' and a.fid = (SELECT f.fid FROM FEEDS f where active='Y' ORDER BY RAND() desc limit 1) limit 3;

---

Subselect With NULL
why don't i get some results for the second query?

mysql> select * from a;
+---+
| b |
+---+
| a |
| b |
+---+

mysql> select * from a where b not in (select NULL from dual);
Empty set (0.00 sec).

---

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table
doesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN
locations_join lj ON l.locationID = lj.locationID WHERE l.locationID NOT IN
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER BY
l.location ASC

so when shotlistID is selected all the keys from the locations_join joined
to the shotlistID would be remove from the locations list please help, i'm
trying to do this in one query saving from getting all the keys into an
array then checking if the values arent in the array when generating the
list.

---

Get Last Records Details With A Subselect?
I have a ticketsystem where each ticket belongs to an user and each user can insert a couple of messages to one ticket. Therefore I have implemented a date field (used as primary key). Now I want to get details from the last entry belongs to a ticketid.

kdn_message:
updated (date)
ticketid (int)
kdnr (int)
detail
state

select * from kdn_message t
where updated in (select max(updated) as updated from kdn_message group by ticketid where ticketid=t.ticketid order by updated desc)

what is wrong i this statement?

---

Convert A Subselect To Inner Join
I was developing a php/postuke app for a client and I wrote two of my SQL queries with subselects.  I found out after I was done that they were pretty much stuck with MySQL 4.0.x for awhile, so I need to revamp my queries to avoid subselects.  The query uses three tables:

nuke_gwbt_guild_halls
nuke_gwbt_guild_halls_notes
nuke_gwbt_matches

I am getting all of the fields in the first table, matching the notes id from the second table to a notes id in the first table, and then counting some metrics from the third table to return as fields in the resulting recordset, used for ORDER BY sorting.  Here is the working subselect query: Code:

---

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table d=
oesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN lo=
cations_join lj ON l.locationID =3D lj.locationID WHERE l.locationID NOT IN=
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER B=
Y l.location ASC

so when shotlistID is selected all the keys from the locations_join joined =
to the shotlistID would be remove from the locations list please help, i'm =
trying to do this in one query saving from getting all the keys into an arr=
ay then checking if the values arent in the array when generating the list.

---

Subselect/left Join
I have a table like this

| ID | THING | NUMBER |
---------------------------------------------------------------
| 1 | white | 1 |
| 2 | white | 2 |
| 3 | green | 1 |
| 4 | green | 3 |
| 5 | brown | 1 |
| 6 | brown | 4 |

and I want to get just white back if I know two numbers are 1 and 2 or green back if I know the nubmers are 1 and 3.

Its mysql 4.1 so I am allowed subselects or left joins. I am drawing a blank!?

---

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table d=
oesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN lo=
cations_join lj ON l.locationID =3D lj.locationID WHERE l.locationID NOT IN=
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER B=
Y l.location ASC

so when shotlistID is selected all the keys from the locations_join joined =
to the shotlistID would be remove from the locations list please help, i'm =
trying to do this in one query saving from getting all the keys into an arr=
ay then checking if the values arent in the array when generating the list.

---

Transform SubSelect In OUTER JOIN
maybe I'm simply to dump but I could not transform this SQL-Statment
which uses a Sub-select and create on that uses an OUTER JOIN ....

---

Ranking Student Grade? With Subquery/subselect?
I am a mySQL newbie here and have some problem defining the mySQL 4.0.14
or 3.23 SQL to get student grade ranking where tied grade have the same
rank.

I used to set it through MS Access 2002 and use this kind of query:

SELECT nilai.studentNIS, nilai.studenttestmark,
(SELECT COUNT(*) FROM tblStudentGrades
WHERE [studenttestmark]>[Nilai].[studenttestmark];)+1 AS NomorUrut FROM
tblStudentGrades AS nilai ORDER BY nilai.studenttestmark DESC;

I've been looking around mySQL documentation and read that subquery can
be redefined as INNER JOIN or using two SQL statement via variable? I
have no idea on the basics of how to set it out though.

Could one of you please help give a me a sample on how this kind of
query should be done on mySQL? Is it possible to do it in single line?
And without having to use PHP/Perl scripts?

Or maybe I should have approach it differently?

---

Help Needed With DB Structure
I'm finally get a grip on using databases in my scripts, but I'm still lacking a bit in exactly how best to construct my databases ... so I'm hoping that I might be able to get some help with that here

I currently have a MySQL database with one table called "users." In it will be held the registration information for everyone who signs up to the site: username, password, date of registration etc. etc.

Now I want to link that table with another table that will contain information regarding the car that the user drives. This information will be collected from a second form and will contain data such as type, make, year of construction, colour etc. etc.

What's the best way of linking these two tables together in order to help with running searches or scripts in the future? I was planning on inserting the relevant username into the second table as well so that I can run searches across both tables ... but is that the best way of doing it?

---

MYSQL Help Needed
I am having troubles trying to create my first table in mysql

I am using version 5.0

I have entered the code below in mysql:

mysql>CREATE TABLE table_name (
-> column_1_name column_1_type column_1_details,
-> column_2_name column_2_type column_2_details,
-> ...
->);

and i get the following error:

ERROR 1064 (42000): 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 'column_1_type column_1_details,column_2_name column_2_type column_2_details,' at line 2

---

Syntax Needed
I have a table consisting of word_nr (key) and example_nr. Now i want to find out how many entries for example_nr are returned where word_nr=x and word_nr=y.
So i made something like:

SELECT count(*)
FROM mytable WHERE word_nr=x and word_nr=y

---

Query Needed
i have a little problem with figuring out how to make a query for a certain table. The table consists of two columns: word_no and example_no. It is very likely that an entry word_no refers to more than one entries in example_nr. Now i would like to find out how many entries with the same example_nr are there for word_nr x AND word_nr y together.

---

Timezone Help Needed
I am in Washington State, Pacific timezone. However, our business day runs from 7:00am-7:00am. I have found if I change the system time on the MySQL box to the Japanese Timezone this works in "fooling" the system into thinking 7:00am local time is midnight, the start/end of a business day.. the problem comes when trying to update our intranet website, with the differing timezones..the huge difference in time will not allow updates with our ppl who use front page.
(yes, I know notepad would be a better choice then front page. Not my call)

I have tried unsuccessfully to change the time zone in the [mysqld] & [mysqld_safe] sections of the startup INI file. When I try to force MYsql into the japan time zone the server fails to start and an error message pops up failed to start unexpectedly,

---

Performance Needed
I have two tables. One for products and one for reviews.
I have setup a detail page which displays the various fields out of the products table and then looks for all reviews in the reviews table which match the products key and display the average. All good so far.
I want to have a page which displays a whole list of the products and show their rating next to them but I'm wondering if making it search the reviews table and calculate the average for 50 products in one go is going to cause a bit of a strain on the server?
Is there another way I can do this, perhaps storing the precalculated average in the products table but how would I go about doing that?

---

SQL Statement Needed
How would I change in a table every reference to "ren" to "stimpy"?
If a value equaled "renTop" I want it changed to read "stimpyTop".

---

Help Needed Writing Query
I'm trying to make a query to return 10 sorted values, but I need to calculate the value to sort by, ie its not stored as a field in the table.
So say I had some data like this...

id basicrank time
1 5 40
2 7 33
3 3 55
etc.

I want to sort by (basicrank minus (currenttime - time)), so newer records get some priority, returning highest score first.
My query could then be something like this, given current time of 60.
SELECT id FROM table ORDER BY (basicrank-(60-time)) DESC LIMIT 10

---

Wordpress Database Help Needed!
I'm trying to import some content into the wp_posts database, which works, but I can't figure out how to make it automatically publish at a later date.

When I publish the imported content manually (eg. edit timestamp and hit publish) it works, but when I try and upload that into the database, it doesn't seem to register.

I'm doing some traveling soon and I don't want the blog to lay dormant! Here's my SQL for an example post:

Quote:

INSERT INTOwp_posts(ID,post_author,post_date,post_date_gmt,post_content,post_title,po st_category,post_excerpt,post_status,comment_status,ping_status,post_password, post_name,to_ping,pinged,post_modified,post_modified_gmt,post_content_filtered ,post_parent,guid,menu_order,post_type,post_mime_type,comment_count`) VALUES
(123, 1, &#55614;&#57159;-11-21 17:45:39', &#55614;&#57159;-11-21 23:31:15', 'I AM THE POST CONTENT', 'THIS IS THE POST TITLE', 19, '', 'future', 'open', 'open', '', 'this-is-the-post-slug', '', '', &#55614;&#57159;-12-01 17:45:39', &#55614;&#57159;-12-01 23:31:15', '', 0, '', 0, 'post', '', 0);`

So all times are set in the future, and the post_status is "future". Why is it not publishing when the time comes around?

---

Stored Procedure Needed
I have a table named players with these fields:

playerid {int}
teamid {int}
jersey {int}
playername {varchar(50)}
age {int}
And a table named pitchcounts with these fields:

id {int}
gamenum {int}
playerid {int}
pitchcount {int}
Now, what I need is a stored procedure to return the number of pitches each player pitched in a week where the weekno is passed
and the days rest required and next availability date of the pitcher based on the criteria below:

In-House Ages 13-16 (Babe Ruth)
--------------------------------------------------------
Pitches Days Rest
-------------------------------
0-20 0 Rest
21-40 1 Rest
41-60 2 Rest
61-95 3 Rest

********************************************************
In-House Ages 11 & 12 (Majors-American, National & Continental)
--------------------------------------------------------
Pitches Days Rest
-------------------------------
0-20 0 Rest
21-40 1 Rest
41-60 2 Rest
61-85 3 Rest
No more than 125 pitches in a week. 4 12's can pitch but no more than 240 pitches no matter how many games in a week.

********************************************************
Travel Ages 8, 9, 10, 11 & 12
--------------------------------------------------------
Player can pitch up to three innings in a weekend. Two innings doesn't affect In-House the next day, but prior In-House pitching does apply. If 61 or more pitches on Thurs or Sat no travel that weekend. A travel manager must obtain approval from the In-House manager in order to pitch a player for more than one inning if the in-house team has a game following the travel team on the same day. If pitchers does pitch more than 1 inning in travel, pitcher limited to 60 pitches In-House.
If travel follows In-House on same day, player can pitch in travel if no more than 60 pitches thrown in-house. These rules will be in place until may 1st. We want these rules to be followed in spirit as well as literally. If abuses are found, even if within rules, we will change them after may 1st. Under no circumstances is a player able to be made ineligible for In-House play.

********************************************************
In-House Ages 10 and 9'S in Majors (Majors American, National, Continental)
--------------------------------------------------------
Pitches Days Rest
-------------------------------
0-20 0 Rest
21-40 1 Rest
41-60 2 Rest
61-75 3 Rest
********************************************************
Double A In-House (AA)
--------------------------------------------------------
No more than 40 pitches in a game or 80 for a week.

********************************************************
Additional In-House Notes
--------------------------------------------------------
If a pitcher gets to 85 (75 at age 10) pitches in a game in the middle of a batter, they can finish the batter only. Pitching results must be reported by email to Director within 24 hours of the game by the winning manager. Pitcher can not re-enter to pitch in the same In-House game once removed from the mound. Pitcher receives seven warm-up pitches before their first inning on mound and five every inning thereafter. Warmup pitches no not count toward total counts. Pitches to final batter, if count gets past game or week totals, don't count toward totals. 12 year olds in Continental League can not pitch.

Rest Examples:

if 20 or less on Monday, available on Tuesday
if 21-40 on Monday, available on Wednesday
if 41-60 on Monday, available on Thursday
if 61 or more on Monday, available on Friday

---

Help Needed To Join Tables.....
I'm starting into mysql these days and I have some tables created

function_detail
+------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------+------+-----+---------+----------------+
| function_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| function_name | varchar(128) | NO | | | |
| function_description | varchar(512) | NO | | | |
| function_image | varchar(256) | NO | | | |
| function_image_caption | varchar(128) | NO | | | |
| function_extras | varchar(64) | NO | | | |
+------------------------+------------------+------+-----+---------+----------------+

option_detail
+----------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+----------------+
| option_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| option_name | varchar(64) | NO | | | |
| option_description | varchar(256) | NO | | | |
| option_image | varchar(256) | NO | | | |
| option_image_caption | varchar(128) | NO | | | |
| option_extras | varchar(64) | NO | | | |
+----------------------+------------------+------+-----+---------+----------------+

product_detail
+---------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |Extra |
+---------------------------+------------------+------+-----+---------+----------------+
| product_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| product_name | varchar(64) | NO | | | |
| product_short_description | varchar(256) | NO | | | |
| product_long_description | varchar(512) | NO | | | |
| product_series | varchar(64) | NO | | | |
| product_heads | varchar(64) | NO | | | |
| product_family | varchar(64) | NO | | | |
| product_category | varchar(64) | NO | | | |
| product_image | varchar(256) | NO | | | |
| product_image_caption | varchar(128) | NO | | | |
| product_catalog | varchar(512) | NO | | | |
| product_status | varchar(64) | NO | | | |
+---------------------------+------------------+------+-----+---------+----------------+

product_function_detail
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| product_id | int(10) unsigned | NO | | 0 | |
| function_id | int(10) unsigned | NO | | | |
+-------------+------------------+------+-----+---------+-------+

product_option_detail
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| product_id | int(10) unsigned | NO | PRI | 0 | |
| function_id | int(10) unsigned | NO | | | |
+-------------+------------------+------+-----+---------+-------+

product_technology_detail
+---------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| product_id | int(10) unsigned | NO | PRI | 0 | |
| technology_id | int(10) unsigned | NO | | | |
+---------------+------------------+------+-----+---------+-------+
these tables are meant for: (let's see if I can explain correctly)
Product Details would contain all the details for a certain products
Function Details would contain all the functions performed by these products.
Technology Detail would contain all the technology embeeded in these products.

Some products may have either the same technology or the same functions (with the same descriptions) but affirmatively not the same product details (description product name & etc).

For Instance
Product A would perform function a,b,c,d and e and would have technology X,Y and Z
product B would perform function f,g and h and would have technology W, Y and Z.
product C would perform function b,c and I and would have technology O,P,Q and X.

How can I link the three tables?
Meaning that product_detail ---> function_detail when the linkage between the two of them resides in product_function_detail?

Here is something I've tryied

SELECT function_id, product_id as producto FROM product_function_detail WHERE product_id = &#392;' ORDER BY function_id asc;

SELECT distinct function_name, function_description, function_detail.function_id
FROM (select function_id, product_id as producto from product_function_detail
WHERE product_id = &#392;' ORDER BY function_id asc)as xref, function_detail
WHERE producto=&#392;'
BUT so far, no luck.

---

M2M Relational Query Help Needed
I have 3 tables, below are a list of the significant fields in each.

Stories
* story_id
* story_date

Editions
* edition_id
* publication_id

Story_Edition_Jct
* story_id
* edition_id

What I need to do is create a list of all the stories that are newer than 30 days and have not been assigned to a given publication_id, but may or may not have been assigned an edition.

Just to clear that up some more, a publication consists of many editions, so we're looking for stories that may have been published in other publications, but want to restrict them from being republished in a given publication.

The following query is what I've come up with:

---

MySQL Join Needed
I'm pretty new to MySQL and I don't really get the whole join thing, although I understand the theory behind it I have problems when putting it into practice. Basically I'm using PHP with MySQL back end and I've got two tables:

TEST
QUESTION

The primary key of Test is "testno" which is an auto_increment and question has "testno" as a foreign key, so I need a join that will insert the value of "testno" into question when ever a record is entered into TEST based on the "testname" which the user enters into a form.

Let me try and clarify:

$testname = $_POST['testname'];
sql="INSERT into question test.testno where testname = '$testname'";

Obviously this will not work but what I need in effect is a query which carries out this function.

---

Packing Needed For MySQL
I am still playing around with databases. So here it goes:

Is packing needed for MySQL (5.0)?

I have a new DB, which already is 178 MB. Will that continue to grow,
when I add and delete data? Does it reclaim freed space and deleted
rows?

If not, is there a (automated) way to pack tables using SQL or
something simple, which I can call from my app?

---

Simple Code Needed
Just need a little code to transfer a user to a custom error page if connection to the database fails.
Like:
If(no connect){
header("location: error.php");
}

Just need the "no connect" part I guess.

---

Lemans Terms Needed
So can someone tell me in lemans terms what does LEFT JOIN do in MySQL? I see it more and more and have read the manuals but still don't understand. Maybe it's a mental block going on.

---

UPDATE / REPLACE - Assistance Needed
I have a table called COMPANY which holds 5,000 records
Fields: Company_ID (int:2), Name (varchar:100), State_2letter (varchar:2)
i.e. 1, ABC Company, AL
I have another table called STATE (51 records)
Fields: State_ID (int:2), State_2letter(varchar:2), State_Name (varchar:100)
i.e. 1, AL, Alabama
I want to change the structure of the COMPANY table to hold the STATE's table STATE_ID record to correspond with the State_2letter. But in the same time change the STATE_2letter to be an INT format too.
So, my COMPANY table will look like this...
i.e. 1, ABC Company, 1
I just cant find my solution on how to to an UPDATE command correctly.

---

Advice Needed On Database Schema
I am trying to come up with a proper database schema for this situation that's at least 3 NF. I'm just having trouble with a few of the associations.

First, here's the situation.

I want to store information about all the cards in a Tarot deck.

In the deck, there are two types of cards; namely the major and minor arcana.

Both of these have some common information I want to store, and I want to be able to choose just a random card, but they also have some data unique to each, and I also would like to choose a random card of a specific type.

For example, both types have an associated element. I will have an elements table probably with simply id and name, and perhaps an element_id in the main cards table.

Both also have an associated planet and zodiac sign. Most of the major arcana cards have either a planet or zodiac sign, but not both. Also the association is a little different. With the minor cards, the planet is a specific decanate within the sign. I guess that might not matter too much though in the storage of the data, though again it might if I wanted to retrieve the planets associated with the decanates of a sign.

Further, the major arcana have a specific Hebrew letter associated with each card, while the minor cards have no such associated letters.

Finally, each of the major arcana cards are classified as elemental, planetary, or zodiacal. The minor cards have no such classification.

So, how should I store these data? They are both cards, and have some common data, but both have very different data as well.

---