MYSQL :: SUM Of Two Foreign Table Fields

Foreign Fields
I have created a database with about 17 tables. I have been creating foreign
keys some of which have worked but when creating others I get the message
below

*************************
1005 (ER_CANT_CREATE_TABLE)

Cannot create table. If the error message refers to errno 150, table
creation failed because a foreign key constraint was not correctly formed.

***************************

I have checked to see If there are any obvious differences between the
tables that allowed the foreign keys to create and those that wouldnt. But I
could not find any differences

Multiple Fields Linking To One Foreign Key
I have a table that is going to track project info, time, date, people working on the project, materials, etc.

My question is what would be the best way to have multiple fields for the people working on the project, for example this is my current layout (don't laugh too hard):

tbl_washLog (washID*, time, date, crewID**, crewID2**, crewID3**, tailID, paintID)

The crew ID fields need to be foreign keys back to the crewID field in the crew table which is:

tbl_crew (crewID*, firstName, lastName)

(* = Primary Key, ** = Foreign Key)

Is it possible to have crewID, crewID2 and crewID3 all be foreign keys of tbl_crew.crewID? If not, what would be the best way to go about doing this?

Insert Using Foreign Keys And Other Fields
I am trying to create SQL commands to initialize a database with several tables tied together using foreign keys.

Table 1:
id1 int primary key auto_increment
state varchar

Table 2:
id2 int primary key auto_increment
fk1 points to table 1, id1
city varchar

So I can create two SQL commands:
INSERT INTO Table1 (state) VALUES ('Illinios'), ('Virginia'), ("Massachusetts');

INSERT INTO Table2 (city, fk1) VALUES
('Springfield', 1),
('Springfield', 2),
('Richmond', 2),
('Springfield', 3);

My problem is that if I create a new dataset using the same two commands, but add a state at the beginning of the file (or remove one), the keys change, and the 2nd insert statement will associate the cities with the wrong state, or fail.

I would like the second statement to be something like:

INSERT INTO Table2 (city, fk1) VALUES
('Springfield', SELECT id1 FROM Table1 WHERE state='Illinois');

or something like that. Is there any way for mySql to do this?

Fields In One Table Overwrite Fields In Another Via JOIN
I'm writing a simple web game which uses mysql to store data. The prototype works ok so far, but now I'm refactoring it to support multiple players. This means coming up with a way to store the player's current world state.

I'm thinking of doing this by having a table that defines the initial world state, and then a table containing the 'state' of anything different.

Example:
The 'objects' table looks like this:
object_id, name, room
1, Hammer, 1
2, Mirror, 1
3, Spade, 2

So, at the start, room 1 contains a hammer and a mirror, and room 2 contains a spade.
Now, if player 12 picks up the hammer then drops it in room 2, I store this override in the 'state' table

player_id, object_id, room
12, 1, 2

So. I'm trying to see if there's any kind of join syntax that would return all the objects in a room for a given player's 'session'. Obviously it could be done with temporary tables, or just comparison outside of SQL, but I keep thinking this might be possible *somehow*

One Table With Many Fields Or Many Tables With Few Fields?
I need to build a database, but I'm torn between these 2 choices:

Is it better to have one table which has many fields
or
many tables which each has few fields?

Is it true that the latter is worse because it will require many join operations?
What is the limitation of the first option (one table with many fields)?

Selecting From One Table When Foreign Key Is In Second Table
I have a table where the primary key is cid. I want to display all records in that table only if cid is also in a second table as a foreign key but am not totally sure how to do it.

Foreign Key On MyISAM Table?
Am I right in thinking that foreign keys currently do not work with MyISAM tables at all?

Foreign Key To Existing Table?
Is it possible to add a foreign key to a table that already exists? Or do I have to drop the table and recreate it?

Altering My Table To Add Foreign Key
i have two tables, cars and people.

cars:
make
model
engine

people:
name
address
sex

what i would like to do is alter my table to create a foreign key 'MAKE' in the people table. i dont know the syntax to do this.

Which Table Is Foreign Key Constraint On
When trying to delete a record from a table, I receive the message
"[root@localhost:3306] ERROR 1217: Cannot delete or update a parent
row: a foreign key constraint fails". I know there is a foreign key,
but don't know what table it is on. Is there an easy way to find out
what table the foreign key is in that is restricting the record from
being deleted.

INSERT ID From Foreign Key Table
I have a form used to populate 2 tables: Provider and Contacts. Provider has
a foriegn key column - Contact1ID - which has a foreign key relationship
with the prmary key - ID - in the Contacts table. The form submits data to
Contacts then Provider but I need the ID generated by the Contacts
submission to be entered into the Contact1ID column in the Provider table.

If I use this:

INSERT INTO Provider(Contact1ID)
SELECT ID FROM Contacts
WHERE FirstName='$contact1{FirstName}'
AND Surname='$contact1{Surname}'

.... all I get is a new row.

UPDATE doesn't seem to allow SELECT to grab the new ID either.

Any ideas?

Adding Foreign Key To Table
How do i go about doing this in mysql, Ive checked on the internet but I can't find the syntax for it.

Assigning Foreign Key After Completion Of Table
I know how to assign a primary key after completion of a table does anyone know how to set a foreign key?

How To Make One Table Has Two Foreign Keys
i just start to use mysql and i want to know how to write the command to make two fields as a foreign keys For Example,

table 1
school_code (PK)
School_room
Bldg_code
Office_room (FK)
Prof_id (FK)

how can i write this command using constraint?

Associative Table Or A Directly Foreign Key
I am working with hibernate and i want to map my Objects to tables structre.

There is two ways to map relation:
1. with associative table
2. directly foreign key (i mean without associative table)

Can you please explain what are the advantages for each way?

Cannot Modify Table To Create A New Foreign Key
I used this syntax:

ALTER TABLE table1 CHANGE field1 field1 char(60) NOT NULL REFERENCES table2(field2)

field1 is INDEX for table1
field2 is PRIMARY KEY for table2

table1 and table are InnoDB type

I'm getting this 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 'REFERENCES table2(field2)' at line 1

What's the problem ? I followed MySQL Reference Manual to create the query, it' supposed to work.

Assigning Foreign Key After Completion Of Table
I know how to assign a primary key after completion of a table does anyone know how to set a foreign key??????

Adding A Foreign Key Into An Existing Table
May I know how should I do that?

The SQL statement should be:

CREATE TABLE USER_SYSTEM_SECURITY (
USERID VARCHAR(32),
HOSTID VARCHAR(32),
HOME_DIRECTORY VARCHAR(32) NOT NULL,
ACCESS_RIGHTS INT(3) NOT NULL,
PASSWORD_EXPIRY_DATE DATE,
ROLES VARCHAR(32) NOT NULL,
PRIMARY KEY (USERID, HOSTID),
FOREIGN KEY (USERID) REFERENCES MEMBER (USERID) ON DELETE CASCADE,
FOREIGN KEY (HOSTID) REFERENCES SYSTEM (HOSTID) ON DELETE CASCADE);

But I forgot to add in the last row during the table creation.

Foreign Key Relations In MyISAM Table
I am trying to setup a database with 2 tables 'Items' and 'Categories'. There descriptions are:

Items(itemID,itemCategory,itemDescription)
Categories(Category)

I have added each of the databases, but have tried using the following to create a Foreign Key Relationship, but this doesn't appear to work.
---
ALTER TABLE Items add CONSTRAINT fk_categories FOREIGN KEY (itemCategory) REFERENCES Categories(Category) ON DELETE CASCADE ON UPDATE CASCADE;
---
Im running on phpMyAdmin 2.3.2 and MySQL 3.23.58. Both Tables are MyISAM, i have read abit about INNODB but my host doens't give the option for that database type. The only other options are Heap, BerkleyDB, ISAM and Merge.

Im wondering if anyone can help me to work out how to create the relationship and where i can view it. After using the above, it accepts the code in the SQL window, but when a Category is changed in Categories, the Items table does not update.

Multi Field In Same Table Reference To Same Foreign Key
I am having trouble to come up with correct query, could someone give me a hand?

I have created a table which contains several fields (SampleUser_ID, TestingUser_ID, Report_User_ID) and all of these fields reference to same User Talbe's primary key - user-ID.

It works fine when I retrieve data only with Sample person name (as following)

SELECT Resulttable.ID, Resulttable.data, lk_operators.UserName as 'SampleName' FROM lk_operators RIGHT JOIN ResulttableON lk_operators.User_ID = Resulttable.SampleUser_Id

But I having problem to display all officer's name who work on the different task for the same sample. eg:
Result.ID, SamplePerson_name, Testing_person_name, Report_person_name, Result.data

I have try join on top of join without any luck. Can someone point me to the right direction?

Query To Insert 2 Foreign Key Values In Table
I want to insert 2 foreign key values along with some other value into table ....

Can't Add Foreign Key Contraint While Referencing Table Has Contents
using MySQL administrator to add foreign key constraint as such:

ALTER TABLE `testhibernate`.`city` ADD CONSTRAINT `FK_countryid123123` FOREIGN KEY `FK_countryid123123` (`countryId`)
REFERENCES `countries` (`id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

when the country-table contains data this gives:
"MySQL Error number 1005
Can't create table ... (errno: 150)"

when I delete the data from country-table the command is executed succesfully. Am I correct in thinking that it's not possible to lay contraints on tables while they contain data (in this way)?

Mutli Table INSERT With Foreign Keys
I want to add a new row to one table and then add an other row to a second table where
one column is a foreign key to the first table.

The first tableÂ´s primary key is set to auto increasment and I wounder if there is a
simple way to find out the primary keyÂ´s "auto-set-value" from the first INSERT so I
can use it when I fire the second INSERT? example:

INSERT INTO tableA VALUES('','Mike','Male')
INSERT INTO tableB VALUES('','Boston','?AboveInsertPrimeKey?')

Table Design, Normalization, Foreign Keys
In building a MySQL database what are the best practices to optimize speed and normalize?

This is a food management database. Tables are such as item, manufacturer, brand, vendor, store location, nutrition facts, price, store dept, packaging, item type. And a few others.

Given:
1. Tables are concise and have keys on all relevant search fields.
2. All tables have a prime, integer id field. (First field in table.)
3. Any repeating data field will be normalized. (Except for a few very minor areas.)

Questions:
1. Use where field=field clauses to link the various tables only or mostly?
2. Use joins?
3. Use foreign field requirements to insure links and data completion?
4. Use triggers & procedures to do (3).
5. Have application programmers enforce the business logic? (Not really favored.)
6. Which approach is fastest? Where clauses to join, join clauses? Does it matter?

Any recommended design tools? Am using MySQL Browser & Administrator.

MySQL Table Design Style With Foreign Keys
I just wondering what proper (My)SQL style would be for the following situation (which I made up for the sake of this)...

You have a SpiceRack table and a Spice table. Each rack has many spices, in typical one-to-many fashion. So the Spice table would have rackID column. Easy.

But let's say each rack has one default spice.

So should the SpiceRack table have a column "DefaultSpiceID" or should the Spice table have a column "isDefault"?

In one sense, I would say the former, to help enforce a single default spice. In another sense, I would say having two tables point to each other is ugly and just do some extra queries to enforce that there is only one with the latter.

And while I'm at it... for that isDefault, and similar T/F columns... What's the best data type? SMALLINT (1) UNSIGNED, ENUM(0,1), or LONGBLOB?

Getting All Fields From A Table And ONLY ONE From Another One
maybe it's time to get some rest, today...

SELECT * , User_Avatar
FROM restaurantsrate, users
WHERE Card_ID =222443
AND Ratings_Deleted =0
AND Ratings_UserID = User_ID
LIMIT 0 , 30

I'm trying to get all fields from table restaurantsrate and only User_Avatar from table users, but I'm getting ALL fields from both tables? anyone can help?

How Many Fields Can A Table Have?
I'm presuming you can have as many fields as you'd like, but I'm just curious as to what the maximum recommended is. Is it ever better to split the fields over 2 tables instead of one? even though most the time if you're grabbing data from table1, you'll also be getting data from table2. The question popped into my head as I was making a "Orders" table for an online store. It has 39 fields so far and I'll probably need to add a few more (depending on what other information the client needs to know about their customer orders).

Table Fields
Is it ok to have a big table with 27 fields or should I try and split it down into smaller tables. It kind of makes sense all being in one table to me, but if that is bad design then I would break it down.

Sum 2 Fields From 2 Table
I have facing a problem to sum 2 fields from 2 table.

SELECT Table1.Stud_Number, Sum(table1.School_fee), Sum(table2.libary)
FROM Table1, Table2 WHERE Table1.Stud_Number = Table2.Stud_Number
GROUP BY Stud_Number

Db Table Fields
Is there an industry standard or best practice some of you more experienced developers could pass along concerning the number of characters I should define in my tables for a person's first name, last name, username, phone, email... and any other fields you think worth mentioning?

My Password is encrypted with sha1(), so that field is 40 characters long.

I'm looking to keep the tables as efficient as possible, so I'd like to follow some 'norms' if there are any.

Fields In Table
How do I fix this error? My tables have data.

More Than 50 Fields In A Table
I had made a table which contains more than 50 fields..
I am afraid this will hamper later ..
What i want to know ?
1>how many fields should be in a table for optimization ?
2>what should taken into consideration for future hazards in case there are more than
50 fields in table ?

Merging Two Fields In A Table!
I have used a join to bring two tables together, which works fine, but what I now have is 2 fields with dates in which I would like to order by, and at no point are these two dates overlapping, is there a way I can merge the two together, or order by both so as they overlap. My current SQL is:

SELECT * FROM tblblog
LEFT JOIN tblfilmreview
ON tblblog.blog_date = tblfilmreview.date_watched

UNION

SELECT * FROM tblblog
RIGHT JOIN tblfilmreview
ON tblblog.blog_date = tblfilmreview.date_watched

ORDER BY blog_date DESC, date_watched DESC
Thankyou for any advice you may be able to give
Dan Duke

How Many Fields Of A Table Are Recommended?
May be a stupid question, but just want to know, how many fields/columns are recommended ? I know, it depends how many someone needs, but incase if a person needs alot of fields, then for example, is it okay to make 200 fields of a table of mysql database? or its not good to add so many fields ?

Two Similar Fields One Table
I am trying to figure how to make a single select query in one table between
two fields to see if they are similiar. I need the second one to have a
wildcard. I can easily do this with no wildcard to see if they are the same
but I can't get anything to work when it should be LIKE.

Example:

Table: test
Field1 = "testing 123"
Field2="testing"

I need something that selects that row because the word "testing" is in
both.

Fields In Table Will Not Always Update,
I'm using C#, ASP.NET and MySQL, The language is no problem (not to much) but the rest well I wonder. I have a DB table that has 5 seperate fields. A main one that
is Integer and the rest are VarChar(50). each of the 4 others are identical in setup. I can change some records and others I can't.

ID int(10),
std varchr(50),
atd varchr(50),
dtd varchr(50),
ctd varchr(50)

I update say 'atd' with new data, Date, Time and a code of 15 letters/numbers.This one takes, I change to another record and try to update ctd and it shows like it takes but when I use the Command Line it shows no updates on ctd. this is random on this also.
If I change to another record it may all work or not. Any Ideas at all?

Adding Fields To Table
Once a table has been created, can additional fields be added? What would be the syntax?

Update Fields In Table
I am trying to update multiple records,rows,fields in a table. Below is what I am placing in the sql field in phpmyadmin. It works with one update line but not more. What am I missing here?

UPDATE Products SET TitleTag='U.S. Air Force Retired Shop On-Line for Flags of the world - US, International Flags ' Where Product_ID=28;

UPDATE Products SET TitleTag='Vietnam Veterans of America Shop On-Line for Flags of the world - US, International Flags ' Where Product_ID=29;

Table Fields Do Not Show Up
When creating a connection to my mysql database using dreamweaver mx, or various other programs, i can see the table names once the connection is made, but the fields do not show up with the exception of a couple of tables. I've looked over those tables several times and cannot find any differences that would cause this problem. The only program this does not seem to affect is phpmaker.

SHOW FIELDS FROM Table?
My code is shown below, I can select * from employees but I cannot get
any data back from SHOW FIELDS, I do not receive an error, in fact,
something is returned but I'm not sure what. I can execute the
command:

SHOW FIELDS FROM test.employees

in the Control Center just fine, even cmd.ExecuteReader will enter
reader.Read() once, something is returned but what does it look like?

Counting Fields In A Table
is there a way to get the field count in a table in MySql?

Maximum Fields In A Table
I have a website which runs pretty slow... I have turned on the SLOW-QUERY-LOG, and there's one table that is in the log over and over again...
It's the table which stores 99% of the user information.
This table has 61 fields, and alot of these are varChar and their set at 100 or so....
Anyways, I was curious what is the maximum amount of fields you should have in table, and in my case should I split the data between several tables and just use a complex Join statement?

93 Fields In A Single Table
I'm putting a database together for a client, he has a questionaire that comprises of 93 questions that range between yes or no's to if no why not (text and varchar fields)
I think his questionaire is ridiculous but he is the client at the end of the day.
I have been trying to decide how to put this monstrosity together and it looks as though a table with 90+ fields is going to be the result because there are no field repeats. I could design a generic "yes and no" table then populate it with the "yes no" questions linking it with a relationship but then the coding to ensure order is going to be a lot of extra work...Dwane

How Can I Count Non-empty Fields Of A Table
there's a form with some fields, after submitting, all non-empty inputs are in mysql table's field. how can i count how many are non-empty fields in a row of mysql table?

Prefix Table Name On Result Fields
this has to be a simple one, but it has me stumped.

I am doing a join across 10 tables. Some tables have conflicting field names (i.e. they are the same). When i access the resultset by fieldname, obviously at times I get a conflict. I cannot access the fields by the index position.

my join is in this form:

Select * FROM table1 LEFT JOIN table2 ON table1.id = table2.id2;

Now, how can I get the table names to prefix the returned columns?

I am using the creole framework for anyone who may be interesed, but have access to the RAW SQL.

Simple: Adding New Fields To A Table
i'm VERY VERY new to mysq! i have an existing table with 4 simple fields: id, lastName, firstName, and email.

i'm trying to create a registration page, and need to add a few more fields to the table: StreetAddress, City, State, and ZipCode.. i just cant remember what the sql statement is, or where to find it, and i'm not sure "what" its called - and that makes it hard to google.

i'll add the new feilds using the command line, or more likely just type the code directly into phpMyAdmin. the table name is tblCustomers

hopefully i've explained myself well enough. i know adding new fields to a table has to be about as basic as it can get.. but i cant remember how to do it!

edit:
so i did find the code i used to create the table

create table tblCustomers(
AdminID int unsigned not null auto_increment primary key,
LastName varchar(50),
FirstName varchar(50),
Email varchar(80),
AdminPW varChar(20)
);

insert into tblCustomers values
(NULL,"Smith","Bob","bob@example.com","newpass"),
(NULL,"Jones","Bill","bill@example.com","newpass"),
(NULL,"Doe","John","john@example.com","newpass"),
(NULL,"Rules","Ann","ann@example.com","newpass");

Fields In Users Db Table For Authentication
which fields do you place in your db for user authenication?

for example..:
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`Username` VARCHAR(40) NOT NULL default '',
`Password` VARCHAR(40) NOT NULL default '',
`Lastlogin` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

i wonder for some more useful fields for software logging/management people sometimes (or seldom :-)) use like:
lastip
createdon
lastlogin
active ('yes', 'no', 'banned') -

How Can I Compare Two Table Fields In One Query?
This is my problem:

"SELECT * FROM a,b WHERE a.field LIKE b.field " --> this works

"SELECT * FROM a,b WHERE a.field LIKE %b.field% " --> this works not

My Question:

Is it possible to compare two filds with the substitute symbol % if the fields are stored in different tables?