MYSQL :: JOINing From Multiple Fields To One Table

Joining Multiple Fields To A Single Table?
I have 1 table with 2 columns, 'id' and 'name':

tbl_names:
idname
------
1Bob
2Jeff
3Fred
4Joe
5Bill

I then have another table which contains several fields which hold id's
from the above table:

tbl_output:
idperson1person2person3
-----------------------
1231
2543

I need a query that will return the names for the specified id from
tbl_output.
If I have just one 'person' field in tbl_output I would do it with an
inner join like this:

SELECT name from tbl_names
INNER JOIN tbl_names on tbl_names.id = tbl_output.person
WHERE tbl_output.id = ?

but I can't figure it out when theres multiple fields to be joined from
the same table...e.g I want to specify tbl_output.id = 1, and it give me:

person1person2person3
---------------------
JeffFredBob

Joining Multiple Columns To The Same Table...
I have a table of job postings and they can have up to three certifications associated with them. In the job table they are stored as certification_1, certification_2, certification_3 and feed off the same look-up table. I need to join the jobs table to the look-up table for reporting so I'm trying to write three join statements (one for each column) but I keep getting the following error:

"Not unique table/alias: 'certification'"

The query is below:

SELECT
job_postings.fname,
job_postings.lname,
job_postings.phone,
job_postings.extension,
job_postings.fax,
job_postings.email,
job_postings.title,
location.value as location,
job_postings.posting_date,
job_postings.description,
licensure.value as licensure,
education.value as education,
certification.value as certification_1,
certification.value as certification_2,
certification.value as certification_3,
job_postings.salary,
experience.value as experience,
description.value as job_description
FROM
job_postings
LEFT OUTER JOIN location ON location.id = job_postings.location
LEFT OUTER JOIN licensure ON licensure.id = job_postings.licensure
LEFT OUTER JOIN education ON education.id = job_postings.education
LEFT OUTER JOIN certification ON certification.id = job_postings.certification_1
LEFT OUTER JOIN certification ON certification.id = job_postings.certification_2
LEFT OUTER JOIN certification ON certification.id = job_postings.certification_3
LEFT OUTER JOIN experience ON experience.id = job_postings.experience
LEFT OUTER JOIN description ON description.id = job_postings.job_desc

Joining A Table Multiple Times
I was wondering if it was possible to join 2 tables on 3 different feilds, but I don't want the 3 feilds to limit my results... I'll try and explain:

Table 1 = "offices"
Table 2 = "employees"

The offices table has the addresses of all the offices and 3 extra feilds "manager", "supervisor", and "employee". (these 3 feilds have a numeric value equal to the primary key in the employees table)

The employees table has the employee information like address and phone number and such.

I would like to write a query that would pull the office name as one feild, followed by "manager", "supervisor" and "employee".

Currently I have this, but it only pulls the employee and I'm not sure how to get the manager and supervisor to be a part of the query:

SELECT CONCAT(office.id_store, ' - ', office.storename) as offices, CONCAT(user.lastname, ', ', user.firstname) as fullname
FROM office LEFT JOIN user ON office.empoyee = user.id_user

Selecting Language Depended Fields Through Multiple Table References
For a long time i think i need your instructions dear people on sitepoint. I'm making a sql query for getting user info, but i dont know how to handle this situation:

SELECT
user.name, user.firstname, user.lastname, ___ as user.city, ___ as user.province
FROM
user, languagefield, city, province
WHERE
user.id = %i
AND languagefield.language = %i
AND city.id = user.city
#tricky part
AND city.name = languagefield.id
AND city.province = province.id
AND province.name = languagefield.id
So i'm trying to get users first and last names plus city and province names from language tables. Is it possible with one query? How would you make SELECT and WHERE clauses?

Im using this db tbl scheme:

table : city
id (int4) primary key
name (int6) ref languagefield.id
province (int4) ref province.id

table : province
id (int4) primary key
name (int6) ref languagefield.id

table : user
id (int4) primary key
firstname (varchar32)
lastname (varchar32)
city (int4) ref city.id

table : language
id (int3) primary key
char2name (varchar2)
name (int6) ref languagefield.id

table : languagefield
id (int6)
language (int3) ref language.id
value (varchar255)

id + language unique

*****

I'm also thinking and planning to set more current language depended content to my application, so this is very important part of my design, 'cause it could be reused when dealing with other tables and fields.

Joining Data From 2 Fields Into The First One
I have a table with data as follows

USER FIELDID VALUE
1 1 Bob
1 2 Smith
2 1 John
2 2 Smith
etc.

I want to concatenate the first and last names and update the firstname fields
with the full name throughout the whole table. I know how to loop through the
data (in PHP), concatenate them, and write the result back to the first name
field, but I'm trying to learn SQL.

Isn't there an obvious way to accomplish the same thing with just SQL? Somehow
I just can't see it.

Joining Multiple Tables In One Query
I was wondering if anyone could see the best way to lay this functionality out. I have the following tables and fields (comma seperated part).

Assume the vendors userid = 5. I want to grab a list of all users that are within that vendors territory.

[table] users
userid, zipcode
* multiple users per single zip code

[table] vendors
userid, vendorid

[table] assoc_zipter
zipcode, territory
* multiple zip code per single territory

[table] assoc_venter
vendorid, territory
* multiple territories per single vendorid

Here is the SQL written out.

SELECT * FROM users WHERE
* vendors.vendorid=5
* vendors.vendorid = assoc_venter.vendorid
* assoc_venter.territory = assoc_zipter.territory
* assoc_zipter.zipcode = users.zipcode

I am trying to get this all in one sql statement. Here is a sql statement that does not work:

SELECT * FROM users
JOIN vendors ON (vendors.userid=5)
JOIN assoc_venter ON (vendors.vendorid=assoc_venter.vendor)
JOIN assoc_zipter ON (assoc_venter.territory=assoc_zipter.territory AND users.zipcode=assoc_zipter.zipcode)
GROUP BY users.userid

Left Joining Multiple Tables
I have a tricky mysql problem I just can't get my head around. I have 3 tables:
candidate (CandidateId)
answer (AnswerId, Answer, QuestionId, CandidateId)
question (QuestionId, Question)

I want to retrieve data on all candidates and their answers to the questions "First Name", "Last Name" and "Phone (Mobile)". If the candidate has filled out all 3 answers (and hence there are answers for all 3 questions in the 'answers' table), then the following query works fine:

SELECT DISTINCT candidate.CandidateId, candidate.Email, a1.Answer AS LastName, a2.Answer AS FirstName, a3.Answer AS Mobile
FROM candidate, question q1, question q2, answer a1, answer a2, question q3, answer a3
WHERE q1.Question = 'Last Name'
AND q1.QuestionId = a1.QuestionId
AND q2.Question = 'First Name'
AND q2.QuestionId = a2.QuestionId
AND q3.Question = 'Phone (Mobile)'
AND q3.QuestionId = a3.QuestionId
AND a1.CandidateId = candidate.CandidateId
AND a2.CandidateId = candidate.CandidateId
AND a3.CandidateId = candidate.CandidateId

However, if the user has never submitted an answer for one of the fields, they won't come up in the query. I think I want to do a LEFT JOIN to bring up the candidate even if they haven't filled out the answer but can't figure out the SQL. Can anyone help?

Joining Two Tables With Multiple References
Anyone have any idea how to join two tables where more than one column in the child table is referencing the same key in the parent table?

table #1: members (memberID, firstname, lastname)
table #2: log (issuerID, receiverID)

Both issuerID and receiverID are foreign keys referencing memberID.

I want a query that will list the names of the issuer and the receiver in one query... is this possible?

Joining Multiple Physical Tables
I have 5 distinct tables T1, T2, ... T5 which have exactly the same structure. Each of them describing specific enterprise departments.
Is it possible to join them to create only one logical table (ie. TX) against which I can issue single select statements :

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*

Joining More Than One Table
I have the following query which generates what I needed: Lists students, modules done, continuos assessment done for those modules and the sum of the continuos assessment. I have a one-to-many-to-many relationship between students, modules, exams and continuous assessment.
One student does more than one module and each module has more than one exams and each exam has more than one continuos assessment. How do I sum up and find the average of all exams done by a student. In effect, I want to list student id, module, continuos assessment and average of all modules done by one student

SELECT s.id, f.moduleid, f.exammark, SUM(c.mark) from students s
LEFT JOIN finalexamtab f
ON s.id = f.studentid
INNER JOIN contassessment c
ON s.id = c.studentid and f.moduleid = c.moduleid
WHERE s.major = 'CASE4'
GROUP BY s.id, f.moduleid, f.exammark;

Joining More Than One Table
The following are my tables:

students
| id | name | major | varchar | sex

module
| id | name

lecturer
| id | name

lecturermodule
| lecturerid | moduleid | semester | creditweight | examweight | contassessmentweight | passmark

contassessment
| studentid | moduleid | assessment | mark

finalexam
| studentid | char | moduleid | exammark | given

I have the following query which generates what I needed: Lists students, modules done, continuos assessment done for those modules and the sum of the continuos assessment. I have a one-to-many-to-many relationship between students, modules, exams and continuous assessment.

One student does more than one module and each module has more than one exams and each exam has more than one continuos assessment. How do I sum up and find the average of all exams done by a student. In effect, I want to list student id, module, continuos assessment and average of all modules done by one student :

SELECT s.id, f.moduleid, f.exammark, SUM(c.mark) from students s
LEFT JOIN finalexamtab f
ON s.id = f.studentid
INNER JOIN contassessment c
ON s.id = c.studentid and f.moduleid = c.moduleid
WHERE s.major = 'CASE4'
GROUP BY s.id, f.moduleid, f.exammark;

Joining Same Table
Is it possible to join the same table more than once. I'm trying to query the shipped and allocated sums and this is pulled from the nsidetails table. When I run the below query I get "Not unique table/alias: 'nsidetails'". Is there another way I can sum on the same table and same field without using a seperate query.

Table JOINing
I'm trying to pull the NEW clients from a table that records the average sales monthly, but I'm not getting what I want.
I'm using this query:

Code:

select c1.BUC, sum(c2.Promedio) as dato1, sum(c1.Promedio) as dato2
from Captacion c1 left join Captacion c2 on c1.buc = c2.buc
where c1.corte = '2005-01-31' and c2.corte='2004-12-31' group by c1.BUC
having dato2>0 and (dato1 is null or dato1<=0)

It outputs 2,069 rows, but It should have output about 20,000. What I want is all the clients (BUC's) that averaged something in january but didn't averaged in December.

Joining A Table
Is there a way to join a table to itself? (Technically this might be a very stupid question.)
The data in this table is linked in a tree-like structure.
Every row/id is related to another row/id in a parent-child relationship. I want to test for the status of the parent row (which shares the same id as it's child row's "parent_id" field).

something like:

SELECT g.id, g.parent_id FROM groups g, groups gg WHERE g.parent_id = gg.id and gg.status = 1;

Multiple Fields
i have a query regarding mysql like clause.can we use
select name like 'john%' or 'bill%' from nametable ;
Is the above syntax correct or is it wrong.If wrong please give me the correct syntax.
what i am trying to do is that i am trying to select either john or bill with sum last name in all the rows.

Joining The Same Table Twice In One Query
I have a table I want to join twice in one SELECT statement and I can do it just fine. However, the column I want that gets pulled in has the same name in both instances and I don't know how to name them uniquely. Here is a stripped down version of what I'm trying to do:

Joining / Subquery A Table On Itself - Help?
What I need to do is this: Query to get all rows whose hair='None'. Thats certainly simple enough to do, but I need the additional row's hair color to be added to the end of each resulting row. I'm assuming the PersonID could be used to do a join on itself and marry together the data, but I've been writing all sorts of things in the past 24 hours and getting unpredictable results. Usually with FAR more duplicate rows than desired.

Here's the structure of my table.

Index Key For Joining Table
Say I have three tables:

Members (key: m_id)
Groups (key: g_id)
Membership

Membership only exists to establish a many-to-many relationship between Members and Groups (each member may be part of several groups). The only fields I really need in Membership are foreign keys m_id and g_id. According to books I've read, I should have a primary key in the membership table, but I don't see the point. Even if I had a key, say ms_id, I would never know it, would always have to query for it. The table is almost always used in join queries (only time this is not the case is when establishing or removing membership).

Joining Table Records
Migrated an MS Access database (which has linked/joined table records).

Reviewing the records within MySQL Query Browser, I noticed there's no obvious indication of the table records still being joined/linked; nor did I find a way to recreate them.

I need to know that a link exists before I begin coding PHP. (Which is also new to me.) Don't want to run into a problem with having the correct coding but the wrong answer based upon the lack of links.

Joining Two Records From Same Table
I have a parent-child relationship in a single table, with permissions set on the parent object.

I want to query one child record, but grab the permissions from the parent object....

MySql Table Joining
Im having problems joing the tables in my mysql database. I am using 4 tables to store the data for the entry fields (using drop down menus to input the data) and a final 5th page for collecting the data together for the final output. I am using left joins to join all of the tables together using thier ID numbers. The problem that I have is two of my original input field use data from the same table. This is causing me problems when I try to query data from the 5th (results) table as im echoing the same thing for two fields. Is there a way that I can fix this without having to use 6 tables?

Search With Multiple Fields
I have a form with essentially 5 different selects, let's call them country, state, subject area, tag, and age range. What I want to do is find all of the users who are the closest match to the search query (once I know how to do this, I can modify the query to find their names, email addresses etc...)

In the database, users have a country_id (not name!), state_id in one table, and subject area id, tag id, and age range id in another table (all in the same column as multiple rows for the same user).

I want to do a database search and match all of the users who meet at least 1 of the criteria, and sort them in order of closest match (all 5 criterion) to least match. Assuming I consider the order of preference to be subject area, tag, age range, country, and then state, how do I do this?

To make the problem even slightly more difficult, the form doesn't return the actual country name, it returns the row the country shows up in the country table (same for the other fields) so I'm currently doing a look up for each field to figure out which country/state/etc... I'm actually dealing with.

I don't see how I can do a FULLTEXT search for this information because it is all in different columns.

Update Multiple Fields
I need to update some 900,000 records, each different. I will be pasting the code into the sql window of PHPMyAdmin at my web site. The individual updates look like this:

UPDATE table SET Field1='sample', Field2='sample1' WHERE UniqueID=12345

UPDATE table SET Field1='sample2', Field2='sample3', Field3='yes' WHERE UniqueID=2021432

How can I string together a bunch of these where the SET field values are always different and there is one each for 900,000 different ID's. Right now, I have these in 30 text files but I can't seem to get the syntax correct.

Count Of Multiple Fields
I have a database that stores some stats for a hockey league in the following manner:

Field names are GP, G, A

Whenever a player Plays a game, his ID is added to the column GP while other columns go to 0
If a player scores a goal, his ID goes under G, the guy who assisted goes under A

What I want is a query that can give me the count for each ID that shows up in GP for GP, G and A

Is there an easy way to do this with just one statement? Right now, it's really messy since I have to loop through each player ID in GP and make individual queries to count GP, G and A because I can't figure out an easier way and I don't want to rebuild the DB

Update Multiple Fields With 'OR'
Is there a better way to do this (more efficient).

I have up to 200 items to update per query, the id's are not sequential. Using up 200 (well 199) OR's in a single query doesnt strike me as being the best way to do this. All the fields get set to the same value.

# Example
UPDATE table SET value=value WHERE id=11 or id=23 or id=31 or id=74 or id=56 or id=96;
# Up to 200 fields

Or am I stuck with this query? (which works).

Multiple Distinct Fields
Okay SQL gurus, I have a table laid out as so:
RowID | UserID | UserType | pID

Now, how can I write a SQL query that would select every distinct UserID and pID combination.

So if the data in the table were:

1 jjdoe internal 19
2 jjdoe internal 6
3 jjdoe internal 6

It would only return rows 1 and 2.

Ordering Multiple Fields
I'm relatively new to mySQL so I've been looking around the internet for help on how to order based on a user-defined pattern and haven't seen exactly what I'm looking for. If someone could help with this example I created to illustrate what I'm trying to do in general, I'd greatly appreciate it.Let's say I have a table in my database of Army officers with the following fields: rank, lastName, firstName.

I'm looking for the right query so I can display in an HTML table every officer in the mySQL table and order the rows first by rank, then by lastName (if ranks are the same), then by firstName (if ranks and last names are the same).

Displaying Multiple Fields
I am sure amongst this community that this question is rather straight forward.
I have a database where a table is added that contains the user login, password and other user information.
What I wish to do now is for each user I want to be able to add a product description, old price and new price.
So when this particular user logs in, they will see a list of products on one column and then the old price in another column and new price in the third column.
The prices obviously have to be matched to the same product.
Eg:
productA new_price1 old_price2
productB new_priceX old_priceY

How would one do this?

Comparing Multiple ID Fields
I have an advert table which contains three category ID fields (catid, catid2, catid3). These catid fields match up with the catid field in the category table. I want to run a query that returns any row that contains any mention of a specific category.

SELECT * FROM advert, category
WHERE advert.catid = category.catid
AND advert.catid = '3'

The above query works fine for the first advert.catid field, but once I introduce...:

OR advert.catid2 = '3'

... the results start getting strange. The same row is returned 9 times.

GROUP BY Multiple Fields
Can somebody show me how to properly use GROUP BY if I'm using multiple fields.
For instance:

I have a table that has a month field and a year field. I want to group all entries by month and year. How do I prevent grouping a record from January 2007 with a record from January 2008?

Indexes With Multiple Fields
I have seen some tables that have indexes which contain multiple table fields. What is the purpose of having an index with multiple fields instead of a seperate index for each?

Duplicates On Multiple Fields
The table has fields:
home_id, owner, street_number, direction_one, street, street_type, city

I have a problem where the same address information was entered in but with different owners. How do I find all the duplicates? I tried this query:
SELECT home_id, street_number, street, city
FROM home_data
GROUP BY street
HAVING ( COUNT(street) > 1 )

but that only works for duplicates on street, at the very least it should check for dups on street_number and street and city. When I ran the above query it took forever. My table has over a million records in it. Basically my computer froze up but kept running and I kept getting virtual memory errors.

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)?

Joining Tables On Unknown Table Name
I have a framework that uses dynamically created tables, named using an
incremental "attribute set ID", as follows:

attrdata_1
attrdata_2
attrdata_3
etc, etc...

I also have another table that stores data for each object within the
framework, called "object". The fields in this table are fixed so are always
known, one of which is "attrset_id" which relates to one of the
"attrdata_***" tables.

The fields in the "attrdata_***" tables are all unknown except for a fixed
"object_id" field that links objects with an entry in those tables.

The idea is that the "attrdata_***" tables can be used to extend the data
for each object so, for example:

We need to extend Object 100 to store new properties "name", "address" and
"tel", so a new "attrdata_4" table is created containing fields "object_id",
"name", "address" and "tel" and "object_id" is set to "100" (the object's
ID) and the "attrset_id" field in the "object" table is set to "4" (the
newly created attribute set's ID).

Now, when querying the DB I want to link the "object" table with the
relevant "attrdata_***" table to get the complete extended data set, so
ideally this would be....

SELECT O.*, A.* FROM object AS O
LEFT JOIN CONCAT("attrdata_", O.attrset_id) AS A ON O.id=A.object_id
WHERE O.id=100

... but that doesn't work. What I need to know is if anything along those
lines exists? I've looked, but not found so I'm now making sure :) Although
it could be done using a couple of statements, I'd prefer, if possible, a
single statement solution.

Averaging And Counting When Joining A Table To Itself.
I have a table which allows users to rate a product (details at the bottom). Each user can make two types of ratings on a product - 'Looks' and 'Functions'.
A user can give a rating for none, one or both of the rating types.

What I would like to do is create a query that will tell me the average rating for Looks and Functions as well as the number of users that the average was taken from.

At the moment I run two queries:

SELECT AVG(rating), count(*) FROM ratings WHERE ratingtype='Looks' AND productid=n
SELECT AVG(rating), count(*) FROM ratings WHERE ratingtype='Functions' AND productid=n

Ideally I would like to do this in one query, to reduce the number of queries that are being run. (The page that shows all of the products currently runs hundreds of queries - one to select the details of each product and then for each product the above two statements are run).

Is this possible? I have tried a number of different queries, none of which work. The closest I got is (note: typed out, so may contain typos): Code:

Joining Table With Null FK Rows
I've got this query:

DROP VIEW `pmd_manf2`.`vw_ProductFirmware`;
CREATE VIEW `pmd_manf2`.`vw_ProductFirmware` AS
SELECT p.Name, fw.FirmwareID, fw.DateCreated, fw.FileSize, fw.Prod_ProductID, CONCAT(pf.FirmwareVersion, '.', p.FirmwareVersion) AS Version FROM Firmware as fw
JOIN Products AS p ON p.ProductID = fw.Prod_ProductID
JOIN ProductFamilies AS pf ON pf.ProductFamilyID = p.ProdFamily_ProductFamilyID
JOIN Customers AS c ON c.CustomerID = p.Cust_CustomerID;

p.Cust_CustomerID can be null (not all products are associated with a customer)
When I exclude:
JOIN Customers AS c ON c.CustomerID = p.Cust_CustomerID;

the query runs fine, when I add
JOIN Customers AS c ON c.CustomerID = p.Cust_CustomerID;

I don't get any results. I've read up on JOIN and tried the different JOIN flavors but I can't get around this problem. Do I have an error in my query or is it not possible to join a table that won't have a match for every row in the parent?

Create A New Table By Joining Two Tables Together
TableA and TableB have one field in common: studynumber.

This works:

SELECT * FROM main3 JOIN studies_001 ON main3.studynumber = studies001.studynumber;

Now I want to create a new table based on that JOIN.

I can't seem to do it - I changed the fieldname in one table to snumber to avoid duplicate fields but this does not work:

CREATE TABLE studies_try1 SELECT (main3.studynumber, main3.studydate, main3.studytitle, main3.studyintro, main3.studytext, main3.booknumber, main3.messagetype, main3.media2_filename, main3.teacher_id, main3.media1_filename,studies_001.id, studies_001.snumber, studies_001.chapter_begin, studies_001.verse_begin, studies_001.chapter_end, studies_001.verse_end) JOIN studies_001 ON main3.studynumber = studies_001.snumber;

Joining Parent With Child Table
I seem to always want to do this type of join, but I can never find
how to do this.I am trying to join two tables. The parent table will always have one
row of data and the child may have 0 to 45 rows of data. I am trying
to join the tables in an outer join, so if 0 rows in child it is not
affected. But I only want one row returned of the child.
How do I do this in mySQL?
I have tried LIMIT, but this is not quite what I wanted.

How To Update All Fields With A Multiple Of The Field Value?
Is there a MySQL command to replace all the values of a field with the field's original value that's had a mathematical formula applied to it? Basically, I want to divide the field's original value in half, then add 40% more. The formula would be y=x/2+.2x where y is the new value and x is the original value.

I know I could write a PHP script that would select each field's value, apply the formula, then update the new value, but it'd be cool if there's a MySQL built-in function to do this.

SELECT DISTINCT With Multiple Fields
I'm kind of stumped on a query I'm trying to build. basically I have a forum table like this:

Post_ID | Thread_ID | Post_EntryDate | etc.....
90 | 22 | ....
89 | 21 | ....
...
...
...
85 | 1 | ....
84 | 1 | ....
83 | 10 |....

and so on. I would like to get the last 20 threads with most recent posts filtering for Thread_ID duplicates of course. so far I have this:

SQLtemp = "SELECT DISTINCT Thread_ID FROM Posts ORDER BY Post_ID DESC LIMIT 0,20"

this succesfully returs a list of unique Thread_IDs of the last 20 threads with most recent posts. However, I need to get the Post_IDs and other fields but when I throw Post_ID in the SELECT statement all I get is duplicate values for Thread_ID

How Change Multiple Mysql Fields?
I have a table with 2,733 records.

I need to change a field (published) on all records to show ....

Multiple Search Fields And Subqueries?
I'm trying to construct a query in PHP that will be able to handle multiple search fields. The data is in multiple tables.

COMPANY
company_id
name
address

SERVICE TYPE
company_id
service

OFFICE LOCATIONS
company_id
city_name

My question is how do I construct a query that will allow there to be these three search fields (company name, service type, location).

I know that subqueries are probably needed, but I'm a newbie and still not quite clear how to get these to work properly.

The queries I've been able to construct return zero results unless all fields are filled in, or they return multiple listings of the company name since the company may have more than one office location. I don't want the user to be required to fill in all fields to get meaningful results.

Count Fields Over Multiple Rows
I have a database like this

id, field1,field2,field3,field4,field5

Database contains 100 rows, some rows have no fields filled, some
1field , some 2 fields etc.

How would i count the number of fields filled in total?

So the outcome is (number of fields filled in row1)+(number of fields
filled in row2)+(number of fields filled in
row3)....................+(number of fields filled in row100)

Sort By Multiple Fields In Different Directions?
I'm wondering if I can sort by multiple categories in different directions. I tried this and it doesn't work:

SELECT * FROM table ORDER BY field1 DESC ORDER BY field2 ASC LIMIT 0,10

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?

Select Fields Accros Multiple Dbs
I am trying to use the SELECT DISTINCT statement to create one list of email address I have which are in two different databases, I have tried the below syntax, but does not seem to work...can anyone please tell me where I am going wrong!

SELECT DISTINCT m2e_db.email, events_bookings.email FROM m2e_db, events_bookings ----Ment2Excel (M2E)----
--LEARN BY EXAMPLE--