MYSQL :: JOINing Three Tables.

Joining 5 Tables
firstly I am using MySQL 4.0.25.

I have a database storing availability of rooms for an accommodation business.

The following tables are relevant to my question:

room:
- room_id
- room_name
- accom_type
- thumb_id
- room_comments

accom_type:
- type_id
- accom_type (the name)
- desc_short
- features
- max_slept
- num_queen
- num_single
- num_bunks
- main_thumb_url

accom_type_pics:
- image_id
- small_url
- large_url
- caption

calendar_date:
- date_id
- calendar_date
- season_id

room_availability:
- date_id
- room_id
- availability_status
- booking_id

There are 4 accom_type - each with more than 1 room - all in all there are 9 rooms.

Each accom_type has a main_image which has its url stored in the accom_type_pics tbl.

The room_availability is used to store unique pairs of room_id and date_id - and the status of the room for that date (and booking_id if it is booked).

I am attempting to select all of the accom_types that have availability for a certain range and number of guests.

I am just not sure which table I "start" with - I have a fair idea of how to write the query, I am just not sure which order the joins need to be in.

SELECT at.accom_type, atp.small_url
FROM accom_type at
LEFT OUTER JOIN accom_type_pics atp ON
atp.image_id = at.main_thumb_url
LEFT OUTER JOIN

Joining Tables!
i'm making a simple online shop with products that can have any number of attributes..e.g a shirt mite be able to be color: red, blue, yellow, size: medium, large...then a pair of shoes mite be color:white, cream, size, 9, 10, 11 etc.

So far I have the following tables...

products:
id: name : price: description

product_attributes:
id: productid: attributeid

product_attribute_names:
id: name

product_attribute_values:
id: attributenameid: value

so, the product_attributes tables stores what attributes each product has (each product can have any number of attributes), in this table, attributeid points to the id in the product_attribute_names table.
product_attribute_names simply names and identifies each attribute (eg. size of shoe A, colour of t-shirt B).
then product_attribute_values lists all the possible values for each attribute.

Now, i think this is a pretty good, normalised way to store products and attributes, but I'm having difficulty joining the tables in a sensible manner.

The product table is good because when listing products you can just list product names, price and description. But when you click on the product I have a more detailed product page where you can select what attributes you want before you buy. But I'm slightly baffled as to the best way to use JOIN to extract all the attributes. Ideally for each product I'd want to return a table like this:

for product #3762:
attributename : attribute value
color : red
color : blue
size : large
size : small

so i cud access the returned array as $product['color'][0] or $product['color'][1] or $product['size][0] (using php). How could I acheive this using JOINS?

Joining 3 Tables
I am using MySQL 4.0.25.

I have the following tables:

Gallery
gallery_id
gallery_name
gallery_desc
feature_image
archived

Album
album_id
album_name
gallery_id
feature_image
archived

Image
image_id
album_id
sml_url
image_title
image_orientation
...

I am using the sql on a page that displays all of the albums that belong to a selected gallery. I need to select the count of number of images in that album, the album_id, album_name, the url of the feature image, the title of the feature image, and the orientation of that image (wide or high).

I am able to achieve this no probs with the following sql:

SELECT count(i.image_id) as 'image_count', a.album_id,
a.album_name, i.small_url, i.image_title, i.image_orientation
FROM album a
LEFT OUTER JOIN image i ON a.album_id = i.album_id
WHERE a.gallery_id =
1 AND a.feature_image = i.image_id AND a.archived = Ɔ' GROUP by
a.album_id, a.album_name ORDER by a.album_name ASC

I also need to get the name of the gallery that these belong to. I have tried the following sql:

SELECT g.gallery_name, count(i.image_id) as 'image_count', a.album_id,
a.album_name, i.small_url, i.image_title, i.image_orientation FROM album a
LEFT OUTER JOIN image i ON a.album_id = i.album_id AND a.gallery_id = 1
AND a.feature_image = i.image_id AND a.archived = Ɔ'
LEFT OUTER JOIN
gallery g ON a.gallery_id = g.gallery_id GROUP by a.album_id, a.album_name ORDER by a.album_name ASC

which works fine for gallery_id = 1, but when i display any other gallery, it displays the albums that belong to gallery 1..

I think I am not far off here! - just a minor tweak with my 2nd Left join?

Joining Tables
I have two tables (table1, table2) that share common data.

Table1 has 50 distinct entries while table2 has 10 distinct entries. I want to select all the data from table1 that IS NOT in table2. So, if the data is in table2, don't show it.

So I should get 40 results.

Joining Three Tables
I have three tables; USERS, COMMENTS, and IMAGES. USERS is a complete list of all my users with a unique id called USERID which is the primary key. COMMENTS is a list of comments with the USERID of the user who posted them. IMAGES is a list of images with a corresponding USERID for every user who has posted an image. My problem is that I want to join all three tables so that I may display the results of COMMENTS with all the details from USERS and IMAGES. I am joining on USERID, but the problem is that there is not necessarily an image for every user so when I join the tables all the comments for which the users do not have an image are not displayed. Is it possible to join the tables such that MySQL puts in NULL values when it can't find an image in IMAGES?

The command I am currently using is:
SELECT * FROM COMMENTS LEFT JOIN ON (USERS.USERID=COMMENTS.USERID AND USERS.USERID=IMAGES.USERID)

Joining Tables
I'm trying to get the results of a query to be <= users input where two tables are required for getting the results. When I test the query I get every row in my database even if > users input.

The query I'm using is:
$get_prods = "select * from products,prod_items WHERE products.prod_name = prod_items.prod_name and prod_items.item_points <= '$num_points' order by products.city, products.prod_name";

Can anyone see anything obviously wrong with this? is there perhaps a better way to do this? I'm somewhat (okay totally) confused about whether to use inner, outer, left, right joins and so I used this generic join.

Joining Tables
I have a userdatabase and a confenquiry table. Userdatabase has a Userid which is imputed into the confenquiry along with other information when they enter a new enquiry. What i want to do is for each user to be able to display all their enquiries.

I have a session in place so when i enter <? echo $userid; ?> it will come up with their userid. Therefore i thought i could write...

SELECT enquirynum, startdate, enddate
FROM confenquiry, userdatabase
WHERE <? echo $userid;>? = useridconf

However this does not work, can anyone suggest a different way for this to work?

Joining Tables
i'm experimenting a simple project on a student attendance system. Here's a brief discription of the tables:

Table 1: (Contains the student id and name of all 400 students)
name: student_id
fields: student_number (primary key)
student_name

Table 2: (Contains the student number of those who attended the morning assembly (which not everyone attended! :p) )
name: morning_assembly_22febuary
fields: student_number (foreign key)

My question is: How can I join the to tables to search for the student number & name of those who didn't attend the morning assembly?

Joining Two Tables
I have 2 tables. one with 5 columns and the other with 6

The tables have an accountid column in commom

I would like to join the tables such that the accountids are not duplicated and the new table now has 10 columns.

Joining 2 Tables
I am having a problem with a join statement. I have 2 tables.

table one
- id
- name
- body

table two
- id
- body
- pdate

The relationship is one to many (table one -> table two)

I want to select all the records from table one, and only get the latest row for each record from table two which is related to table one. (pdate is a date field)

Just now if I have 2 rows in table one and 3 rows in table two, 2 of which point towards a single recond in table one, i get 3 rows returned whereas I just want two.

Joining Tables In Sql
I have built an sql script that connects to one tables. however I want to connect now to 2, this is what I had:

$query = "SELECT * FROM artists WHERE ID = ".stripslashes($ID);

how do I connect to 2 tables, I know about foreign fields, its just the sql Im not sure about, if someone could post some example ive been googling it for ages, with no success.

Joining Two Tables
I've got two tables.

Table 1 (ID, DATE, TOY)
Table 2 (ID, DATE, TOY, DESCRIPTION)

I'd like to join them so the results are ID, DATE, TOY, DESCRIPTION meaning I want table 1 to look like table 2.

All my attempts have resulted in millions of rows being returned, not the 77k there are. I'm assuming this a left join job? I've read around the forumn and only encountered answers that go up above what I'm trying.

Joining 2 Tables
I have two tables with that data...truck_id and id in TRUCKS_NAME are reffering to the same thing... how do I create a select query to join both?

TRUCKS_ENTRY
id = 89
truck_id =3
create_by =61713
description = Test
name = John

TRUCKS_NAME
id = 3
truck_name = Mercury
truck_vin = LIIVLKSJOIKES
truck_plate = 2H1037

Joining Tables
I have two tables:

table A
id cause1
1 10
3 11

table B
id cause2
2 12
4 13

I want table C
id cause1 cause2
1 10 null
2 null 12
3 11 null
4 null 13

How can I do this?

Joining Tables....
I'm having some trouble joining tables. My two tables are: aws_event (with hundreds of thousands of events) and awu_user (with lots of users). I need to retrieve all data from aws_event where the id field in aws_event is equal to all user IDs with a certain teacher ID in the awu_user table.

So: in the example below, in need to fetch all data from aws_event for users who are students of teacher 1.

aws_event
------------
user | event | more data
----------------
1 | blah | more
1 | blah | more
2 | blah | more
3 | blah | mpre

aws_user
----------
userid | teacher
1 | 1
2 | 1
3 | 2

Normally, I'd write this in PHP, but I have no PHP access to the server... only SQL query access.

Joining Tables
Table 1            Table2
--------           --------
Ability Bonus      Ability Bonus, 1, 2, 3
AC Bonus           AC Bonus, 4,5,6

What I want the result to be
-----------------------------
Ability Bonus, 1
Ability Bonus, 2
Ability Bonus, 3
AC Bonus,4
AC Bonus,5
AC Bonus,6

I find figure out the right syntax to use, ive tried different combinations of JOIN and UNION but I can't seem to get a solution.

Joining Two Tables
if this is possible:

SHOW COLUMNS FROM table1
ie.

table1_a
table1_b
table1_c
table1_d

and then join another table to this result so it would end up looking like this

table1_a | table2_rowa
table1_b | table2_rowb
table1_c | table2_rowc
table1_d | table2_rowd .

Joining Tables To Themselves
im in the design stage of a very large project at the moment (as you may know from some of my other questions).

im pretty pleased with what i have at this stage but there is one subject that id like to seek expert opinion on before going any further.

in an instance where you had a table of data with millions of records with data such as

id date grade
1 2006-01-01 2
2 2006-01-01 1
1 2006-05-01 3

and you had an extremely common query being (in english)

show the grades of id's who graded 2 on their most recent grading date

what is better practice

creating a second table that has an extra column

id date grade prevgrade
1 2006-01-01 2 null
2 2006-01-01 1 null
1 2006-05-01 3 2

so you can simply say

select id from gradingresults where prevgrade=2

or joining table to itself

i am planning the extra column version but am being advised by a colleague that this is against table normalization procedure.

i imagine that running a query the first way would be considerably faster than the second. any opinions?

Joining Tables
The below statement selects all from orders table and joins the customers table where id=id. The problem is some rows in the orders table will not have a match. Now when I go print the results the $line[id] becomes "NULL" because there was no match. How can I keep the id field from the orders table even if there was no join for that row. Hope that is not to confusing.

Joining 2 Tables
I have two tables with the following fields:

table 1 ("f"):
forum_id (and other fields)

table 2 ("ft"):
forum_id, user_id and some other fields

I want to join the tables on (f.forum_id = ft.forum_id and ft.user_id = 2) but the trick is that ft has an entry for forum_id = 0 that f does not have and I want the join to somehow include a result for that as well. Is there a solution other than to insert another row in table f that has forum_id of 0?

Joining Tables
im developing a b2b portal like alibaba.com or pzplaza.net. Product can be posted in different categories. so the relation b/w products & categories is m-to-m. i developed extra table like 'product_has_category' with both category & product primary keys. Also when there are two level of categories.. main & sub. products are posted into subcategories only. now the problem is user selects MAIN CATEGORY while searching for a product, query always comes up with many records of same product due to m-2-m relation.
though i could not make it clear to u, i suppose, can you please help me ???
one solution is in my mind is: i should allow user to select subcategories bcuz relation is between product & subcategories.( only main categories given in search)

Joining Tables
I have three tables in the following format:

table1.primary_key
table1.data

table2.primary_key
table2.table1_keys

table3.primary_key
table3.table2_keys
table3.data

I want a query that will display
table1.data table3.data

When I try joining the tables I lose rows

Joining Tables
I'm not getting an SQL query to work as designed, and I would love if someone could point me where I'm going wrong.
I have two tables, one is rankings, the other is data. I rank people in the rankings table and all their data is stored in the data table (like height, weight, etc).
Right now, I have two columns in rankings, the id auto increment field and the id for the person's id.

SELECT d.name,d.id FROM data AS d,rankings AS r where r.user_id = d.user_id;

Basically, I want it to select all the users that are in the ranking table (in the order they're ranked, which is specified by the auto_increment value) and return their name. For some reason, this query executes with an empty set.

Joining 3 Tables! Mysql Help...
Here is my current setup:

featuredvideos: videoid
videos: videoid, userid, videotitle, videodescription
members: userid, username, email

So I have a page where I want to list all the featured videos, and I want to grab all the video details as well as details about the user. So I need to somehow join all 3 of these tables.

Currently I joined 2 tables:

PHP

$sql = "SELECT * FROM featuredvideos JOIN videos ON featuredvideos.videoid=videos.videoid";

but I also need information about the user, how would I go about doing this? Also, would it be advantageous to also store the userid in the featuredvideos table as well?

Joining On Unionized Tables
I'm working on a CMS with several database tables featuring animal names and information. My pages begin with two multi-table queries using the UNION command, similar to this:

PHP

SELECT * FROM
(SELECT GZA.Name FROM gz_animals GZA
  WHERE Name LIKE 'Canis_lupus'
  UNION ALL
  SELECT GZM.Name FROM gz_mammals GZM
  WHERE Name LIKE 'Canis_lupus'
  UNION ALL
  SELECT GZB.Name FROM gz_birds GZB
  WHERE Name LIKE 'Canis_lupus'
  UNION ALL
   )
  as Animalia

SELECT * FROM
(
  SELECT GZA.Name, GZA.MyCla FROM gz_animals GZA
  UNION ALL
  SELECT GZM.Name, GZM.MyCla FROM gz_mammals GZM
  UNION ALL
  SELECT GZB.Name, GZB.MyCla FROM gz_birds GZB
)
  as TClass
  WHERE Name LIKE 'Canis_lupus'

I actually use variables instead of "Canis lupus" so it can display whatever database value matches my URL. Anyway, the above tables focus on scientific names. I'm now ready to add common names, but I'm not sure how to do it.

First, not every animal species has a common name, so I created a separate series of tables for common names. Actually, I've only created the birds' common names table so far. I'm trying to figure out how to join it into my query, but nothing works.

I assume that if I add a join to one table, I have to add the same type of join to every table in the series, right? But if the only nams table is bird_names, then shouldn't I be able to join every other table (gz_mammals, gz_birds, etc.) to bird_names?

If that should work, then I'm probably messing up on this section:

PHP

WHERE Name LIKE 'Canis_lupus'

I've tried variations like these...

PHP

WHERE Name LIKE 'Canis_lupus' OR GZBN.NameCommon LIKE 'mandarin_duck'
WHERE Name LIKE 'Canis_lupus' OR WHERE GZBN.NameCommon LIKE 'mandarin_duck'

So is there a way to do this join if I have just one common names table? Also, note that the first series of tables form a super table named "Animalia," while the second series form a table named "TClass."

Instead of joining bird_names to every individual table, is it possible to simply join it to Animalia, then to TClass? How could I do that?

*Question #2*

Is it possible to create a series of tables joined by UNION to form the super table Animalia, then create a second series of tables joined by UNION to form the super table CommonNames, then join CommonNames to Animalia?

I envision a scheme where I could join the tables even if the only common names table was birds_names. When I finished mammals_names, I'd simply add it to super table CommonNames.

Query Joining 26 Tables
Is there any way a MySQL Query joining 26 tables wont become absurdly long?

Problem JOINing Two Tables
I have two tables, for simplicity lets call them Table AAA and Table BBB, I have a query as follows
SELECT count(DISTINCT AAA.ID ) AS post_count, count( DISTINCT BBB.ID ) AS vote_count
FROM AAA JOIN BBB ON BBB.user_id = 'John'
WHERE AAA.user_id = 'john'

Now this query works fine if both the tables contain records with user_id as 'John'. The problem arises when there's no 'John' in any of these two tables.
There could be situation in my program where john has a entry in only table AAA or only BBB. In case he has no entries in the any table that corresponding DISTINCT COUNT should be returned as zero.

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

ARTICLES and ARTICLE_IMAGES

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

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

Trouble With Joining Tables
I'm having some trouble running the following query:

SELECT DISTINCT(im.id) FROM item_master im LEFT JOIN xref xr ON im.id = xr.item_master_id WHERE im.item_number LIKE '%100%' OR xr.oem LIKE '%100%'

For some reason, the query takes 2 minutes to execute. If I remove the bit that says "im.item_number LIKE '%100%'", or change it to compare data from the joined table (eg. "xr.item_number LIKE '%100%'"), the query is executed in 31ms.

Joining/Merging Two Tables Into One?
INSERT INTO test select testing.products_id,testing.products_percase,testing2.products_name,
testing.products_weight,
testing.products_model,
testing.products_price,
testing.products_quantity,
testing2.products_description,
where testing.products_id = testing2.products_id;

This is what I have so far. Basically, I am trying to create one table with the values listed here from the two tables "testing" and "testing2". It says it is stopping near the where clause; does anyone know how to fix this issue so I can merge the two tables?

Joining Two Tables To Get Value Of Unique ID
trying to select a value (PK) from one table and join to another table, getting the value of the item with that ID. I seem only to be gettinbg the unique ID value and not the other value in the record.

("SELECT Business_Type_ID From tbl_BusinessDetails table inner join tbl_businessType
on Business_Type_ID = Type_ID");

here's the table structure

tbl_BusinessType
| Type_ID | Business_Type |

tbl_BusinessDetails
| Business_ID | Business_Name| Business_Type_ID |

I am trying to get the value of Business_Type (from tbl_BusinessType) using the PK (Business_Type_ID) in the tbl_BusinessDetails.

I also do not want any matches.

Joining Tables That Are In Different Databases
How do I join two table that are in two different databases? In MSSQL or Oracle, I would have done something like "SELECT * FROM db1..table1 t1, db2..table2 t2 WHERE t1.id = t2.id". Unless my memory failed me anyway. Is there an equivilant syntax for MySQL?

Joining 4 Tables - Performance
The Tables I have are...

Types        Styles        Contents
-----        ------        --------
ID        ID        ID
Name        Name        Name

Items        Types_Are    Styles_Are    Contents_Are
--------    ---------    ----------    ------------
ID        Item_ID        Item_ID        Item_ID
Name        Type_ID        Style_ID    Content_ID
Status
Created
...
...

Items can obviously have more than 1 Type, Style, and Content, or even non of them. Users will select Type(s), Style(s), and Content(s), sometimes they may not select any types, or styles, or contents. I want a list of Item IDs to be returned that match these conditions. Currently I run the select query as....

select distinct t1.id from items t1, types_are t2, styles_are t3, contents_are t4
where  t1.status = 2
and    t2.item_id = t1.id and (t2.type_id = 3)
and    t3.item_id = t1.id and (t3.style_id = 3)
and    t4.item_id = t1.id and (t4.content_id = 2 and t4.content_id = 4 and t4.content_id = 5)
order by rand().

But sometimes this takes around 5 minutes to run, and with only around 50 Items on the database. Obviously this is a mega basic join, so how could performance be improved, what sort of join whould i be using?

MySQL Joining Tables
I have 2 tables, one that has

$query="CREATE TABLE products (id int(6) NOT NULL auto_increment,fname varchar(15) NOT NULL,lname varchar(15) NOT NULL,stage2 varchar(20) NOT NULL,itemnumber varchar(20) NOT NULL,quanity varchar(20) NOT NULL,email varchar(30) NOT NULL,location varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";

In the 2nd table I want to have 3 items, itemnumber, quanity, and descrition.
I want itemnumber from the 1st table to refer to the 2nd table and update the quanity. For example, If I order 3 of item #12345 then i want it to update the #12345 to 3.

And then say in a week someone else orders 4 of #12345 then i want it to change to 7. How would I go about linking these tables together,

Joining Two Tables Or Difference Between
I have 2 tables called 'usersTBL' and 'propertiesTBL'
user info stored in 'usersTBL' and their properties stored in 'propertiesTBL'

I want to select the users from 'userTBL' which does not have any property in 'propertiesTBL' table, Both table has a same column called 'username'

the Query I wrote was

SELECT *
FROM usersTBL, propertiesTBL
WHERE usersTBL.username NOT
IN (
DISTINCT propertiesTBL.username
)

Joining NON-Related Tables
Is it possible to join non-related tables? If I have one table called "community", how would I join it with the table "membership"? They don't have any related fields. I would like to display the data from the "membership" table in the "community" table:

community
==============
id
members
group

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

membership
==============
memberspergroup
feepergroup

Joining Tables With Different Columns
I have 2 tables:
A:[name,password,email,phone,height,weight]
B:[name,password,email,age]

I wish to do a search in both the tables, and if a match is found in A,
it will return values from A(name,password,email,phone,height,weight)
and vice versa.

I tried UNION but it says the columns don't match.
I tried LEFT JOIN and it just merge everything together.

Joining Larger Tables
In a multi-table join, would it be wiser to join the table that has more records first or the table that has the least records first? Is there any performance gains with a certain order of doing this?

MySQL, Joining 2 Tables.. How In This Instance?
Well, the way I have my tables set up there is specific info I need from one of them in order to do some processing on another. While the design might be flawed, it intriguied me to find out how to do this.. which is why im here.

I have a table called "teams" which contains the following information...
teamid, teamname, teamcaptain, etc...

I have a table called "league" which contains the following information...
playerid, playername, team, etc...

Now teams.teamid and league.team both contain the id of the team.

I need to query the MySQL database based on teams.teamname and get all of the players (who are in the league table) who belong to that team. My problem is, in the league table I only have teamid. So I somehow need to join these 2 tables in order to get this info?

I have tried: SELECT * FROM teams join league on teams.teamid = league.team where teamname=$teamname

I get the teamname info back, however all of the other fields seem to be blank?

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 Tables Which Aren't Directly Related
I'm struggling to get my head round a SQL query and wonder if you might be able to help (I'm using MySQL version 4.1.20-1).

I have three tables: accounts, items and invoices.

CREATE TABLE accounts (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY (id)
) ENGINE = InnoDB;

CREATE TABLE items (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
accounts_id INT UNSIGNED,
...
PRIMARY KEY (id),
FOREIGN KEY (accounts_id) REFERENCES accounts (id) ON DELETE SET NULL
) ENGINE = InnoDB;

CREATE TABLE invoices (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
items_id INT UNSIGNED,
...
PRIMARY KEY (id),
FOREIGN KEY (items_id) REFERENCES items (id) ON DELETE CASCADE
) ENGINE = InnoDB;
Each account can have multiple items and each item can have multiple invoices. Hope you're with me so far?

So here's the question. If I have an account ID, how do I get a list of invoice IDs for that account when accounts and invoices aren't directly related?

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.

Joining 3 Tables And Quoting Question
Joining 3 tables

I have 3 tables: requests, users and categories. What I want to do is to select some columns from the requests table and replace the id's on some columns with the data in the other 2 tables, like this:

SELECT r.id, u.username user, r.name, r.added, r.votes, r.filled, r.filledby, r.torrent, c.name catname, c.image catimg FROM `requests` r, `categories` c, `users` u WHERE r.cat = c.id AND r.user = u.id ORDER BY r.added DESC

My problem is that I have no idea how to add another column wich will list the actual username based on the id in the r.filledby column.

The second thing is quoting all the table and column names since some of them (i think) are reserved names. A good example is this table:

CREATE TABLE `faq` (
`id` int(10) NOT NULL auto_increment,
`type` set('categ','item') NOT NULL default 'item',
`question` text NOT NULL default '',
`answer` text NOT NULL default '',
`flag` set('0','1','2','3') NOT NULL default '1',
`categ` int(10) NOT NULL default '0',
`order` int(10) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

and to make it work i use queries like this:

SELECT `id`, `question`, `flag`, `categ`, `order` FROM `faq` WHERE `type`='item' ORDER BY `order` ASC

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

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

Joining Two Tables For A Search Engine
let's say I have 3 tables. one has titles, one has words, and an index
where each title is broken into words (title_id and word_id)

I want to be able to search terms in any order, so "potter harry" is
the same as "harry potter".

select t.name from titles t, words w, index i where (w.name in
('harry", "potter"))
and (w.word_id = i.id) and (i.title_id = t.id);

This produces a list of titles matching my search times. however, with
over 3 million products, it can be quite slow. 3 seconds is too slow.
sometimes it takes a minute.

is there a better way to do a join when there are more search terms
like "harry potter and the chamber of secrets"?

the fastest way I found was to get the word count for each term, join
on the least used word, and then make sure the remaining terms are in
the titles. but sometimes it returns 10,000+ titles.