Column That Calculates Average Based On Other Columns?

Store Value Based On Two Columns
I have a table

column1...column2.....column3

column1 is an int

column2 is an int

I want to store column1/column2 in column3 and have it update each time column(1 or 2) updates.

What is the best way to do this?  I was thinking a trigger and a stored proc?  Is there any way to store that info without a trigger or stored proc?

---

Concat Columns Based On Certain Criteria Using Only SQL?
I have a big table (15 million records). Each row has a parsed address (number, prefix, street, type, suffix) and I need to take these separate pieces and concatenate them. However, often times a few of those pieces will be blank, so I want to prevent any unnecessary spaces.

I'm using MySQL 4.1. I found some documentation for using if statements for MySQL 5.0, tho I dont know if any of it works in 4.1.

The basic psuedo code of what I want to do is this:

---

Adding A Key Field Based On Other Columns
I have a MYSQL db based on an excel sheet

There's a membership number field that's mostly empty, there's five different levels of membership from full to part time and so on, each represented by a 'Y' in the relevant field, also a date field with the date that they first joined

What I want to do is allocate a unique membership number for each member, a six figure number starting at 100001 upwards for the full members starting with the oldest memberships, 200001 upwards for the part time members, and so on.

---

Logical OR On Columns? (Merging Two Or More Columns Into One Column)
Considering the following example table:

Column1 | Column2 | Column3
===========================
NULL |"Value1" | NULL
---------------------------
"Value2"| NULL | NULL
---------------------------
NULL | NULL |"Value3"

In each row only one column contains an actual value (a string in our
example), the other columns are set to NULL.

I would like to merge those columns into one single column, discarding
the NULL entries.

The desired result would look like this:

Merged_Column
=============
"Value1"
-------------
"Value2"
-------------
"Value3"

A '"SELECT column1 OR column2 OR column3 as "Merged_Column"' doesn't
work unfortunately. Is there an easy way to accomplish this on the SQL
Server?

The next problem I am going to face is that every column is in fact
made of a different data type (ie. Date, Int). Is there a way to
modify the merge select (if that's possible) to have it cast
everything into a single specific type? Ie. make everything in the
merged column a string.

---

Update One Column Based On Another
I have two tables. One set up like this:

id item_id group_id userid outcome settled

The second is

userid yes no

I would like to look at the first table and find all rows that have a specific group_id, then update the userid yes/no based on the outcome on that row. Outcome Y then add 1 to the yes column of that userid, outcome N then add 1 to the no column of the userid. Is this possible in one SQL statement?

---

Selecting Another Column Based On Max()
I've got a funky query I'm trying to set up. I've got this poll answers database, and each row has a qid that matches to a question. So I can group answers by the question they correspond to. Now I'm trying to select the total votes and the answer with the most votes for each question. The total votes part is working fine, but I'm having trouble with the winning answer part.

I've got a MAX(votes) that works well, and finds the answer with the most votes for every question, and returns its votes. What I want to do is select the `answer` column (the one that houses the actual text answer) of the answer with the greatest votes (the winner). Code:

---

Increment Based On Another Column
I'm having a little trouble working out the best way of doing something.

I'm storing a key split across two fields, the first field is 3 letters, the second is three numbers:
CREATE TABLE table1 (
id_a CHAR(3) NOT NULL,
id_b SMALLINT(3) NOT NULL,
...
);
What I'd like to do is have it so that when a new row is entered 'id_b' is calculated as MAX(id_b) + 1 WHERE id_a = new_id_a.

Is it safe to do this as some sort of nested query or is there a better way?

---

Selecting Based On Matched Column?
Lets say I have table with two columns zip_work and zip_home. I'd like to construct a query looking something like this:

SELECT {here is the problem} FROM myTable WHERE zip_home=99999 OR zip_work=99999 GROUP BY zip_home, zip_work LIMIT 100;

In response I'd like get two column table with zipcode as the first column and COUNT() of matched users. The idea is to know how many users are working or living in 99999 zipcode.

---

How To Insert A Calculated Value Based On Diff Column
let's say I have 3 columns
create table test1 (
id int,
dateCreated timestamp default current_timestamp,
localDateCreated datetime);

the localDateCreated is always 8 hours ahead dateCreated.

so when i insert id number, lacalDateCreated will be calculated based on dateCreated. How to do that?

I thought trigger might help, but it gave error

mysql> delimiter |
mysql> create trigger trgtest2ai after insert on test2
-> for each row begin
-> update test2 set local=new.ts where id=last_insert_id();
-> end;
-> |
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test2 set id=10;
-> |
ERROR 1442 (HY000): Can't update table 'test2' in stored function/trigger because it is already used by statement which invoked this stored fun
ction/trigger.
mysql> delimiter ;

---

Join Or Combine Two Tables Based On Unique Column
I use MySQL query browser and have made two tables in one database:

First table: Test
Column names: Filename (Varchar(255)),RowNumber(Integer),Bestandsnaam (Varchar(255)), Pad (Varchar(255)), Grootte (Varchar(255)), Created (Varchar(255)), Modified (Varchar(255)), Accessed (Varchar(255)), Deleted (Varchar(255))

Second table: Hashtest
Column names: Filename (Varchar(255)), RowNumber (Integer), MD5 (Varchar(255)), SHA1 (Varchar(255)), Pad (Varchar(255))

Of both tables the column RowNumber is the primary key. Also, in both tables the column Pad is the same (the same content, not exactly in the same order)

Now, I want to combine the two tables into one table, based on 'Pad'

I suppose I have to use the Script function of MySQL query browser. Please explain to me what I have to do to combine the two tables into one table. This third table has this columns:

Filename (Varchar(255)),RowNumber(Integer),Bestandsnaam (Varchar(255)), Pad (Varchar(255)), Grootte (Varchar(255)), Created (Varchar(255)), Modified (Varchar(255)), Accessed (Varchar(255)), Deleted (Varchar(255)), MD5 (Varchar(255)), SHA1 (Varchar(255))

---

Column Type In Computed Columns
Hi, i need to force a column type on a computed column. I'm using ADODB in a web app i'm developing but when i use computed columns the field type returned is a VARCHAR. In the case below i want it to return the column type as DATETIME for the complete_date column, so i can perform the conversion to a unit timestamp. Here is a segment of sql out of a larger query.

CASE
WHEN count(s_c_u_t.task_id) = count(s_c_u_t.complete_date)
THEN max(s_c_u_t.complete_date)
ELSE NULL
END AS complete_date

---

Generate A Column With Data From 2 Columns
I have 2 columns:

1) Division--with numbers 100, 101, 102,....

2) Stations-with numbers 1,2,3,...

I want to create another column say "Div-Station" with values 100-1, 100-2, 100-3, 101-1, 101-2.....so on. How do i do that in SQL server.

---

Best Average
OK. Bit of a maths question (at which I am useless)

Basically i've got a table with avg_rating and users.

avg_rating is the average rating of a specific id and users is that amount of people who have voted. I'm trying to sort the list by the best rating. However, if 50 users have voted an average of 2 for one id but only one user has voted 5 for another id, this id would be 1st on the list.

Is there a way I can sort the table by the best avg compared to the total of users that have voted?

---

Average Age
I have a dating script running which uses MySQL to store the users and their information. Now that I have some users, I would like to create a PHP file to show some site statistics. Actually I would like to get the average age of the users that are stored in the database, for example something like

Average age: (At this point I would just like the average age, for example 27.33)

Is this possible in some way?

---

Average Age
I got this players table and I do want to list its average age...

SELECT AVG(YEAR(SUBDATE(CURDATE(), TO_DAYS(birth)))) FROM players

This works fine for me BUT, it doesn't on my server though it isn't 4.1.XX as it is on my computer at home...

---

7 Day Average
I don't know if this can be done but I'm looking to write a query that selects the LAST 7 records the and gives me an AVERAGE.
Basically I have a table which logs the number of hits each day, I want to select the last 7 and see what my average is. How do i write something like this?
there is is only 2 fields in the counter table called date and counter.

---

Returning A One Value Average
I'm using PHP and MySQL, and am trying to return an average (songle value).
My code isn't working, and I'm getting confused between the mysql_fetch, here's my code:

$average = "SELECT AVG(overall) as overall, AVG(gameplay) as gameplay,
AVG(graphics) as graphics, AVG(sound) as sound FROM VIDEO_GAME_DETAIL
WHERE name=" .$row['name'];
$result = mysql_query($average);
$rating = mysql_fetch_array($result);

// down in an HTML table
echo "<td>" .$rating['overall']. "</td>";


$row['name'] is the name of a PC Game from the VIDEO_GAME table. In the VIDEO_GAME_DETAIL table I have reviews for many games, with ratings. My goal here is to average the different ratings (overall, gameplay, sounds and graphics) for a one game, and stick them with the main page that lists the games (1 row per game).

I get this following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Users/Marconi/Grad/lleccia/www/ISYG250/proj4/reviews.php on line 54

line 54 is $rating = mysql_fetch_array($result);

---

Running Average
How can I get mysql to do a running average of a given field using dates
from another column so that the average for each element is for a given time
period rather than from the 5 rows next to the element?

---

Average From Two Tables
I've two tables storing the scores of test marks. The following show the selected scores from the two tables of a certain person with id=1 and level=5:

# table1
id level score
1 5 80
1 5 80
1 5 100


# table2
id level score
1 5 40
1 5 60
1 5 50
1 5 70


The sum from table1 is 260 and the sum from table2 is 220 for a total of 7 tests. The grand total is 480.

I would like to be able to obtain the average of 68.57 (480/7) for a particular person of a particular level. Would I be able to do that with a single SQL? I've tried a number of sql queries but the don't work.

---

Running Average
How can I get mysql to do a running average of a given field using dates
from another column so that the average for each element is for a given time
period rather than from the 5 rows next to the element?

---

Average Length
Basiically I have a table called ‘Members’ with the following attributes, (the m is short for member), Members (mno , mname, date_joined, date_left, maddress, mtel)
I need to produce a single SQL statement that will output the longest length of current membership, the shortest length of current membership and the average length of current membership.And the out put I get should be easily understood, not just a number if you what i mean.

---

How Many Is Too Many Queries On The Average Web Page?
I'm just curious... I'm trying to watch how many queries I run in any given page. But I find myself being too conservative sometimes, trying to think of better ways to do things... but then end up getting frustrated.

Just looking for some average feedback. In your opinion or experience, how many queries to the DB using PHP is too many queries? I know less is better... i'm just trying to get an idea.

---

Query Fetching Average
I have 2 tables in a database. One table consists of persons. Each person appears only once in the table.....

---

Average Of Grouped Fields?
SYSTEM MOUNT KB_USED KB_TOTAL

host_1 / 1000 30000
host_1 / 1500 30000
host_1 /apps 1092 234343
host_1 /apps 7266 234343
host_2 / 4598 553444
host_2 / 4545 553444
host_2 /opt 3435 435345
host_2 /opt 4534 435345

What i want is, something like this

SYSTEM MOUNT AVG(KB_USED/KB_TOTAL)*100

host_1 / 4.166
host_1 /apps 1.78
host_2 / 0.82
host_2 /opt 0.91

How do I do this? Basically I want them grouped by mount per system.

---

Average Over Results From Query
I would like to write a query in which i calculate the average over the result from an earlier query. I have a transaction header table and a separate transaction lines table. I am trying to count an amount per transaction by summing the lines table, grouped by transactionnumber and would like to calculate the average over all of these sums.

---

Average Time Query
I have come stuck with averaging a column with type of Time.

I know there is a function AVG() that I can use to filter the average of a column, however, this brings back an integer.

Is there any way to return the average in time format (IE 00:00:00)?

BACKGROUND INFO:

SELECT `estimatedTime`
FROM job_sheet;

Returns,

01:00:00
21:30:00
01:00:00

The data type is time, and the default is 00:00:00.

---

High Average Load (50%)
I have a Windows 2003 server with MySQL 5 installed (5.0.19). The server has a 3ghz processor and 2gb of memory.

The server has about 20 databases of the type MyISAM and there is almost no load trough queries. The mysqld-nt.exe process has an average load of 50% and goes up when a query comes in. Why is this? I have tried logging to see what MySQL is doing but I cannot find anything abnormal.

---

Average Time Difference
I have a table which includes two timestamps - creationTS and processedTS.

I want to run a query that tells me the average difference between these two timestamps on a minute by minute basis, i.e. one row for each minute of the day, containing the total messages created in that minute and average amount of time it took to process data created in that minute.

Is this possible in a query, or do I need to grab each entry and use PHP to work out the average processing time?

---

Average Number Of Records
Say I have 10,000 records, each with a UNIX_TIMESTAMP()'d date field, and want to know the average on how many records have been created each a 24 hour period for the past 3 months. How would I go about doing that?

I tried the following but receive a group by error:
SELECT AVG(COUNT(*)) FROM my_table WHERE date >= TIMESTAMPADD(MONTH,-3,FROM_UNIXTIME(UNIX_TIMESTAMP())) GROUP BY id

---

Result With The Highest Average
I need to return the result with the highest average which should be calculated by dividing total_value by total_num_votes.

How can I do the division in the query to get the result with the highest average: ( total_value / total_num_votes )

I'm guessing it should look something like this:

SELECT ( total_value / total_num_votes ) as average FROM ratings ORDER BY average LIMIT 1

---

Average Query Execute Time???
I have a query taking about 4 sec that gets only 18 records.. That's not good. What can I do to speen up my Query?
<CFQUERY DATASOURCE="#datasource#" NAME="qry_job_search">
SELECT DISTINCT (t1.JobPostId), t1.Actionlkp, t1.Date,
tbl_job_posting.*,
tbl_job_post_requirements.*,
tbl_employer_info.*,
tbl_employer_url.*,
tblkp_employment_type.*,
tblkp_education_exp_lvl.*,
tblkp_salary_range.*,
tblkp_employment_exp_lvl.*,
tbl_job_post_location.*,
tbl_job_post_job_categories.JobCat,
tblkp_countries.*,
tblkp_prefectures.*
FROM tbl_job_post_history AS t1
JOIN tbl_job_post_location
ON tbl_job_post_location.JobPostID = t1.JobPostID
JOIN tblkp_countries
ON tbl_job_post_location.CC1 = tblkp_countries.CC1
JOIN tblkp_prefectures
ON tbl_job_post_location.CC1 = tblkp_prefectures.CC1
AND tbl_job_post_location.PrefectureID = tblkp_prefectures.ADM1
LEFT JOIN tbl_job_post_requirements
ON tbl_job_post_requirements.JobPostID = t1.JobPostID
LEFT OUTER JOIN tbl_job_post_job_categories
ON tbl_job_post_job_categories.JobPostID = t1.JobPostID
JOIN tbl_job_posting
ON tbl_job_posting.JobPostID = t1.JobPostID
JOIN tbl_employer_info
ON tbl_employer_info.UserID = tbl_job_posting.UserID
LEFT JOIN tbl_employer_url
ON tbl_employer_url.EmployerID = tbl_employer_info.EmployerID
JOIN tblkp_employment_type
ON tblkp_employment_type.ID = tbl_job_posting.EmploymentType
JOIN tblkp_education_exp_lvl
ON tblkp_education_exp_lvl.ID = tbl_job_posting.EducationExpType
JOIN tblkp_salary_range
ON tblkp_salary_range.ID = tbl_job_posting.Salarylkp
JOIN tblkp_employment_exp_lvl
ON tblkp_employment_exp_lvl.ID = tbl_job_posting.EmploymentExpType
WHERE t1.JobPostId NOT
IN (
SELECT DISTINCT (JobPostId)
FROM tbl_job_post_history
WHERE Actionlkp =4
AND date = (
SELECT MAX( date )
FROM tbl_job_post_history
WHERE JobPostId = t1.JobPostId )
AND JobPostID = t1.JobPostID
)
AND HistID = (
SELECT MAX( HistID )
FROM tbl_job_post_history
WHERE JobPostID = t1.JobPostId
AND Actionlkp =1
)
AND Date >= ( curdate( ) - INTERVAL 30 DAY )

<CFIF SESSION.COUNTRY_A NEQ "" AND SESSION.PREFECTURE_A EQ 0 AND SESSION.CITY_A EQ "">
AND tbl_job_post_location.CC1 = '#SESSION.COUNTRY_A#'
<CFELSEIF (SESSION.COUNTRY_A NEQ "" AND SESSION.PREFECTURE_A NEQ 0 AND SESSION.CITY_A EQ "") OR FORM.PREFECTURE NEQ 0>
AND tbl_job_post_location.CC1 = '#SESSION.COUNTRY_A#'
AND tbl_job_post_location.PrefectureID = '#SESSION.PREFECTURE_A#'
<CFELSEIF SESSION.COUNTRY_A NEQ "" AND SESSION.PREFECTURE_A NEQ 0 AND SESSION.CITY_A NEQ "">
AND tbl_job_post_location.CC1 = '#SESSION.COUNTRY_A#'
AND tbl_job_post_location.PrefectureID = '#SESSION.PREFECTURE_A#'
AND tbl_job_post_location.PostCity LIKE '%#SESSION.CITY_A#%'
</CFIF>
<CFIF (SESSION.CATEGORY_A NEQ 1) OR FORM.CATEGORY NEQ "">
AND tbl_job_post_job_categories.JobCat = '#SESSION.CATEGORY_A#'
</CFIF>
<CFIF (SESSION.KEYWORD NEQ "") OR FORM.KEYWORD NEQ "">
AND t1.Actionlkp = 1
AND (tbl_job_posting.JobTitle
LIKE '%#session.Keyword#%'
OR tbl_job_posting.JobDescription
LIKE '%#session.Keyword#%'
OR tbl_job_post_requirements.JobRequirements
LIKE '%#session.Keyword#%')
<CFIF SESSION.KEYWORD NEQ "" AND SESSION.PGV NEQ "brief">
<CFSET SESSION.PGV = "detailed">
</CFIF>
</CFIF>
<CFIF SESSION.EMP_TYPE_A NEQ 1>
AND tbl_job_posting.EmploymentType = '#SESSION.EMP_TYPE_A#'
</CFIF>
<CFIF SESSION.EXP_LVL_A NEQ 1>
AND tbl_job_posting.EmploymentExpType = '#SESSION.EXP_LVL_A#'
</CFIF>
<CFIF session.SRT EQ "D">
ORDER BY Date DESC
<CFELSEIF session.SRT EQ "C">
ORDER BY tbl_employer_info.CompanyName
<CFELSEIF session.SRT EQ "L">
<CFIF form.country NEQ 0>
<CFIF form.prefecture NEQ 0>
<CFIF form.city NEQ 0>
ORDER BY tbl_job_post_location.PostCity
</CFIF>
<CFELSE>
ORDER BY tblkp_prefectures.FullNameS, tbl_job_post_location.PostCity
</CFIF>
<CFELSE>
ORDER BY tblkp_countries.CC1, tblkp_prefectures.FullNameS, tbl_job_post_location.PostCity
</CFIF>
<CFELSEIF session.SRT EQ "T">
ORDER BY tbl_job_posting.JobTitle
</CFIF>
</CFQUERY>
Can anybody see a shortcut to get the same results??

---

Average Query Results Not Correct
This query works, but average results are not correct.

I don't no what the problem is!

Should i use subquery's?

Joining is not working fot this type of query's?

---

Average Query With 2 Rows From Same Table
My table:
"answer"
answerID
answer(int)
questionID(int)
userID(int)

answer1 is questionID = 1
answer2 is questionID = 2
WHERE userID is the same for both answer1 and answer2

I want the average of answer1/answer2: AVG(ans1/ans2), but how?

---

Find Average Amt Of Days Dates
I've been working on this problem for quite some time now. I've searched google and all over these forums, but didn't quite find what I'm looking for.

Here's what I'm doing:

mysql> SELECT idno, sro, pname, adate, cdate , TO_DAYS(cdate)-To_DAYS(adate) as days FROM turnaround group by idno,sro order by adate;
+------+----------+--------------+------------+------------+------+
| idno | sro | pname | adate | cdate | days |
+------+----------+--------------+------------+------------+------+
| 4 | S1111115 | Tupperware | 2003-05-03 | 2003-05-05 | 2 |
| 5 | S1111116 | Tupperware | 2003-06-03 | 2003-06-05 | 2 |
| 3 | S1111114 | Blab | 2004-05-03 | 2004-05-05 | 2 |
| 2 | S1111112 | Product Test | 2005-03-11 | 2005-03-11 | 0 |
| 1 | S1111113 | Big Tester | 2005-04-06 | 2005-04-08 | 2 |
| 6 | S1111111 | blah blah | 2005-11-18 | 2005-11-22 | 4 |
+------+----------+--------------+------------+------------+------+
6 rows in set (0.00 sec)

What I need to do now is find the average number of days. I've tried several combos of AVG() without sucess. Could someone point me in the right direction?

---

MySQL Order By Average Of Two Fields
I have made a Toplist mod for vBulletin, and it currently has a setting to choose to rank the sites by their in hits, or their out hits. I would like to add another setting that can rank them by the average of the in and out hits for each of the sites in the Toplist. Does anyone know how I can have MySQL order them by the average of those two fields?

---

MySQL Round And Average Functions
I have a column in a mySQL database table that stores a rating from 1 to 5.
I wish to select all rows from this table and get the average rating rounded to the nearest whole number.
I imagine its something like this

"SELECT ROUND(AVG(rating)) from ScoreTable"

---

Calculate A Moving Average And Updating A Table With It
I'm learning SQL and now I'm trying to update a table (koersen) with stockdata (date, ticker, open, close, volume, MA10vol).
The column MA10vol is empty and should contain the avg volume from the last ten records with the same ticker.
What I tried sofar:

SELECT @hdat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 0,1);
SELECT @ldat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 0,10);
SELECT tickerVL, AVG(totvolume) AS MA10
FROM(
    SELECT  SUM(k.volume) AS totvolume
        FROM koersen k WHERE k.datum BETWEEN @hdat AND @ldat
        GROUP BY k.tickerVL
    ) AS tijdelijk

The update part comes later. I'm now trying to make a subquery to get me the total volume of the last ten days. But I get a message that the subquery returns more then 1 row (more tickers I think).

But I want a query that updates every MA10vol for every ticker. I know I'm doiing something wrong but what?

---

How Do I SELECT FROM Columns Where The COLUMNS Has A PATTERN?
I've been looking far and wide on this one. I'm looking for a way to select from columns where the columns have a certain pattern!

Example:

Instead of doing:

SELECT q1, q2, q3, q4, q5, q6, q7, q8, q9 FROM exam

I would like to do:

SELECT q* FROM exam

or

SELECT q% FROM exam

Whatever will match everything that has the letter q in it. Anyone have an idea? I've been banging my head on this one.

---

Get Average Of A "count"
How to get average of a count(user);
Following syntax run very well
$records = mysql_query( "SELECT user,action,count(user) FROM log where date='$datetr2' and action='add' group by action,user")

But now I also want avg(count(user)), but it didn't work

$records = mysql_query( "SELECT user,action,count(user) ,avg(count(user)) FROM log where date='$datetr2' and action='add' group by action,user")

count(user) only get hot many times a user log into the webside a day. Right now I want to know the average times of user logging into the web within month. I don't know to get average number. avg() function doesn't work here.

---

Web-based Chat
I'm debating between 2 different table setups for a web-based chat system (gchat clone). The first is just one table labeled 'chat' which has the columns 'user_1' 'user_2' 'time' 'message'. The second would be dynamically creating/deleting tables with the user names as table names like 'user_1/user_2'. I don't know a whole lot about DB table organization but I was thinking the actual 'select' statement would be faster with seperate tables. Any ideas?

---

Using A Web Based Database
My web site host allows me to create MySQL databases on its database server. I've created a database as a trial. Now I would want users of my website to supply data to the database and for me to be able to extract it for analysis.

---

Web Based Application
Im pretty new to MySQL and been asked to develop a web-based application using MySQL for data repository. The site im developing has three different kind of access level i.e. Guest, Registered User, and Administrator. The last two users/group have similar privileges (Add/Update/Delete operations) except Administrator being the Super User.

My Questions:

(1)Say there are more than 100 registered users, do I have to add these users manually one after another using the Insert SQL command or is it better to use one Group ID since MySQL support concurrency connection?

(2)By default, table "user" of "mysql" database stores users who can access and manipulate the database. is it possible to create new database or table to store these users?

(3)What is the best way to store images, videos and audios in MySQL (for better performance)? is it storing file paths (its location) or storing it as Binary Objects)?

---

Select Based On FK
I have two questions regarding the MySQL v4.1.16 scenario below:

1. What should the FK on update and on delete parameters be (i.e., default, cascade, restrict, no action, etc.)?

2. How can I insert the correct id’s as implied below?

I have tried just about everything (including “Insert …select�, “LAST_UPDATE_ID();�, etc.) and whenever I think I am starting to get close I either get more errors or ‘0 records added’ with no error message;

I would greatlyappreciate someone who could instruct me on how to accomplish this task.

---

Web Based Application
i have a body that im doing a web based form for his small business.now he needs to have access to that form in all time s when he is at his clients even if they have no internet.
now i was thinking of installing wamp on his laptop and making a web based application.since wamp will act like a server he wont need internet.i think?
so my question is can i make a script that when he connects his pc to internet it will send the info store in the wamp db to his web server db?is that possible?

---

INNER JOIN BASED
I have a php script browsing a field from table 1, after user selects one the scripts adds selected field to another table. Now I dont want to browse already added fields. I have a query like this:

select table1.f1, table1.f2 from table1, table2 where (table1.f1=32 and table1.f3<>table2.f4)

Why this doesnt work??? it returns all fields (multiplying number of all records)!
I want fields from table 1 which does not exist in table 2...
What am I doing wrong....It looks so simple but I cant see the problem

---

Selecting Records, Then Summing Parts Of A Column, Then Sorting By That Column...
I’m keeping track of baseball stats, and each row represents one line of stats (from a box score) for one player of a single game. Because of this, a single player may have multiple rows in the table. I want to cumulate each player’s stats (so they’ll be one row per player) and display as output, which isn’t a problem. Then I want sort by a certain stat, but by now I’ve already looped through the table, so I can’t sort using a mySQL query at this point. I tried first putting values into an array in a previous project, but that became extremely complicated. What’s the best way to approach this?

---

Full Text Index And Full Text Search On Several Columns And Indivdiual Columns
1. Say if I want to do the full text search on three columns, "title", "description", "keyword". The search could be done only on one column, or combine any two columns, or combine all three columns, do I need to create full text index for all these possiblilities, like full text index on each column, and then full text index on any two columns and then full text index on all three columns?

If I have to make so many different kind full text index, that will slow my database performance or not?

2. Will the relevance also be calculated based on which column, such as the "title" will have higher relevance than "keyword", the "keyword" will have higher relevance than "description"?

---

Sql Statement Based On The Result Of Another?
I would like to use the result of one sql statement in another condition...

$select="select descr from ne_cats where " select sub_cat_id from ne_cats_rel, ne_cats where ne_cats_rel.cat_id='$cat_id'" "; .....

---

SQL Selection Based On Date
I'm using this function / string to pull data based on yesterday:

$tomorrow = date('Y-m-d',strtotime('tomorrow'));

How can I pull data similarily based on "last month"?

---