MYSQL :: Joins And Nulls

NULLS 2
if I have a table for:

PHONE_NUMBERS

that can be associated with FKeys to one or more COMPANY_ID and/or a PERSON_ID how would you construct the columns/relationships?

Assuming a PHONE_NUMBER_ID as PK then a n:m relationship to both the COMPANY and the PERSON tables??? (i.e. a new table with a compound PK of phone_number_id plus company_id plus person_id) but then I'd have to allow either the company_id or the person_id to be null wouldn't I?

NULLS
if you had a table with columns:

Prefix_ID ---> data for say 75% of rows normalized to Mr/Mrs./Ms
FirstName ---> data for 50% of rows
MiddleName ---> data for 25% of rows
LastName ---> data for 100% of rows
Suffix_ID ---> data for 5% of rows normalized to Jr./Sr./III

would you:

1.) make suffix_ID NULL-able ?

2.) make any other column NULL-able?

3.) make Suffix_ID default to 1 where Suffix_ID 1 = ''?

4.) make FirstName and/or MiddleName default to '' ?

or other alternatives/suggestions for best practices?

Concatenating Nulls
I would like to concatenate two columns where one is potentially null. The result I get now is if one is null, the concatenated value is null. Here's what I would like:

Column1 = '123', Column2 = null, return '123' (not null)
Column1 = null, Column2 = 'abc', return 'abc' (not null)
Column1 = '123', Column2 = 'abc', return '123abc'

The statement that isn't working is something like:

SELECT CONCAT(Column1,Column2) FROM Table WHERE id=12

NOT NULLs With Default
I'm cleaning up a database but I see this all over the MySQL world. I understand that having defaults speeds things up. But what I don't understand is why someone would make a field NOT NULL and then put a null default in it? This makes no sense to me at all. Is this the fault of using phpMyAdmin to create tables?

Here's a sample:
CREATE TABLE `listnames` (
`id` int(11) NOT NULL auto_increment,
`list_name` char(30) NOT NULL default '',
`display_name` char(100) NOT NULL default '',
`active` char(3) NOT NULL default '',
`last_modified` timestamp(14) NOT NULL,
UNIQUE KEY `id` (`id`)
)

Ok, so you don't want NULLs in your data but there is no default. It's very likely that the data will be wildly differently. So setting a default makes no sense -- or does it?
I don't want to drop the default '' if it makes sense & I'm just not getting it.

Finding NULLS
I am querying with the following hoping NULL and not NULL:

SELECT *
FROM pet
WHERE sex='NULL';

returns empty set, but there should be one record.

SELECT *
FROM pet
WHERE death<>'NULL';

also returns empty set when there should be one record.

Count Of NULL And NOT NULLS
How would I write a query to count the number of instances where a the
PREP field is NULL and when it is not null. I know how to do it
seperately, can I get the numbers in the same query. I want something
like this:

Project #PREP_NOTNULL #PREP_NULL

Ignoring NULLs In Indexes
Access has a nice feature that allows creation of indexes WITH IGNORE NULL which means that records with null values are ignored in the index, so the index will be
shorter and faster to search. Is there any equivalent for this in MySQL?

Equality Testing With NULLs?
When I do "SELECT count(1) FROM table_name WHERE 0 <> 2", I get the total number of rows in the table. When I do "SELECT count(1) FROM table_name WHERE NULL <> 2", I get 0. Shouldn't I the same answer as I did with WHERE 0 <> 2?

SQL Select Statement Returning NULLS
when I run this SQL qry:

SELECT
DATE_FORMAT(recDate, '%Y-%m-%d') AS a_date,
recTitle, recBody,
recID
FROM j_plug
ORDER by recID DESC
LIMIT 15;
The 'recTitle' column often returns a NULL. Is there any way I can change the SQL statement to return an actual value if there is a NULL, such as 'NOTHING'.

This is probably a silly request - obviously if it is a NULL, then that should be that. Trouble is that I am having a problem with my ASP code when the value is NULL, and it'd be useful if it returned an actual value for NULL records.

Unexpected NULLs When Using 'left Join'
I'm trying to select all records from a table, plus the appropriate values from other tables to which my first table has pointers. I quickly discovered the benefits of LEFT JOIN over WHERE clauses but then found that I was getting more NULLs than expected. For example: Code:

Unique Index With Nulls Where Is It Stored
The memory space defined by DataMemory is also used to store ordered indexes, which use about 10 bytes per record. Each table row is represented in the ordered index. A common error among users is to assume that all indexes are stored in the memory allocated by IndexMemory, but this is not the case:

Only primary key and unique hash indexes use this memory; ordered indexes use the memory allocated by DataMemory. However, creating a primary key or unique hash index also creates an ordered index on the same keys, unless you specify USING HASH in the index creation statement. This can be verified by running ndb_desc -d db_name table_name in the management client. if i issue a :

create unique index idx_b on tablename(b);

where b can contain nulls is this index stored in index memory and in data memory or just data memory?

NULLs And Empty Strings, Treating Them Equally?
I've built a database class in PHP which, when given an empty string for a field value, converts it to NULL, simply because although their technically not the same thing, as far as useful data goes, they are equally useless.

I want to use the NOT NULL flag on fields to dictate which fields should have data, and which shouldn't, and really, an empty string is no data.

I was just wondering what other peoples opinion was on this, and weather or not it is a reasonable restriction to enforce, I cant think of any situation where data is required for a field (NOT NULL), and an empty string would be a reasonable value.

How To Universally Act On All Cells In A Table, Turning Blanks To NULLS
i should be able to do this, but it's actually been a pain for a while. how can i turn all the blanks in a table to NULLS?

LOAD DATA INFILE, Updates All Other Fields With Nulls.
I am using the following command to update three fields ONLY in the destination table. The infile that I am using was created using:
..........................................................
select code, desc2 , qty_on_hand from plu_tmp
into outfile "c:mp.txt"

..........................................................
LOAD DATA INFILE "c:mp.txt"
REPLACE INTO TABLE `plu` (code, desc2 , qty_on_hand)

I expect to update only the three fields, without touching the other fields.
What happens is that the specified fields are updated, and all the rest of the fields are reset to nulls as they have not been specified.

Is there any way I can use the load inifile to insert / Update just the
specified fields.

Natural Joins And Joins With USING
I was wondering if there is a way to make MySQL 5.0.15 ( final release ) able to use natural joins and joins, using old code that worked with 5.0.11 and earlier.

Since I am new to starting mysql and modifying it, more information the better.

NULLS (was "Is This Normal?")
to have empty cells in the results?
like...i have one column called 'specs' but only a few rows in the result have actually a value in it, the rest is NULL
i know beforehand that the result will be like this most of the time...is it bad?

Joins
Im trying to show 1 image per product using a table join:

$sql = mysql_query("SELECT p.product_id, p.product_name, p.description, i.photo_filename
FROM products AS p
LEFT JOIN product_images as i ON p.product_id = i.product_id
WHERE filter = ".$_GET['filter']."
ORDER BY product_name ASC");
One product has 2 images linked to it .. with that join rather than displayin the record once with a single image .. it shows the record twice, once for each picture in the db then proceeds to the next record.

What kind of join do I need to have it display 1 image for each record in the loop?

Self Joins
I'm not new to mysql however i have an issue with something that im trying to do.
I store football games in a 'games' table, the table has [among other fields] game_id,game_hteam,game_ateam,game_hscore,game_ascore which relates to the gameid, the hometeam id, the away team id and the relevant scores for each side.....

JOINs
I may be trying to do too much in a single query here, but it would be nice if I could get it working! Apparently, the version of MySQL used by my work does not support the WITH ROLLUP feature, otherwise I think that would work.....

Help With Joins
I have 2 tables (users and comments), where the suers table has a bunch of details about each user, and the comments table has comments left by that user. Each user can have more than 1 comment. I am trying to write query that extracts all users alongwith all their comments.

I have something like:
$sql = "Select * from comments left outer join users on comments.userid=user.userid";

This basically gives me the result, but in an unelegant way. I get as many records for the same user as they have comments. Is there a better way to do this, where I get 1 reslt per user and all their comments are in the form of a list or array?

Joins
I have 2 tables (users and comments), where the suers table has a bunch of details about each user, and the comments table has comments left by that user. Each user can have more than 1 comment. I am trying to write query that extracts all users alongwith all their comments.

I have something like:
$sql = "Select * from comments left outer join users on comments.userid=user.userid";

This basically gives me the result, but in an unelegant way. I get as many records for the same user as they have comments. Is there a better way to do this, where I get 1 reslt per user and all their comments are in the form of a list or array?

Joins
I'm trying to modify a query to avoid using group by or distinct to reduce the amount of time to get results.

I want to get every record from table 1 that has a matching record in table 2
The tables are in a 1 to many relationship ie. One record in table 1 will have many matching records in table 2. I've tried right, left and inner and neither seem to give the desired results - any suggestions from the gurus?

Joins.
I'm using mysql to house a database of quotes, complete with ratings by users. So in one query, I need to get everything with a certain rating from the "quotes" table, get the submitter's username from the "users" table and finally check the "ratings" table to see if the user viewing the quotes has already rated it.

The "ratings" table consists of the user id and the quote id. So ideally, I'll be able to to get the first 25 quotes and end up with each in a row something like this:

submitter's id - quote id - quote text - user id - quote id 2

Joins
I have three tables named project, projecthistory, and billing for my project tracking database. My problems arises when a project doesn't have a projecthistory or any billing items entered for it yet. Then the project doesn't show up at all.

project: id, name
projecthistory: project_id, description, submitdate
billing: project_id, monthlyrate, quantity

SELECT MAX(ph.submitdate), SUM(b.quantity), p.id, p.name, ph.description,
FROM (project p LEFT JOIN billing b ON p.id = b.project_id) LEFT JOIN projecthistory ph ON p.id = ph.project_id;

Ultimately what I need is for ALL projects to show up. If any projecthistory exists, display ONLY the newest one (for that project_id). If any billing exists, display the SUM of the quantities (for that project_id).

Out Of Joins
I have this query:

SELECT c.id AS cid, c.cat_name, f.id AS fid, f.forum_name, f.forum_desc, f.redirect_url, f.moderators, f.num_topics, f.num_posts, f.last_post, f.last_post_id, f.last_poster, t.question, f.parent_forum_id FROM '.$db->prefix.'categories AS c INNER JOIN '.$db->prefix.'forums AS f ON c.id=f.cat_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$forum_user['g_id'].') LEFT JOIN '.$db->prefix.'topics AS t ON f.last_post=t.last_post AND f.last_post_id=t.last_post_id WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND (f.parent_forum_id IS NULL OR f.parent_forum_id=0) ORDER BY c.disp_position, c.id, f.disp_position

and i want to add the field displayname from table: '.$db->prefix.'users where username = f.last_poster

I have used all of my joins i think (left, right, inner) and i need to add that in, can someone add this in.

Many Joins
I've this table structure:

t2(jb, B)
|
tj12(b, jb)
|
t1(A, b, c)
|
tj13(c, jc)
|
t3(jc, C)

How can I do an elegant "SELECT A, B, C FROM t1" ?

When I try
JOIN tj12 USING(b)
JOIN t2 USING(jb)
JOIN tj13 USING(c)
JOIN t3 USING(jc)
MySQL join tj13 with t2.
Must I use "JOIN ... ON ..." to force MySQL or thre's an other way?

Using Joins
I'm having trouble figuring out which join i should be using to attain the results I am looking for. here's a bit of background information.

I have 3 tables

Table 1 - hp_cats
- cat_id
- cat_name

Table 2 - hp_projects
- project_id
- various other fields that are irrelevant

Table 3 - hp_proj_cat
- pc_id
- cat_id
- project_id
- active

Basically, I have a table of projects, and a table of categories. each project has an entry in hp_proj_cat for each category. if the category is active for that project, active = 1 in hp_proj_cat.

I'm looking for a way to retrieve all projects that have categories say '85, 200, 13, 6' and are active (ie, set to 1).

I know this will involve a join, but am a tad bit confused. I greatly appreciate any help anyone can provide.

Sql / Joins
I have 4 tables: tblProducts (it has all the product names), tblInventory (it has all the products actually in inventory), tblMainIngredient (it has all the main ingredients ids associated with the products' ids), and tblProductIngredient (it has all the ingredients' ids).

I am trying to retrieve all the products and their main ingredient but also those that don't have one. I need to be able to display the product name (in tblproducts) as well as the name of its main ingredient (tblproductingredient). I wrote the following SQL but it doesn't seem to work as it returns only the products that have an ingredient associated with them:

SELECT * FROM (tblProduct INNER JOIN tblInventory ON tblProduct.ProductID = tblInventory.ProductID) INNER JOIN (tblMainIngredient INNER JOIN tblProductIngredient ON tblMainIngredient.IngID = tblProductIngredient.IngID) ON tblProduct.ProductID = tblProductIngredient.ProductID WHERE tblProduct.ProductID = " & productID ORDER BY Name, Price, Comments

Joins
I was wondering if someone cold explain the JOIN to me, I've looked over the forum but don't understand.
I know kind of that the INNER JOIN is the same as WHERE???
whats the difference between LEFT and RIGHT JOINS?
And INNER and OUTER JOINS???
Please help because I'm having major problems with my search sql which I'm trying to write for my uni coursework

Joins
Table Oelsls contains all products bought (history) table Invt contains
the products to sell (Inventory) I'm trying with no success to list all
products in Oelsls table (field name in both tables: part_id) that are not
in the Invt table.
My objective is a grid of all Special (non inventory) products.
Can anyone produce the correct sql statement?

Joins
an explanation of the different types of JOIN.
LEFT, RIGHT, INNER, OUTER etc.
I can find syntax for all the instances but feel a discussion which compares the different types will be very useful.

Self Joins
however, when I do this, I am only able to display the two parts that meet the ON statement, and not have the right side null when there is nothing that exists on a left join. that is how it should be.

for example

select this1.something, this2.something from sametable as this1 left join sametable as this2 on this1.column = this2.column
where this1.form = 'red' AND this2.form = 'blue'

I have used this, and it works. basically, I am joining the red columns with the blue columns and displaying the data on one area because the red columns are different rows and so are the blue columns. anyway, is there a way to make it so that if there is no blue column that exists, the red column will display its contents.

Joins
i have these two queries:Code:

SELECT totalscore FROM top_scores WHERE userid=3
SELECT MIN(score1),MIN(score2),MIN(score3) FROM scores WHERE userid=3

how can i use join to make that query into one, but still selecting from the two seperate tables?

Joins
I'm trying to join the equipment table whenever "nsidetails.equipmentid = equipment.itemid". I get correct data but the equipment table never joins.

SELECT equipmentid, COUNT( equipmentid ) AS numOfItems
FROM nsidetails
LEFT JOIN equipment ON nsidetails.equipmentid = equipment.itemid
WHERE nsidetails.itemstatus = 'SHORT' AND nsidetails.location = '".LOCATION."'
GROUP BY nsidetails.equipmentid
ORDER BY nsidetails.equipmentid

JOINs And UserNames...
i need a bit of help on the following problem.
Table I
Row_i. 1 2 _
Row_ii. 3 1 6
...
Row_j. 9 _ _
The numbers in each row identify users (User_A, User_B, User_C); not all rows have all three users filled in - in the example above, the first row has two users, the second three, and the last only one.
Table II
Row_i. 1 Fred Bloggs
Row_ii. 2 John Smith
Row_iii. 3 Pat Brown
...
Row_j. 6 Tom Adams
...
Row_k. 9 Vicky Brown
...
Row_l. n Name Surname

I can thus identify each user (if present) in Table I.

So far, so good. Now for the problem. I need to generate a list of ALL the names used in Table I, either as User_A, User_B, or User_C, without repetitions, ordered by Surname. Given the example above, the output should be
6. Tom Adams
1. Fred Bloggs
3. Pat Brown
9. Vicky Brown
2. John Smith
Simple, right?... But I guess the old brain is just not cooperating today, because I can't seem to get my query right.
Any simple and elegant solutions out there?
Many thaks in anticipation for your help.

Joins And Limit Used Together
I have a query, which reads all information from tables.

select se.section_name,pr.*,ph.code from prices pr left join pharmacy ph on pr.pharmacy_id=ph.id left join sections se on pr.section_id=se.id where medication_id=7 order by se.order_id asc, cast(`dosage` as SIGNED) asc, cast(`total_price` as SIGNED) asc

result can be viewed here:
http://www.foreign-drugstores-online...oduct.php?id=7

I need to read only top 5 (total price) for each `dosage`

somekind of: group by `dosage` order by total_price desc limit 5 per group

Limiting Inner Joins
This has been bothering me and the relation isn't typical of the other expressions I've had to build.

I have a table of 250 records that I'm returning ie.

SELECT B.ID, B.Title, B.Owner
FROM blogs AS B
WHERE B.Status = 0
I need to return not only the blog name but the latest post they've made on their account. So what I did was used:

SELECT B.ID, B.Title, B.Owner
FROM blogs AS B
INNER JOIN archives AS A ON B.ID = A.BlogID
WHERE B.Status = 0
But when I do this it returns each record in archive with the relevent blog information. What I would like is for it to determine the latest ID insert on the blog.

So if he has posts IDs such as 1010, 1050, 2063 and 2075 then it will return the blog information and the results of record 2075 in the archives table.

I've tried a couple ways so far but each fails, I think what I want to do is somewhat limit the return on the inner join...sort of like add a WHERE clause unto the clause.

I would have done this with a stored proc. or a view but I'm using MySQL 4 and I want to keep the querying function in my code for now.

Not sure if that makes sense to the experts, if my problem isn't clear let me know I'll explain it further.

Conditional Joins
This is similar to a previous thread of mine: http://www.sitepoint.com/forums/showthread.php?t=375169

I have two fields in my user table, 'lastentryid', and 'lastprivateentryid'. What I need to do is join the entry table using that entryid. Unfortunately, the results are skewed if their last entry is private, because lastentryid won't contain it. I am running into this problem in a few places, and I'd really like to solve it without using subqueries. I might even accept better organization on the db level.

Here is my query as it is now

SELECT
user.userid, user.options, user.displaygroupid, user.usergroupid, user.username,
ugroup.opentag as opentaga, ugroup.closetag as closetaga,
dgroup.opentag as opentagb, dgroup.closetag as closetagb,
entry.title, entry.dateline as entrydateline
FROM vb_user as user
LEFT JOIN vb_usergroup as ugroup ON(user.usergroupid = ugroup.usergroupid)
LEFT JOIN vb_usergroup as dgroup ON(user.displaygroupid = dgroup.usergroupid)
LEFT JOIN vb_blog_entry as entry on (entry.entryid = user.lastentryid)
WHERE entry.dateline > $cutoff
ORDER BY user.username ASC
Here is what I want... but this doesn't work.

SELECT
user.userid, user.options, user.displaygroupid, user.usergroupid, user.username,
ugroup.opentag as opentaga, ugroup.closetag as closetaga,
dgroup.opentag as opentagb, dgroup.closetag as closetagb,
entry.title, entry.dateline as entrydateline,
if (
user.lastentryid > user.lastprivateentryid,
user.lastentryid,
user.lastprivateentryid
) as last_entryid
FROM vb_user as user
LEFT JOIN vb_usergroup as ugroup ON(user.usergroupid = ugroup.usergroupid)
LEFT JOIN vb_usergroup as dgroup ON(user.displaygroupid = dgroup.usergroupid)
LEFT JOIN vb_blog_entry as entry on (entry.entryid = last_entryid)
WHERE entry.dateline > $cutoff
ORDER BY user.username ASC

Multiple Joins
My database has three tables with the following fields:

--tblIngredients--
IngredientID
Ingredient
IngredientInfo

--tblRecipeIngredients--
RecipeIngredientID
RecipeID
IngredientID
Quantity

--tblRecipe--
RecipeID
RecipeName
Directions

tblRecipeIngredients is a join table to link each recipe from tbRecipe with it's respective ingredient(s) from tblIngredients.

Now, I'm trying to write a query that will return all recipe names (RecipeName) that don't have each of the Ingredients specified. In other words, I'm going to have a form that allows the user to select their on-hand ingredients and I want the query to eliminate all the recipes that include ingredients that the user does not have on hand and return the rest. I hope I'm making sense here...

Anyhow, this is what I have so far, but it doesn't work the way I would like it to:

MySQL Joins
Table 1: messages
has the following columns:
ts: datetime stamp
username: username of who sent the message
message: Text of the message itself

Table 2: users
Has these columns:
username: obvious
joined: What time they joined
status: enumerated, either active, away, or kicked

I need to do the following:

Select all messages which were sent no earlier than when the user joined. So the timestamp of the message must be greater than or equal to the time at which the user joined.

I tried this, but it doesn't work at all:

SELECT TIME(ts), messages.username, message FROM messages LEFT JOIN users ON ts >= joined

Not sure why it doesn't work, but I'd like to know :)

Joins On Several Tables
A have a table "users" of users, for this matter let's say with two fields: id (int) and name (varchar).

I also have a table "replies" with various user replies.

Fields: user_id (int), input_id (int), reply (varchar)

This table works as one entry per reply â€“ if a user replies to a form with four input-fields this is recorded as four lines in this table. The inputs are identified by input_id.

OK. The task is, being presented a number of input field ids (lets still say four), to retrieve a result set of users having replied to these fields and their replies.

This would be a simple join, BUT i want a row even if the given user has replied to less than all of the given inputs. IE there may not be matches for all inputs. No matter which combination of LEFT and RIGHT JOINs i try I seem to leave out some. Code:

Outer Joins
I canÂ´t figure out how to create the following SQL

select a.folio, a.tipo, a.date
from solic a, order b
where a.id = b.id_sdc and b.tipo!=399 and b.tipo!=398

so far so good
besides, I want the rows from table a with no match in table b

select a.folio, a.tipo, a.date
from solic a, order b
where (a.id = b.id_sdc and b.tipo!=399 and b.tipo!=398)
or a.id not in (select id_sdc from b)

this select does not work with the version I have. I tried using the left join but couldnÂ´t get the right rows

Alternative To Self-joins?
I have a table that has values of variables for certain entities. The
columns of interest are targetID, variableID, and valueID. A row (1, 5,
9) means that target number 1 has a value of 9 for variable 5. Being
denormalized, target number one will have many possible rows in this
table, one for each variable for which it has a value.

My problem occurs when I want to find out what targets match a certain
set of variable values. For instance, I want to find out what targets
have a value of 9 for variable 5 and a value of 25 for variable 10. I'm
thinking that this can be a simple self-join:

SELECT mya.targetID from mytable as mya
LEFT JOIN mytable as myb
ON mya.targetID=myb.targetID
WHERE (mya.variableID=5 AND mya.valueID=9)
AND (myb.variableID=10 AND myb.valueID=25)

Does this make sense so far? The problem is that this doesn't scale.
When I have more than 31 variables and I need to evaluate them all,
MySQL breaks: I can't do more than 31 joins.

My design calls for perhaps 80-100 variables, so even 64-bit
architecture with a limit of 64 joins won't get me there.

I need another data structure that won't get me stuck on too many
joins. Any suggestions? If I have to scrap this approach in favor of
another,

Complex Joins
Here is the problem. Table_1 (gl_Train_KeyIdeas)

+----------------------+----------------+-------------------+---------------+
| KeyIdea_ID | Unit_ID | Group_ID | Title |
+----------------------+----------------+-------------------+---------------+
| 1 | 1 | 27 | yada 1 |
| | | | |
| 2 | 1 | 27 | yada 2 |
| | | | |
| 3 | 1 | 27 | yada 3 |
| | | | |
| 4 | 1 | 27 | yada 4 |
| | | | |
+----------------------+----------------+-------------------+----------------+

Table_2 (gl_Train_Progress)

+----------------------+----------------+----------------------+
| ID | User_ID | KeyIdea_ID |
+----------------------+----------------+----------------------+
| 12 | 5 | 3 |
| | | |
| 11 | 5 | 2 |
| | | |
| 10 | 5 | 1 |
| | | |
+----------------------+----------------+-----------------------+

The following sql returns field KeyIdea_ID = 4 which is the only
KeyIdea not in both tables.

SELECT gl_Train_KeyIdeas.KeyIdea_ID
FROM gl_Train_KeyIdeas LEFT JOIN gl_Train_Progress ON
gl_Train_KeyIdeas.KeyIdea_ID = gl_Train_Progress.KeyIdea_ID
WHERE (((gl_Train_Progress.KeyIdea_ID) Is Null) AND
((gl_Train_KeyIdeas.Unit_ID)=1));

What I seem to be having trouble with is specifying the User_ID in
table 2. I need to specify the current user for example: an sql with a
User_ID = 6 would return KeyIdea_ID of 1, 2, 3, and 4.

TABLE JOINS
I am trying to joing two table and get certain information back based on YEAR.

I've tried a left inner join against TableA, but it excludes certain members. Any help would be great!

My Query:
SELECT MEM_ID,FNAME,LNAME,MNAME,PRIM_EMAIL,PAY1,PAY2,YEAR FROM (
select MEM_ID,FNAME,LNAME,MNAME,PRIM_EMAIL,IFNULL(N.PAY1,'N') AS PAY1,IFNULL(N.PAY2,'N') AS PAY2,IFNULL(N.YEAR,'2007') AS YEAR
FROM MEMBERS M LEFT JOIN NATIONAL_DUE_STATUS N
on M.MEM_ID = N.FK_MEM_ID ) AS NAT_DUE_VIEW WHERE YEAR=2007;

Code:

Timeouts When Using JOINs
I'm working on a database that stores the results of answers to questions for testing / training purposes.

I'm having a problem with the inner loop consistently timing out at 30 seconds. I've adjusted the timeout values in-script and in configuration but that doesn't seem to have had any effect at all despite restarting the server. Error logs indicate a pipe error which fits with the script not completing and hence the timeout.

I've run the "OPTIMIZE <table>" command and added indexes in the transaction tables of the next table up to help optimise but this doesn't seem to help either. (ie. questionID for the QuestionAnswer table). Tables such as Question are using their primary key as an index (i.e. QuestionID for Question table) Code:

Joins Or Inner Selects
I have 2 tables with a group name, date and premium column I want to sum both of them for a specific month.

Table 1
--------
group 1 | 2005-12-01 | 50 |
group 1 | 2005-12-01 | 50 |
group 2 | 2005-01-01 | 40 |
group 1 | 2005-01-01 | 50 |
group 1 | 2005-12-01 | 40 |

Table 2
--------
group 3 | 2005-12-01 | 10 |
group 4 | 2005-01-01 | 40 |
group 3 | 2005-01-01 | 50 |
group 3 | 2005-12-01 | 10 |

so my sql answer should be :

2005-12-01 | 140 | 20 |

I have been using this

select sum(a.totalpremium), sum(b.totalpremium)
from `productioncalc` a
inner join `policies` b
on a.`group name` = b.`group name`
and a.`inception date` = b.`inception date`

where a.`inception date` = '2005-12-01'

its not working, its mulitplying everything togerther etc, should I use normal select or is my join wrong? I am using mysql 4

Multiple Joins
How can one link two fields in one table to single field (Primary key) in another table in a single (left join) query, to return two values? e.g.

Table 1
ID (PK)
...other fields
Departure_ID
Arrival_ID
...other fields

"places" table
Place_ID
Placename (value to use in view)

I think this is illegal but, more probably, impossible; perhaps someone could suggest an alternative methodology.