MYSQL :: Outer Join And Distinct

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 ....

Outer Join
hello, I am trying to run following query in mysql.

select a.name, a.record, b.data from A a, B b where a.name*=b.name and b.date='2006-07-28'

How can I do this in mysql? I looked up LEFT JOIN but still can't figure it out.

OUTER JOIN
I have 2 tables with the corresponding columns:

1) InventoryComputers-ComputerID, ComputerSN.
2) InventoryStations-Autonum, BranchID, StationID, ComputerID.

I want to display all rows of InventoryComputers for which the ComputerID does not have an entry in InventoryStations.
So basically, I want to display those computers that do not have a station assigned to them.

I tried this sql statement, but it also displays those rows that are present in InventoryStations table, which I don't want.

SELECT * FROM InventoryComputers
LEFT OUTER JOIN InventoryStations on InventoryStations.ComputerID=InventoryComputers.ComputerID
ORDER BY InventoryComputers.ComputerID

Any ideas what is wrong??

Outer Join
i have a main table called property, and a lookup table called ptype (property type). the propertry table has a "type" field, and a "subtype" field. both these fields look at the ptype table to get their titles.

so, i currently have the query below, but i also want to add to this the subtype. trouble is the field name is going to be the same for both the type and subtype values, so how do i get at them both?

I tried "AS subtype" after the LEFT OUTER JOIN but this didnt work.

SELECT * FROM property
LEFT OUTER JOIN ptype ON ptype.pty_id = property.pro_ptype
WHERE property.pro_id = 1

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.

Three Table Outer Join
I have three tables:

items (id, article, etc)
deliveries (id, shipment_date, etc)
movements (item_id, delivery_id, quantity, etc)

I want to get result as following:
article | sum(quantity)

but for all articles (outer join) with condition for deliveries table(shipment_date >= '2004-08-01' for example)

It works for two table join (items and movements) without deliveries:
select i.id, sum(m.quantity) from
items i left outer join movements m on m.item_id = i.id

but how I should join "deliveries" table to add condition for shipment_date ?

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.

Iso FULL OUTER JOIN
I need to do a full outer join, but have not seen this available in the fine manual. Is this type of join available?

The task is to list everyone that has departed the company in a certain date range. Sometimes a record is entered on table A, sometimes on table B, sometimes on both.

SELECT a.NAME, b.NAME FROM TABLEA A FULL OUTER JOIN TABLEB B ON A.SSN = B.SSN WHERE A.DEPART_DATE BETWEEN @BEGIN AND @END or
B.DEPART_DATE BETWEEN @BEGIN AND @END .

A Simple Outer Join
Sorry to post a naive question , But i need a query for this simple condition.
There are 2 tables employee and email . both have employee id as PK. I need to list employee names and emails . If enrty does not exist in email ..then it should get NULL .
Select employee.name, email.id from employee , email where ?

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 & ";"

Outer Join Syntax Error
Trying to do an outer join to retrieve info from two data bases and keep coming up with syntax errors....here's the code

$result = @mysql_query('SELECT * FROM members OUTER JOIN 2nd_location USING (members.ID = 2nd_location.ID) WHERE members.type= "RET" AND members.active="A" AND members.multi_location="Y" ');

OUTER JOIN With Extra Conditions?
I need to get a list of products in a certain category, along with the quantity of each item already added to the shopping cart for a given session number. I'm having a heck of a time satisfying the latter condition.

shoppingCart table:
+--------+-----------+-----------+-----+
| cartID | sessionID | productID | qty |
+--------+-----------+-----------+-----+
product table:
+-----------+--------------+------+--------+
| ProductID | ProductCatID | name | Active |
+-----------+--------------+------+--------+
Here's my base query, which just gets all the products in a given category.

SELECT product.*
FROM product
WHERE `ProductCatID`='{$id}'
AND `Active`=Ƈ'
Here's the query I've got so far:

SELECT product.*, shoppingCart.qty
FROM product
LEFT OUTER JOIN shoppingCart ON shoppingCart.productID = product.ProductID
WHERE `ProductCatID` = Ɗ'
AND shoppingCart.sessionID = '{$sessionID}'
AND `Active` = Ƈ'
Obviously, this does NOT work, because it limits the query to ONLY products that have the specified sessionID. I need it to return ALL the products in the category, but give me the quanity for items in the shoppingCart table, ONLY IF the sessionID matches (otherwise it should return NULL)!

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?

Transform SubSelect In OUTER JOIN
maybe I'm simply to dump but I could not transform this SQL-Statment
which uses a Sub-select and create on that uses an OUTER JOIN ....

Double OUTER JOIN On The Same Table?
I'm trying to make a request with an OUTER JOIN on 2 fields at the same time:

Here are the tables:

test:
- id
- user1
- user2

users:
- id
- username

And Id like to get as result each line of the 'test' table, with the corresponding username from 'user' fir the fields user1 and user2.

Do you know how to do this without using several SELECT?

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?

Full Outer Join Should Work
why doesn't the following work?

mysql> select * from girls full outer join boys where girls.city = boys.city;

where :

drop table girls;
drop table boys;
create table girls (name varchar(12), city varchar(12));
create table boys (name varchar(12), city varchar(12));
insert into girls values('Mary', 'Boston');
insert into girls values('Nancy', null);
insert into girls values('Susan', 'Chicago');
insert into girls values('Betty', 'Chicago');
insert into girls values('Anne', 'Denver');
insert into boys values('John', 'Boston');
insert into boys values('Henry', 'Boston');
insert into boys values('George', null);
insert into boys values('Sam', 'Chicago');
insert into boys values('James', 'Dallas');

If needed, I can go into insert and create for you!

BTW left and right work OK!

BTW what do P1(T1,T2), P1(T1,T2) and R( mean in the following

SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)
WHERE P(T1,T2) AND R(T2)

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)

Workaround For Outer Join Using Filtering Subquery
I'm using mysql 4.0.x, which does not allow the following query (which works
fine in 4.1). Is there any way I can issue a single query to achieve the
same results? (I want a list of all records from table al, nulled where
there is no match in table alm, which has been filtered. Without a
subquery, the filtering occurs after the outer join, and for one specific
row from alm, I only see the nonmatching records from al where NO other alm
record matches.)

SELECT al.id, alm.idmember, al.listname
FROM addresslists AS al
LEFT OUTER JOIN
(SELECT idlist, idmember FROM addresslistmembers WHERE idmember = 4)
AS alm
ON al.id = alm.idlist

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?

Selecting Distinct Results In A Join
SELECT p.post_title
, p.post_name
, c.cat_name
, DATE_FORMAT(p.post_date, '%M %D, %Y') AS dateadded
FROM wp_posts AS p
LEFT JOIN wp_post2cat AS pc ON p.ID = pc.post_id
LEFT JOIN wp_categories AS c ON pc.category_id = c.cat_ID
WHERE MATCH(p.post_content, p.post_title)
AGAINST(TRIM('merchant') IN BOOLEAN MODE)
ORDER BY p.post_date DESC
I am doing a fulltext search on a Wordpress database. Some posts are in multiple categories. When using the above query I will get a result for every category a post is in. I don't want that. I have tried using distinct with no effect. The wp_categories has a row for each category a post is in and how I am joining it seems to be the culprit.

Distinct And Join Query In MySQL
I have a query question, it may be really easy but somehow I cannot get it

What I have is a table with columns, two of which I will name Item1 and Item2,

I would like to get a distict set of pairs Item1-Item2 from this table, more specifically if table looks like this ....

DISTINCT With Multiple Tables And INNER JOIN ?
My query works OK this way, but I need every "kohde_name" only once
(preferred the earliest "hav_date").

I've tried with "DISTINCT kohde_name" in many ways, but always end up with
syntax error...:(
Is there any solution with DISTINCT, or do I need anotjer solution?

my (simplified) query:
SELECT henk_name,henk_number, kohde_name
,kohde_txt,havainto_id,hav_kohdetxt, hav_date
FROM ((henkilo INNER JOIN havaitsija ON hja_henkilo_id=henkilo_id)
INNER JOIN havainto ON hja_havainto_id=havainto_id)
INNER JOIN kohde ON hav_kohde_id=kohde_id
WHERE ...

Join 3 Tables - Distinct Results
I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacturers have reported in the given month.

Getting Two Distinct Fields From One Tbl From A Join Query Of Two Tables
Im trying to get distinct (the same row returned only once) user_id and
forename from a tbl of users (they are always distinct) where user_id
in that tbl equals friend_id in another table friend_id, which 'should'
be unique ie there 'should' only be one row of a 'friend connection',
but in the case of bugs etc this might not be true, what i want to do
is select only the row of tblusers with the user details that are
matched by join condition below, but only ONCE for each, ie not in as
many times as there are the same connection in tblfriends.

For some reason the result i am getting is a repitition of the 2 fields
in tblusers (that i am selecting) for as many rows as there are in
tblfriends that match the join condition.

How can i make it only return a row once?

Java Code Or Pseudo Code For "Outer Join"
I want to implement the "outer join" functionality in Java. Can
somebody explain the pseudo code for the same. OR what needs to be done
to extend the hash-join Java code of equijoin.

SELECT DISTINCT, (and Display Other Fields Not Distinct.)
I am using SELECT DISTINCT to select 1 of a duplicted field. So far I have;

SELECT DISTINCT `field1`, `field2`, `field3` FROM table1

This returns what I need. There is also another field (field4) which I also want to select, but not distinctly.

Something like: SELECT DISTINCT `field1`, `field2`, `field3` NOT_DISTINCT `field4` FROM `table1`

The field that is not being selected distinctly contains a '1' or a '0'. My table is ordered by field4 (0 first) does this mean the select distinct will select those with '0' before those with '1' (I want '0' to have priority when select distinct)

I will only be using this SQL query once to remove duplicates from a database, I am not concerned about performance issues which someone has mentioned to me.

How can I display this not-wanted-distinct field in a distinct query?

Select Distinct And Include Non-distinct Columns
I have a publication table that tracks the products assigned to various publications.

I want to select all of the distinct products, based on product_ID, assigned to a specific publication but I also want to return additional columns that do not need to be distinct.

If I use the following select:

Select distinct publication.product_ID, publication_ID.code, publication.region from publication where publication_ID = '12'

I would get a list showing the three columns that I want to see which are product_ID, publication_ID and region for publication 12 but I get too many items as the distinct function means that all three of these columns must be distinct and I only need the product_ID to be distinct.

So how do I find all of the distinct product_ID but also show other columns such as region?

Do I need to do this with some kind of self-join?

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

Summing Two Columns From Two Left Outer Joins
I've got data from three tables. Two of them have a column that needs to be summed and displayed with the rows of the remaining table. So the raw joins of c3 and c4 from the two tables onto c1 and c2 from the third table look like this:

id c1 c2 c3 c4
1 A B 7 3
1 A B 7 8
1 A B 2 3
1 A B 2 8
And I want

id c1 c2 c3 c4
1 A B 9 11
The group by clause was working beautifully and I didn't have a problem until I threw another table into the mix (the data from c4). Now it's just summing up everything in c3 and c4 from the raw table... not particularly surprising but I'm not sure how to get around this problem

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

LEFT JOIN? RIGHT JOIN? Multiple JOIN?
Simplifying this down to its basics, I'm using LEFT JOIN in a query but I'm not getting the results I want.

The tables are:
table services
service_id
service_name

table services_provided
service_id
service_date (date field)
cust_id
service_quantity

I need to select ALL services from the services table, and the number of services provided (by a specific customer, in a specific time frame) from the services_provided table, so that I can generate a list that shows services provided by that customer in the specified period of time

The query:

SELECT service_date, service_name, service_quantity
FROM services
LEFT JOIN services_provided ON services_provided.service_id = services.service_id
WHERE cust_id = $cust_id
AND MONTH(service_date) = 10
AND YEAR(service_date) = 2007
GROUP BY service_id
ORDER BY service_id
(Aside: The date to be selected varies - it may be the whole year, or may be a selection of months,such as 1, 2 or 3. This is determined dynamically in the script. The cust_id is determined by which customer is logged in.)

I'm pretty sure that the left join as I have it should return all services, even if there's no corresponding entry in the services_provided table.

But because of the WHERE clause, I don't get a complete list of all services -- if the customer doesn't have any entries for a particular service, that service doesn't come up in my results.

Do I need to change how I'm joining the tables, or join them twice? I'm sure I could do this with a nested query, but I'm trying to avoid that.

Join Vs. Inner Join Vs. Implied Join = Different Results ??
I SUM() only on the order table in all queries below. Here's a set of queries that I thought would/should yield the exact same results:

QUERY 1:
SELECT COUNT( o.orderID )
FROM order o
WHERE DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 161

QUERY 2:
SELECT COUNT( o.orderID )
FROM order o
LEFT OUTER JOIN credit_card cc ON o.orderID = cc.orderID
WHERE DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 175

QUERY 3:
SELECT COUNT( o.orderID )
FROM order o, credit_card cc
WHERE o.orderID = cc.orderID
AND DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 157

Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables:

Week (with columns day and hour)
Activity (with columns day, hour, activityid and ac_text)
Person (with columns name and activityid)

I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement:
Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour

I can then make a loop (I am usin asp.net) that writes the activities.
My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid).

I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.