MYSQL :: Summing Two Columns From Two Left Outer Joins

LEFT OUTER Is Faster Than INNER Joins?
Maybe someone can explain this to me real quick. I have two identical queries, except that the join type changed. One uses inner joins:

SELECT OrderItems.product_id AS product_id
, OrderItems.code AS product_code
FROM Orders
INNER
JOIN OrderItems
ON Orders.id = OrderItems.order_id
INNER
JOIN Products
ON OrderItems.product_id = Products.id
WHERE Orders.orderdate >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 24 HOUR))
AND Products.active = 1
GROUP
BY OrderItems.product_id
and the other uses the basic LEFT OUTER JOIN:

SELECT OrderItems.product_id AS product_id
, OrderItems.code AS product_code
FROM Orders
LEFT OUTER
JOIN OrderItems
ON Orders.id = OrderItems.order_id
LEFT OUTER
JOIN Products
ON OrderItems.product_id = Products.id
WHERE Orders.orderdate >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 24 HOUR))
AND Products.active = 1
GROUP
BY OrderItems.product_id
When I run the two queries on the same data (using phpmyadmin), the left outer joins only take 0.12-0.13 seconds whereas the inner joins take a whopping 2.5+ seconds. I kinda thought that an inner join would be faster. I'm still bad with determining join types.... Is there a fundamental reasoning for this that I am missing?

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

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

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.

Inner , Outer Joins
what is the use of left inner join, right inner join ? will you please explain me ?

Outer Joins Return Value
Is there a way for an OUTER JOIN statement to return a value other than NULL?

UNION Outer Joins
I know this returns all the records in a field, and that it is the longest way to return these records, but it is the product of 2 built statements, so there is reason behind the madness.
(

SELECT DISTINCT contact. *
FROM contact
LEFT OUTER JOIN location ON ind_id = loc_indid
WHERE loc_id IS NULL
)
UNION (

SELECT DISTINCT contact. *
FROM contact
LEFT OUTER JOIN location ON ind_id = loc_indid
WHERE loc_id IS NOT NULL
)

Bascially, the first statement selects all entries in contact which do not appear in location, and the second does the reverse.

At the moment, the two statements work independantly, but once I add the UNION statemnet, it returns an error in the string after UNION. Where am I going wrong??

LEFT OUTER JOIN + WHERE ?
I have problem with constructing a SQL query. I have tables with data:

mysql> SELECT * FROM category;

+----+-----------+
| id | is_active |
+----+-----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
+----+-----------+

4 rows in set (0.00 sec)

mysql> SELECT * FROM category_i18n;

+------------------+----+---------+
| name | id | culture |
+------------------+----+---------+
| Agroturystyka | 1 | pl_PL |
| Bed&breakfast | 2 | pl_PL |
| Hotel | 3 | pl_PL |
| Obiekt zabytkowy | 4 | pl_PL |
+------------------+----+---------+

4 rows in set (0.00 sec)
Category table includes category id and is_active status. Category_i18n has category names translations for several cultures and the id field is a foreign key.

Now I'd like to get result like this:
mysql> SELECT c.id, ci.name FROM category c LEFT OUTER JOIN category_i18n ci ON c.id=ci.id WHERE ci.culture='pl_PL' OR ci.culture IS NULL;

+----+------------------+
| id | name |
+----+------------------+
| 1 | Agroturystyka |
| 2 | Bed&breakfast |
| 3 | Hotel |
| 4 | Obiekt zabytkowy |
+----+------------------+

4 rows in set (0.00 sec)
And in this case (culture='pl_PL') it works fine. But when I change culture to 'en_US' I don't get any results. How to change my query to select all ids from category table and join name field from category_i18n table but with culture condition. If there is no translation there should be a NULL value.

Result I'd like to get for culture='en_US':

+----+------------------+
| id | name |
+----+------------------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
+----+------------------+

Left Outer Join
I have noticed when I do a Left Outer Join in short form that many rows
become missing as result of null values.

i.e Left Outer Join (t1,t2,t3) ON (....)

T3 being table with null values in

It seems to be all to hard when I log a bug.

Left Outer Join
I have just converted an app from MS Access to MySql. I have using Hyperion Performance to run my queries. I have 4 tables and main table and three look up tables. when I do a left outer join to one table the query runs fine, however when I do the left outer join to more then one table it error out.

with this error message: You have an error in your sql syntax check the manual that corresponds to your Mysql server version for the right syntax to use near {oj left outer join rc_availability Al4 Al1.rc=Al4.rc} where al1.msa=al at line 1 Code:

Left Outer Join
This is what I have come up with:

SELECT CP1.PosID, CP1.Rank, CP1.BookID, CP2.BookID AS LastWeekBook, CP1.StationID, CP1.ChartTypeID, CP1.WeekID ,
CP2.Rank AS LastWeekRank, CP2.WeekID AS LastWeek
FROM Position AS CP1
LEFT OUTER JOIN Position AS CP2 ON CP1.BookID = CP2.BookID
WHERE CP1.WeekID = {d '2007-05-08'}
AND CP2.WeekID = DATE_SUB({d '2007-05-08'}, INTERVAL 7 DAY)
AND CP1.ChartTypeID = 101
AND CP2.ChartTypeID = 101
ORDER BY Rank;

(Note: joins to other tables containing author infor and book titles etc, have been omitted for simplicity.)

Instead of returning 50 rows (one for each matching row in CP1) it returns 36 rows (only those rows where the book appears in both CP1 and CP2).

I have tried alternative join syntax as follows and all return the same result:
LEFT JOIN
INNER JOIN (36 rows would be expected)
RIGHT JOIN
RIGHT OUTER JOIN

As far as I can tell the SQL as written should be giving me the desired result but it isn't and I can't see what I might be doing wrong.

Left Outer Join
^ just a quick question I can't find an answer for, with the mysql syntax is 'left outer join' the same as 'left join' ?

LEFT OUTER JOIN
am using mysql and want a recordset to be displayed from a query that links three tables.
because a user can add to their selection at a later date i would like all records from a tbl_fitopt to be displayed even when there are no foreign keys in the other tables.
sql is below but at the moment it only lists records from tbl_fitopt that have keys in tbl_fitoptlink - nb i need all records from tbl_fitopt to display so a user can add/subtract from there choices.

strsql = "SELECT tbl_fitopt.fitoptid, tbl_fitopt.fitoptcost, tbl_fitopt.fitopt, tbl_stock.stockid, tbl_fitoptlink.fitoptlinkid "_

& "FROM (tbl_fitopt INNER JOIN tbl_fitoptlink ON tbl_fitopt.fitoptid = tbl_fitoptlink.fitoptid)
INNER JOIN tbl_stock ON tbl_fitoptlink.stockid = tbl_stock.stockid "_

& "WHERE tbl_stock.stockid = " & tkey & ";"

Assitance Requested On Syntax With Joins (outer)
Select d.* from
( select agent_addr, max(date_time) from eventlog where priority = 1
group by agent_addr) d,
(select agent_addr, max(date_time) from eventlog where priority = 6
group by agent_addr) u
where d.agent_addr = u.agent_addr
and d.access_time >= u.access_time

The above statement is to get certain data from an event table. I need
all of the lines where the event is 1 and there are no newer event 1's
or event 6's

I need to know what devices are currently down (Priority 1) based on
this history table. where there is no newer event 1 entries for the
agent_addr, or there is no newer priority 6's for that agent_addr
(priority 6 means device is back up)

I'm stumped on the syntax to do joins, or to get this to work.

the table works like the following
device goes down entry is made as priority 1 eventid 1
device comes back up new endtry is made priority 6 eventid 2
device goes back down another entry is made as priority 1. eventid 3

I need to make sure i only am getting a list of the latest status of
each device in the aformentioned scenario it would be the one with
event ID 3

Multiple LEFT OUTER JOIN
What is the right way to do a LEFT OUTER JOIN on both these tables to the 'Main' table where WHERE Plan.pID IS NULL and Record.rID IS NULL (primary's)

Plan
pID | _mn (foreign main key) | pType

Record
rID | _mn (foreign main key) | rDate

Main
mID (primary) | mComp

-------------------------------------
Will need: pType, rDate, mComp values as a result of the query.

Struggling With Left Outer Join
got two tables:

T1 - User, Date, Stuff

T2 - User, Date, Other Stuff

I want to be able to select rows from T1 based on User & Date, join those rows to the data in T2, so T1.User=T2.User AND T1.Date=T2.Date. Then I want to sort the results in T1.Date order

I've no idea where to start ... anyone point me in the right direction?

Performance Left Outer Join
I changed a query from an equal join to a left outer join but then the performance is suddenly 10 times as slow ?? Is this normal or do I miss something ??

$query1="SELECT * FROM ";
    $query1=$query1 . "clienttable,contacttable AS co1 WHERE co1.coclid=clid ";
    $query1=$query1 . " AND clname like '$alfabetisch%' ";
    $query1=$query1 . " order by $o";

Left Outer Join Syntax
I am trying to join a table to three other tables

Table 1 has all records
Table 2-4 must be left outer joined to table 1, not in a chain

using "other" dbs ...

select t1.a
,t2.b
,t3.c
,t4.d

from t1
,t2
,t3
,t4

Where t1.id = t2.id (+) and t1.id = t3.id (+) and t1.id = t4.id (+)

I've tried using the left outer join syntax ...

select t1.a
,t2.b
,t3.c
,t4.d

From t1
LEFT OUTER JOIN t2 using (id)
LEFT OUTER JOIN t3 using (id)
LEFT OUTER JOIN t4 using (id)

but that, of course chains t1 to t2, t2 to t3, and t3 to t4.

How can I make this join work, without the use of subqueries?

Left Outer Join Produces
In order to eliminate duplicate values from the right of a query I tried the following:

LEFT OUTER JOIN
        `private_comments`
    ON
        `report_cards`.id IN (select distinct `private_comments`.student_id1 from `private_comments`)

I have unique `report_cards`.id but multiples of `private_comments`.student_id1.

Sould Someone Verify LEFT OUTER JOIN?
could someone please verify :

SELECT *
FROM Members, Submits, MNotify LEFT OUTER JOIN MemPay as mp on
(mp.MPMem_ID=Members.MemID and Members.MemID=colname and mp.MPSub_ID=MNotify.FK_SubmID)
WHERE Submits.SubmID=MNotify.FK_SubmID and Members.MemID=colname
MemPay - payment table, where
MPID | MPMem_ID (actually foreign) | MPSub_ID

Members
MemID

Submits - submitted ads
SubID

MNotify - Members become notified of individual submitted ads
MNID | FK_MemID | FK_SubmID

Members can view each MNotify.FK_SubmID (submitted ads -- the ads may be bought, and if so are found in MemPay table. MPMem_ID is the buyer primary id and MPSub_ID is the id of the submitted ad bought.)

In any case, the Member can view all submitted ads (including purchases) while the check on the LEFT OUTER JOIN values gives what he or she has bought.

While it seems to output as it should, when I check on the LEFT OUTER JOIN values with php conditional statements I've getting values oppositie of what they should (checking on NULL, <>NULL, empty, !empty)

Left Outer Join Doing Wired Things
i have two tables, tableA and tableB, i want all of tableA but regardless of if there is a record in tableB that references the record of tableA, so thats what an outer (or left, or left outer) join should do if tableA is the left table... right?

Ok first my Query:

SELECT *
FROM tableA A
LEFT OUTER JOIN tableB B
ON A.ID=B._IDa
WHERE ((B._IDb IS NULL) OR (B._IDb=3)); -- no record exists in tableB with _IDb=3

tableA is referenced by tableB's foreign key field _IDa
tableA has 27 rows, tableB has many rows but none that have _IDb=3

So I expect to get 27 rows with NULL for all fields of tableB, but i only get 18 rows!!
(all fields of tableB are, as expected, NULL, but WHY those 18?!! why not all or none?!)

Note though that B is a composite entity, where _IDa is only half of the primary key, _IDb is the other half referencing someother table that's not important for this problem.

Code:

Slow Execution For A Left Outer Join Query
Whats likely to be the cause of slow execution for a left outer join query?

The original query joins three tables but even if I narrow it down to one it still takes a long time to execute.

$query = "select distinct materials.* from materials";
$query .= " left outer join materials_products on materials.material_id = materials_products.material_id";

There's 914 rows in the materials table and 1348 row in the materials_products table

Is it likely to take a long time for this amount of data or is there likely to be a problem in the table(s) set up or query?

2 Left Joins
I tried the query below and it seems to double count the results.
Code:

select a.prospect_id,
count(b.referral_id),
count(c.fepslead_id)
from prospect a left join referral b on a.prospect_id=b.prospect_id
left join fepslead c on a.prospect_id=c.prospect_id

Is it possible to do left joins twice on the same table and that i am doing it wrong or is it impossible and that i may have to redesign the way my tables are linked.

Left Vs Inner Joins
I'm sure this has probably been asked before and, don't worry, I have searched the forums and found some answers.
BUT does anyone know any links to exampls of the two joins? I've got explanations, sure. But I feel I'd better understand what left joins do (over inner ones) if I could see an example of the output you'd get on a simple DB.Unless anyone has the time or inclination to write one?

Using Left Joins
ive searched for mysql tutorials and cant really seems what im after. basically i have two tables like below and i want to return all the player ids, firstname, lastname. from the player table that DONT appear in the top ten table so the ones that do appear in top ten i dont want returned ? so from below im trying to just get players 1 and 3 (bob and ken) to print out
Player
---------------
id | firstname | lastname
---------------
1 | bob | smith
2 | jim | bill
3 | ken | ten

//-------------
Top Ten
------------
id | playerid | position
------------
1 | 2 | 1

2 Left Joins Of The Same Table
i have 2 tables that i want to count

TABLE 1
categories
--id
--name

forum_comments
--id
--category_id
--index (0 for top topic, or comment ID for sub posts)
--title
--comment

I'm trying to list all of the categories w/ a topic count and a posts
count, so I'm trying to join a select of categories with count()s. my
joins are of the same table but they keep coming up the same number?

select
C.id ,
C.name,
count(F.id) as topics,
count(F2.id) as posts
FROM categories as C
LEFT OUTER
JOIN forum_topics as F ON C.id=F.category_id AND F.level=0
LEFT OUTER
JOIN forum_topics as F2 ON C.id=F2.category_id
GROUP BY C.id

Multiple LEFT JOINs
I have four tables which I want to join to a table with "complete" information.
There are about 2000 rows in each table.

When I try a multiple LEFT JOIN it works as it should but I takes about 20 seconds or more.

How do I optimize a query to handle many rows? What other JOIN-methods can I use for this?
Should I index in any special way?

Nested LEFT JOINs
I've been running MySQL version 4.1.7 on my test server and now have transfered my database and php files to my ISP and they are running MySQL version 3.23.56. Some of my queries are no longer working and I'm guessing it has something to do with the nested left joins? An example:

SELECT id_ctg, name_ctg, longname_ctg
FROM categories_ctg LEFT JOIN products_prd LEFT JOIN prodtomodel_ptm ON categories_ctg.id_ctg = products_prd.idctg_prd ON products_prd.id_prd = prodtomodel_ptm.idprod_ptm
WHERE prodtomodel_ptm.idmod_ptm = MMParam1

MMParam1 = $HTTP_GET_VARS['somevar']

Am I correct in assuming its the joins? And how would I rewrite the query?

Joins And Join Left
Can someone please explain to me how they work?
I was looking over the mysql.com web site for joins and i don't understand it.
Is it possible if someone show me a VERY simple example and how joins actually work.

Multiple Left Joins In One Table
When doing multiple left joins in one table: for each join, which table is considered to be the "left" table (ie: all results must be returned).

The Real Difference Between LEFT And RIGHT JOINS
What is the real difference between LEFT JOIN and RIGHT JOIN?
Are there situations where a query with RIGHT JOIN cannot
be rewritten with LEFT JOIN and tables reversed? (and vice versa).

In MySQL documentation there is stated explicitly:
"RIGHT JOIN is implemented analogously to LEFT JOIN, with the roles of
the tables reversed."

So may be it would be enough to have one universal LEFTRIGHT join
and put tables in a query in proper places depending on what
results are needed? What are two different JOIN syntaxes for?

MySQL 5 Left Joins Not Working
I recently upgraded from MySQL 4.1 to 5.0.15. However, to my horror I have discovered that no LEFT JOINs work anymore.

For instance, the error #1054 is produced with the message Unknown column 'items.item_id' in 'on clause' when the following left join is executed:SELECT items.itemid, items.title, md.keywords, cat.category

FROM items_table AS items, meta_data_table AS md, categories_table AS cat
LEFT JOIN restricted_table AS rst ON items.item_id=rst.item_id
WHERE items.title LIKE 'the%'
AND md.item_id=items.item_id
AND cat.cat_id=items.cat_id
AND rst.item_id IS NULL
GROUP BY items.item_id
ORDER BY items.title DESC
LIMIT 10

This query worked fine in MySQL 4.1 and the current 5.0 documentation doesn't seem to indicate that it shouldn't. What could have happened to cause this?

NULL Values Are Excluded In Left Joins
I have 2 tables:Customer and products, for each customer record i have product1, product2,product3 which contain product codes taken from the products table, but any of the product1, product2, product3 fields can be empty i.e contain null values.

I need to retrieve all customers with the product names for fields product 1,product2 and product3, if any one these is null, it should be pulled as NULL itself. I have used left join on products tables but still, those records which have any prodcut field as NULL just doesnt come out.

Indexing Effeciently With Multiple Left Joins
Can anyone point me in the right direction here.
Tell me what to do, or where to find information?

I am trying to access a PHP page and it takes too long.
The problem is the SQL code is in not efficient.

How do you index effieciently with Joins?

The code has four Left Joins.
Looks something like:

$sql = "SELECT
distinct(hi.item_id),
hi.*,
CONCAT(c.contact_first_name,' ',c.contact_last_name) assigned_fullname,
c.contact_email as assigned_email,
p.project_id,
p.project_person,
p.project_color_identifier,
his.status_date
FROM helpdesk_items hi
LEFT JOIN helpdesk_item_status his ON his.status_item_id = hi.item_id
LEFT JOIN users u ON u.user_id = hi.item_assigned_to
LEFT JOIN contacts c ON c.contact_id = u.user_contact
LEFT JOIN projects p ON p.project_id = hi.item_project_id
WHERE $where" .

Left Join Multiple Columns
I am having problems LEFT joining Muti - columns to ONE table

This is what I am using:

SELECT *
FROM nanny_tbl left join position_tbl on nanny_tbl.position_pos=position_tbl.recid_pos and nanny_tbl.position2_nan=position_tbl.recid_pos
WHERE recid_nan = colname

The first left join woks 100%
the 2nd does not

nanny_tbl stucture is:

recid_pos --- this is primary key
position_pos ---- this is VarChar

Who can help me solve this?

One Table Left Joined To 2 Other Tables Using Different Columns
I have a Table Called MATCHES
I have a Table Called PEOPLE which has BOTH Customer and Dealer records in it

The MATCHES table has a DealerID and a CustomerID column (each "Matches" record is a link between a Customer and a Dealer from the People Table) (this of it as a table of "service calls" for a product ---- the customer who had the problem, and the dealer who fixed it)

I want a query to show me each Match Record along with all data related to the customer and dealer for that match record

Since a match record will always have a customer but sometimes will not yet have dealer assigned to it, I was trying to use left joins but I can't get it to work

Select * from Matches Left Join People as Customers on Matches.CustomerID=Customers.ID, Matches Left Join People as Dealers on Matches.DealerID=Dealers.ID

This of course, doesn't work, but how would I do this?

Multiplying And Summing
sql
SELECT customers.lawson, customers.name, sum(cart.quantity*item_sizes.cost) AS amt, item_sizes.size_id
FROM customers
LEFT JOIN cart ON cart.lawson=customers.lawson
LEFT JOIN item_sizes ON cart.size_id=item_sizes.size_id
WHERE customers.order_confirmed=1 AND item_sizes.cost IS NOT NULL
GROUP BY customers.lawson;

This query currently works in that it returns exactly what I expect. My concern is with sum(quantity*cost) - will it always return what I expect?

My understanding is that sum(), being an aggregate function, is executed after the rest of the query (including the multiplication of the two fields in each row) - is this correct? If it is, then this would return what I expect every time. If not, can someone help me make this return what I need?

This is for one of those projects that had been sitting on someone's desk for months and wasn't assigned to me until it was already overdue, so time is not on my side on this one...

Summing A Column?
PHP

session_start();ob_start();$item = $_GET['item'];$user = $_POST['username'];$pass = $_POST['password'];mysql_connect('localhost', 'mysqluser', 'mysqlpass');mysql_select_db("mysqldb");$result = mysql_query("SELECT * FROM users WHERE user = $user AND password = $pass");$row = mysql_fetch_row($result);if ($row <= 0) { header("Location:item.php?item=failed");} else { $_SESSION['login'] = 1; $_SESSION['user'] = $user; header("Location:item.php?item=$item");}ob_flush();

I think it should work, but it's repeatedly telling me Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /my/directory/html/item.php on line 10. I can't figure out for the life of me why it won't work. It connects properly, I've been doing SQL queries all over the site. The variables POST correctly, and the SQL variables are all correct as well.

Not Summing Correctly
This query will is not showing sums of the individual line items. The tabl shows stats for each player each day, yet even though the intent of the query is to show a sum of each player it is not.
Can anyone tell me what I'm doing wrong?

select distinct `ssfhl4_ssfhl`.`tblplayers`.`Player` AS `Player`,`ssfhl4_ssfhl`.`tblcummstats`.`Position` AS `Position`,`ssfhl4_ssfhl`.`tblcummstats`.`NHLTeam` AS `NHLTeam`,`ssfhl4_ssfhl`.`tblcummstats`.`Status` AS `Status`,`ssfhl4_ssfhl`.`tblplayers`.`Salary` AS `Salary`,`ssfhl4_ssfhl`.`tblcummstats`.`WeekNo` AS `WeekNo`,`ssfhl4_ssfhl`.`tblcummstats`.`FanTeam` AS `FanTeam`,`ssfhl4_ssfhl`.`tblcummstats`.`DateStamp` AS `DateStamp`,`ssfhl4_ssfhl`.`tblcummstats`.`PlayerID` AS `PlayerID`,`ssfhl4_ssfhl.........

Summing Time
With 'time' below defined as data type TIME, how can I convert the result from the follwing sum() into hours:minutes:seconds, or seconds?

I tried time_format but only get NULL... :-(

SELECT sum(time), time_format(sum(time),'%H:%i:%s') FROM test

Result:
sum(time) time_format(sum(time),'%H:%i:%s')
11861 NULL

1:18:61 is what I want, but even hour(sum(time)) does not work.

Summing Dates
I have a table that has two columns, starttime and endtime.Usng this query I can get the time durations between each start and endtime for each row:

SELECT TIMEDIFF(endtime, starttime) from playertabletime where playerid=37 and starttime > DATE_SUB(now(), INTERVAL 1 DAY);

Using this I can sum times together:

SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `a_time_value`) ) ) AS total_time FROM playertabletime;

But in ONE query I want sum all the timediff durations returned from the first select to give me one total duration.Any ideas?I have tried copying the first query into the second but this is not valid SQL.

Summing Fields AS
I have a table with 5 rows, and each row has some fields
I want to select the sum of some integer fields from each row as a new variable[], which I can manipulate later.

for example
SELECT sum(field1, field2, field3) AS new_variable FROM table ORDER BY new_variable DESC.

I know this is wrong, but I would like to make use of the new_variable array later.
In my table, each integer field name is a type of CD someone owns, and its value is the amount that they own.
So my aim is to produce a table of descending TOTAL number of CDs owned per person (row)

Summing Values
Is it possible to sum the values in a table that meet a specific piece of criteria and return only the total?

Specify Conditions For The Left Side Of A Multiple Left Join
I'm doing a left join that looks like this standard example:

SELECT t1.c1, t2.c2, SUM(t3.c3)
FROM t1
LEFT JOIN t2 ON t1.c1=t2.c1
LEFT JOIN t3 ON t1.c1=t3.c1
GROUP BY t1.c1

The problem is that I also want to specify a condition for selecting records from t1:

WHERE t1.c1='x'

so that only the records with that value in c1 will be returned on the left side of the join. I don't know where to put this in the SQL.

Selecting Records, Then Summing Parts Of A Column, Then Sorting By That Column...
Iâ€™m keeping track of baseball stats, and each row represents one line of stats (from a box score) for one player of a single game. Because of this, a single player may have multiple rows in the table. I want to cumulate each playerâ€™s stats (so theyâ€™ll be one row per player) and display as output, which isnâ€™t a problem. Then I want sort by a certain stat, but by now Iâ€™ve already looped through the table, so I canâ€™t sort using a mySQL query at this point. I tried first putting values into an array in a previous project, but that became extremely complicated. Whatâ€™s the best way to approach this?

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.

Outer Join With Where
I am willing to select all the distinct Products from the product table and in case the PC Name was the same as a @ passed PC name return ALSO the local path, Otherwise return null for the localpath field.... How I can do that?
here is my query:

SQL
SELECT Products.Name, Products.Code,
UserProduct.LocalPath, UserProduct.PC FROM
Products LEFT OUTER JOIN
UserProduct ON
UserProduct.ProdCode = Products.Code
this returns the same product name and product code from many users....also, I want to return distinct Product Names also.

Do I Need An Outer Join?
I have a table that stores traffic data keyed on a char (even though I call it a tstamp). The data is coalated into hourly chunks and is viewed through a web app in monthly, daily and hourly increments.

I've been charged with creating some KPI's (Key Performance Indicators) from this data and one of them is for a given month/day/year, generate an addition figure of how many times the average traffic per period (in a column called average) is less than or equal to 5.

mysql> desc coal_counts ....