Slow Execution For A Left Outer Join Query

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

---

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 Left Join
I've never come across this before, a very straight ahead query running extremely slowly...

Table1
Id | Field1 | Field2

Table2
Id | Table1Id | Field1 | Field2

Query....

SQL
SELECT Table1. * FROM Table1 LEFT JOIN Table2 ON Table2.Table1Id = Table1.IdWHERE Table2.Table1Id IS NULL

NOTE: An existing record in Table2.Table1Id is never null, it will always have a record of some description. We're just looking for missing records in Table2.

This query is taking anywhere from 6-20 seconds to run. It's really got me baffled as I thought it should run extremely quickly.

There's about 10,000 records in each table and the query appears to be producing the correct result, just extremely slowly.

---

Slow Left Join
Well I'm trying to do a left join on a couple tables "USERS" and "EVENT_LOG", event_log has a considerable number of rows (1.7mil). I'm trying to do a query to find out which USERS have no entries in the EVENT_LOG of a specific EVENT_TYPE.

Here's the query I'm attempting:

select USERS.USER_USERNAME, 0 as eventCount from USERS left join event_log on USERS.USER_ID=event_log.USER_ID and EVENT_TYPE='AUTHENTICATION' where event_log.USER_ID is null and USERS.CUSTOMER_ID=33700077 group by USERS.USER_ID

EXPLAIN output:
SIMPLE USERS ref CUSTOMER_ID CUSTOMER_ID 4 const 155 Using where; Using temporary; Using filesort
SIMPLE event_log ref USER_ID USER_ID 32 const 12139 Using where; Using index; Not exists


This takes 1.5sec, which is quite long considering the right join takes 31ms which is doing alot more (i.e. counting).

Right Join:

select USERS.USER_USERNAME, count(event_type) as eventCount from USERS right join event_log on USERS.USER_ID=event_log.USER_ID and EVENT_TYPE='AUTHENTICATION' where USERS.CUSTOMER_ID=33700077 group by USERS.USER_ID

I have indexes on USER_ID in the USERS table, and USER_ID/EVENT_TYPE ("USER_ID" index) in the EVENT_LOG table.

So anyone have an idea as to why this is so slow? Shouldn't it be querying the "USER_ID" index in the EVENT_LOG table and simply requesting the event type "AUTHENTICATION" and adding it to the resultset if it comes back null?

---

Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below:

It involves two tables, products and vendors.

Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description:

CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`vendor_id` smallint(6) NOT NULL default &#390;',
`product_code` varchar(255) NOT NULL default '',
`internal_name` varchar(255) NOT NULL default '',
`lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),
UNIQUE KEY `product_code` (`product_code`),
KEY `vendor_id` (`vendor_id`)
) ENGINE=MyISAM;
Vendors are much more straightforward:



CREATE TABLE `vendors` (
`id` smallint(6) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
The following query executes in no MORE than 0.01 seconds:


SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
FROM products as p
ORDER BY p.product_code ASC
LIMIT 0, 30;
And has the following attributes:

+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds:



SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
, v.name as vendor_name
FROM products as p
LEFT JOIN vendors as v ON v.id=p.vendor_id
ORDER BY p.product_code ASC
LIMIT 0, 30;
It has the following characteristics:

+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me.

Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so:



mysql> explain SELECT DISTINCT p.id
-> , p.product_code
-> , unix_timestamp(p.lastmodified) as lastmodified
-> , p.internal_name
-> , v.name as vendor_name
-> FROM products as p
-> LEFT JOIN vendors as v ON v.id=p.vendor_id
-> LIMIT 0,30;
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,

---

Left Join Query
I have this query

SELECT user.user_id, email, password, full_name, company_name,
first_login_date, last_login_date, first_draft_date, last_draft_date,
first_submit_date, last_submit_date, rowc.*
FROM user LEFT JOIN rowc ON rowc.user_id = user.user_id
WHERE user.user_type = 'USER' ORDER BY user.email

Which returns all records in user table plus rowc records if there are matches in the user id.

but user.user_id is null when there are no matching recordings in the rowc.
Isn't left join meant to pull all the records in the user table? What am I doing that is wrong here?

---

Query Trouble With LEFT JOIN
My query as it echoes out in PHP...

SELECT sales_reps.sr_id,
sales_reps.order_id,
sales_reps.name,
sales_reps.job_number,
UNIX_TIMESTAMP(shop_orders.date) as date,
sales_reps.stage,
sales_reps.status, ....

---

Having Troubles With A LEFT JOIN Query
I'm currently building a private members area for a website where designs can be submitted and rated before they are released to the public under one unified "pack".

What I am trying to do is to create a query that lists the latest submissions along with their ratings (if they have been rated) and if the user has rated that submission or not. Here is my query:

SELECT s.id as id,
s.title as title,
s.users as creators,
ROUND(AVG(r.rating), 1) as rating,
r2.id as userhasrated
FROM submissions AS s
LEFT JOIN sub_ratings AS r
ON r.subid = s.id
LEFT JOIN sub_ratings AS r2
ON r2.userid = ".$user->getUserId()." AND r2.subid = s.id
LEFT JOIN packs AS p
ON p.id = s.pack AND p.released = 0
WHERE s.state > -1
GROUP BY
r.subid
ORDER BY s.id DESC
LIMIT 10
The problem is that it only returns two rows: one with some submission that hasn't been rated and one with some submission that has been rated (but only rated by the user indicated by $user->getUserId()).

How can I fix my code so that it returns as many rows as I desire?

---

Problem In Left Join Query?
I have a pretty complex query and it's practically crashing my system due to generating more data than I am looking for.

I basically have 2 tables. They should contain very similar data as they contain information about the same types of things. I was asked to join the 2 tables, getting any information from table 1 that does not exist in table 2, but keep everything of table 2. Anyway I tried this query and it just hangs forever. One problem could be because I have no ID field to match up between the tables. I'm trying to join them based on an address, city, and zip.

Select * INTO OUTFILE 'output.tab' FIELDS TERMINATED BY ' ' LINES TERMINATED BY '
'
FROM table2 LEFT JOIN table1 ON
table2.address = table1.address and
table2.city = table1.city and
LEFT(table2.zip,5) = table1.zip;
My resulting file should be 13000 records in length, with 3000 records that have additional information based on table1 data.

---

DELETE Query With Left Join?
I want to delete items from a table that are determined with a left join.

using

DELETE ( SELECT * FROM item_colors LEFT JOIN items ON item_colors.item_id =
items.item_id WHERE items.group_id =3 ) FROM item_colors

does not work.

how would be the right syntax?

The SELECT part returns the items I want to delete from item_colors
correctly.

---

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?

---

Unknown Column Error With Left Join Query
I can't get my left join operation working whenever my on clause includes a comparison to another table's column. I keep getting errors like this:
#1054 - Unknown column 'pr.risk' in 'on clause'

The query I am trying to execute is shown below. All was working well until I put that top join in. And yes there are risk and issue columns in the pr table....

---

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

---

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.

---

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 SELF Join -- LEFT Join On Same Table
Is it possible to LEFT JOIN a table to itself?

I want to see all records in a table where the year of the sales date is
2003 and where the salesman sold an item to a customer to which he did not
sell that item to in 2002.

The following is an example table (SALES):

+----+-----+------+------------+--------+------+-----+
| ID | SLM | CUST | DATE | ITEM | COST | QTY |
+----+-----+------+------------+--------+------+-----+
| 1 | 1 | AAA | 2002-01-01 | APPLE | 1.00 | 2 |
| 2 | 1 | BBB | 2002-01-01 | APPLE | 1.00 | 2 |
| 3 | 1 | CCC | 2002-01-01 | PEAR | 1.00 | 2 |
| 4 | 1 | AAA | 2002-01-01 | PEAR | 1.00 | 2 |
| 5 | 2 | AAA | 2002-01-01 | APPLE | 1.00 | 2 |
| 6 | 3 | CCC | 2002-01-01 | BANANA | 1.00 | 2 |
| 7 | 1 | AAA | 2003-01-01 | APPLE | 1.00 | 2 |
| 8 | 2 | AAA | 2003-01-01 | APPLE | 1.00 | 2 |
| 9 | 3 | CCC | 2003-01-01 | BANANA | 1.00 | 2 |
| 10 | 1 | AAA | 2003-01-01 | BANANA | 1.00 | 2 |
| 11 | 3 | CCC | 2003-01-01 | APPLE | 1.00 | 2 |
+----+-----+------+------------+--------+------+-----+

What I want to see in my result is that in 2003 Salesman 1 sold bananas to
customer AAA and salesman 3 sold apples to customer CCC. I want to see
these because these salesmen did not sell these customers these items in 2002.

---

LEFT SELF Join -- LEFT Join On Same Table
Is it possible to LEFT JOIN a table to itself?

I want to see all records in a table where the year of the sales date is
2003 and where the salesman sold an item to a customer to which he did not
sell that item to in 2002. Code:

---

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 ?

---

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 .

---

Outer Join And Distinct
I just started building a website and use MySQL as my backend database. I'm having a little trouble creating an SQL query that will get the correct information out.

Here's my problem:

I have 3 tables:
people: stores the name of people who can vote
votetype: stores the type of votes there are (In favor, Against, Withheld)
decisions: the dicisions that were voted on

Then I have another table that links them all together called votes. Votes has three columns: personID, typeID and decisionID. personID and decisionID are primary key. All are foreign key to obvious tables.

I want to retrieve for a given person the votes he made for all decisions. So: a list of all decisions with the vote he or she made and a NULL if there is no vote.

So far I have this query:
SELECT DISTINCT d.ID, d.name, d.date, v.typeID AS vID, t.name FROM decisions d, votetype t LEFT OUTER JOIN votes v ON v.decisionID = d.ID WHERE (v.personID=1 OR v.personID IS NULL) AND (v.typeID=t.ID OR v.typeID IS NULL) ORDER BY d.date;

For some reason the DISTINCT doesn't work and I get a decision that is not voted for three times (once for each type while v.typeID column is NULL).

How can I fix this so each decision only shows up once?

Jurgen

-------------------------
I've found a solution:

SELECT DISTINCT d.ID, d.name, d.date, v.typeID AS vID, IF(v.typeID IS NULL,NULL,t.name) AS stem FROM decision d, votetype t LEFT OUTER JOIN votes v ON v.decisionID = d.ID WHERE (v.personID=1 OR v.personID IS NULL) AND (v.typeID=t.ID OR v.typeID IS NULL) ORDER BY d.date;

---

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 ?

---

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`=&#391;'
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` = &#394;'
AND shoppingCart.sessionID = '{$sessionID}'
AND `Active` = &#391;'
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)!

---

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?

---

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)

---

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

---

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.

---

How To Get Time Execution Of A Query?
Do you know how to get the time that a query executes? Is there a formula on how to get it?

Example:

Note: I have 5000 data in the table cars

$result = mysql_query("Select * from cars");

Question: How to get time of executing the query above?

---

How To Get Query Execution Time
How to get query execution time in "mysqlquery.log" files

I am using:

Linux 7.3
mysql 4.1

How to display the query execution time for each and every query in log files.
i have configured "slow-log-query" and "mysqlquery.log" in "my.cnf" is pasted below.

log = /var/lib/mysql/mysqlquery.log
log-slow-queries = /var/lib/mysql/slowquery.log
long-query-time = 0
slow-launch-time = 1

then i can get a output in "mysqlquery.log" like,

070830 9:24:29 2 Connect root@localhost on
2 Init DB jbdatabase
2 Query select count(*) from vacancy1_table
2 Query select category,listcategory from category_t
able order by category
2 Quit


but i need to display the "timetaken of each end every executed query" along with the query in the above logfile like,

E.g:select category,listcategory from category_table order by category(0.03 secs).

---

Query Execution Time
I am using mysql for my shopping site,Performance of site very slow then I used mysql_stat php function to find out howmany slow queries,it shows 20.

Now my question is any function in mysql(or)PHP to show 20 slow queries.

---

Last Query Execution Time
Is there a way to retreive the elapsed time for the prevously executed query?

Alternatively, Is there a way to query the current time in fractions of a second?

I am attempting to use a stored procedure to execute and track the time it takes to run some queries and other commands.

The logic of the stored proc would go something like this.....

---

Query Execution Time
I am interested in displaying the query execution time as mysql does from the console, but using php. I've looked everywhere for this and can't find anything. Does anyone know how to display this information to the browser via php? The information must be there since the console provides it.

I checked the php function mysql_info(), but that only shows records, duplicates, deleted, matches, changed, warnings, etc. Didnt see any specific php function for getting execution time.

---

Query Total Execution Time
How to get total time taken by a query to execute....

---