MYSQL :: Joins Query

Design / Query Confusion - Joins?
Sorry for the long post here. I'm trying to give enough info. to explain my situation, and am novice enough to not know how much someone might need to know to help.

I'm very new to SQL, I've read through the Sitepoint "Build Your Own Database Driven Website" book, and though I did follow it, I didn't build the joke database, I just converted it to the specific task I want to do to organize some stuff for my department at work.

Basically what I am doing is tracking design drawings; Job name and number, clients, architects, when drawings leave and return for approvals, to the shop and field, etc... Getting all the information that our detailers might need, in one place.

Where I'm running into problems, is designing the database with some thought on how we set up our job numbers and attempting to keep the tables fairly small by creating tables for each year.

A typical job number would be something like:
B06-064 (B is first initial of the Project Manager - 06 for the year - and 064 for the 64th job that year)

04_jobs, 05_jobs, and 06_jobs all have the same columns:

CREATE TABLE 04_jobs (
job_year YEAR(2) NOT NULL DEFAULT ཀ',
id SMALLINT(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
job_name VARCHAR(255),
pm_id TINYINT(3),
client_id SMALLINT(4),
arch_id SMALLINT(4),
finished SET(Ɔ', Ƈ') NOT NULL DEFAULT Ɔ',
PRIMARY KEY ( job_year, id)
);

The job_year seems a bit redundant since the tables are named by year- I was thinking the problem with my select query had to do with the duplicate PKs (id) across tables - this may be a good indication of how little I understand everything - I just added this column and changed the PK this morning.

When I run a query such as:
SELECT * FROM 04_jobs, 05_jobs;

I get rows joined together - each job in each tabled joined on rows.
not what I was expecting. - getting a row for each job - as if the query were on a single table.

When I run a query such as:
SELECT * FROM 04_jobs, 05_jobs WHERE pm_id="1";
I get an error that the clause is ambiguous.

I guess where I'm most confused is not knowing if its:
1. a design flaw
2. a query flaw (everything I've read so far on JOIN seems directed at joining rows - which isn't what I want)
3. Something that is easily handled with a PHP loop (I'm new to PHP too).

I'm doing everything on a command line for now, I haven't written any php for this area of the project.

To Re-write Complex Query With JOINS
I have a problem with one of the JOIN query here.
Below is a brief description of the problem.

tablename : test

RecordId EffectiveDateothertableidvalue
-------- ------------- ------------ -----
12004-01-10110
22004-01-20120
32004-01-20270
42004-01-10280
52004-01-15110
62004-01-25310

Output :
RecordId EffectiveDateothertableidvalue
-------- ------------- ------------ -----
22004-01-20120
32004-01-20270
62004-01-25310

Now I want to use a single SQL query to find a result
where there exist one record for each unique
"othertableid" where the record selected for the
"othertableid" should be the recent one with regard to
"EffectiveDate".

That is from the above records, I want to select
Records with "RecordId" = 2 and 3 because they are the
recent one for "othertableid" = 1 and 2 respectively.
Please be sure that I want to retrive all fields
including "RecordId". The result should not depend on
any other fields but "EffectiveDate" only.

I am using MySQL 4.0.12 and it does not support
"SUBQUERIES" which is now given support in latest
MySQL edition. But I have read in the manual of MySQL
that any "SUBQUERY" SQL statement can be written with
proper "JOINS".

Report Writing Query Problem (joins Again...)
Hola database type folks,

I have been trying to get this right for sometime but now must admit defeat

I have 3 tables

artists -> holds details on the artists
card_gallery -> hold details of card available related to artists via artist_name
scart -> shopping basket related to card_gallery via card_image_id

I am writing a reporting facility that allows the administrator to view all the artists in a table along with their number of cards online and cards sold figures.

Currently I have something like

select
card_gallery.artist_name as cg_an,
count(card_gallery.artist_name) as cg_ca,
count(scart.card_image_id) as s_ci,
from
card_gallery
left join
scart
on
card_gallery.card_gallery_id=scart.card_image_id
group by card_gallery.artist_name
but that doesnt give the correct number of cards sold.
Cards sold are registered in the scart table by setting the processed field to 1

Ideally I would like to be able to view all the artists on one table with the number of cards online and the number sold even if it's 0

Any Tool Where You Can Visually Trace Query Joins?
Talking with a colleague, we wondered if there exists a tool where you can build a query and see all the tables it joins visually but soem sort of line or graph.

Unable To Do Joins With MySQL Query Browser
I downloaded the Query Browser for Windows. I try to join two tables as described in the manual, drag over the JOIN tables tab but nothing happens. The query remains as is.

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.

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:

Joins And Nulls
I have three tables containing information about companies. The first lists companies, each with an id. The second defines what information (attributes) can be held about each company and format it takes (eg address, postcode, owner, etc). The third table lists the specific values of each attribute for each company.

Each attribute value for each company has its own line.

Now, I am trying to select data from this group of tables. The problem is that not every company has complete information. Some, for example, don't have a postcode. But where they do, I'd like to display that. Which brings me to this SQL: 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.

Inner Outer Joins
i have this query

CODE
SELECT distinct u.user_id, u.user_name, u.user_email
FROM tbl_users u
inner join tbl_user_options uo on (uo.user_id = u.user_id and uo.email_id = 10)
and (CAST(date_last_sent AS DATE) IS NULL and uo.user_noreceive is null) OR (uo.user_noreceive = 0 and CAST(date_last_sent AS DATE) <= (DATE_SUB(NOW(), INTERVAL 10 DAY)))
left outer join tbl_photos p on u.user_id = p.user_id
WHERE p.user_id is null

Table Joins
The (untouchable) one is users: here we can't add any field to the existing ones.

I simplify:

users
user_id | otherData (no more team_id here)

tapes
tape_id | user_id | tape_desc | tape_title

teams
team_id | team_name

team_members (new table)
team_member_id | user_id | team_id

When there wasn't this last team_members table, the users table had a related team_id field, so that I could collect all the data I needed this way:

SELECT t.tape_title, t.tape_desc, u.username, t2.team_name
FROM tapes AS t
INNER JOIN users AS u ON t.user_id = u.user_id
--->INNER JOIN teams AS t2 ON u.team_id = t2.team_id
ORDER BY t.tape_title DESC

Now, I've not that (very useful) team_id in my users table and my query must consider the team_members table.

How can I change my query to get the same data I received when I had that field in my users table?

JOINS And/or INTERSECTS
I'm trying to create a query to find missing values in a table.
Example table:
<PRE>
COL1 COL2
---- ----
A 1
A 2
A 3
A 4
B 1
B 3
C 2
C 4

I want my output to be:

COL1 COL2
---- ----
B 2
B 4
C 1
C 3
</PRE>
I thought I could do this with some outer joins but I can't seem to
get even close.

MySQL Joins
Suppose you have the following tables:

CODEt1

_c1_c2_
| a | 1 |
| a | 9 |
| a | 4 |

t2
_c1____c2__
| a | alpha |
| b | beta |

Joins Confusion
From a table of purchase orders ("purchaseorders"), I would like to identify those orders which were NOT placed on hold. Whether or not a purchase order was placed on hold is kept in a separate "holds" table. The two are linked by a "POindex" field.

The following SQL generates the results below (only order 10248 was put on hold):

SELECT holds.huser, purchaseorders.poindex
FROM purchaseorders
LEFT JOIN holds ON (PurchaseOrders.POIndex = Holds.POIndex)
where Purchaseorders.poindex > 10240 and Purchaseorders.poindex < 10250
order by poindex asc

holds.huser purchaseorders.poindex
[Null] 10241
[Null] 10242
[Null] 10243
[Null] 10244
[Null] 10245
[Null] 10247
184 10248
[Null] 10249

But what I really want is a listing of all orders not on hold - in other words:

holds.huser purchaseorders.poindex
[Null] 10241
[Null] 10242
[Null] 10243
[Null] 10244
[Null] 10245
[Null] 10247
[Null] 10249

I've tried the left join condition where purchaseorders.poindex != holds.poindex, but that does not work. I'm obviously missing something simple but can't figure it out.

Outer Joins
is there a workaround for outer joins in mysql? i've got to tables (a,b). what i want to do is select all of a and only the first match from b.

Avoid 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. This is NOT
an architecture or platform issue - I need a design and a data
structure that will scale to lots of variables.

I need another data structure that won't get me stuck on too many
joins.

Understanding JOINs
Table structures:

- AUCTIONS: id | other fields...
- AUCTIONS_BIDS: id | auction | amount | other fields...

When viewing the AUCTIONS, users should be able to filter out records based on whether or not the current high bid for an auction is less than or equal to a value they supply.

I want to select all information from the AUCTIONS table with this query, it doesn't need to return any data from the AUCTIONS_BIDS table. But it does need to be able to reference the AUCTIONS_BIDS table and eliminate auction records in the fashion I described.

As far as I understand JOINs and similar concepts, I am under the impression that this can be achieved with a single query.