MYSQL :: LEFT OUTER Is Faster Than INNER Joins?

LEFT OUTER JOIN WITH INNER JOINS
I am a bit stuck on a LEFT OUTER JOIN. I can get the query to run OK when leaving needed data out of the query (other inner joins). Basically, this is an booking system for a tour company. Some people arrive by cruise ship while others do not. I need this query to gather the customer Booking info from multiple tables and the cruise ship information for those bookings that arrive by ship and return a null ship name for those that dont.

Each ship has a name and a scheduled arrival date. the shipSchedule table holds the data for the scheduled dates and references the ship name from another table(because the same ship comes into town on multiple dates). To eliminate blank entries in the booking table (for those customers not on a ship) there is another table that stores the shipSchedule_ID and the Booking_ID for Bookings that arrive by ship. So, this query needs to get "shipName" from "cruiseShips" then reference the "shipSchedule" table to get the "shipSchedule_ID" then reference the "shipSchedule_ID" in the "ArriveByShip" table to reference "ArriveByShip.Booking_ID" to "Bookings.Booking_ID" field on order to correctly display the ship name the person comes in on for their Booking (possible to have more than one booking)..

The query (this works....but is incomplete) ....

View Replies !

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

View Replies !

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

View Replies !

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

View Replies !

Outer Joins?
In my application this SQL:

SELECT Tags.postId, Tags.term as term FROM Tags
LEFT OUTER JOIN Posts ON Tags.postId=Posts.id
WHERE Tags.userId=30 AND Tags.term LIKE "label/%"
Produces 97 rows... but this:

SELECT * FROM (SELECT Tags.postId, Tags.term as term
FROM Tags LEFT OUTER JOIN Posts ON Tags.postId=Posts.id
WHERE Tags.userId=30 AND Tags.term LIKE "label/%") as allTags
LEFT OUTER JOIN Tags as newTags ON (newTags.term=allTags.term)
Produces 1935 rows.

I guess I don't understand the Left Outer Join well enough as I thought that it just appends the joining table to the table without adding new rows like some of the other JOIN types. So in the second example I've created a dynamic table aliased as allTags and I then wish to join the original Tags table to my dynamic table and then filter it.

If someone can let me know how to achieve this without adding the extra rows that'd be great!

BTW how does one add multiple OUTER JOINS that all are all based on the data from the first table? ie I don't want Table 3 to be using Table 2 data to make the join I just want it to be joining on Table 1 as well. Am confusing myself now....

View Replies !

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

View Replies !

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:

View Replies !

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

View Replies !

Left Outer Join
After the LEFT JOIN statement and the ON statement.

1 LEFT JOIN
2 ON
3 WHERE ? (You cant's seem to be able to use 'where') What can you use to specify or narrow your selection.

View Replies !

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

View Replies !

Left Outer Join Query
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.

View Replies !

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.

View Replies !

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?

View Replies !

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?

View Replies !

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.

View Replies !

Left Outer Join, Upgrade To Mysql 5.0.12
I have the following sql that used to work in an older version of mysql:

SELECT place.* , part.id, part.lastName, part.firstName, part.ssn, exit.*
FROM placements place
LEFT OUTER JOIN participants part ON part.id = place.participant_id
LEFT OUTER JOIN exit ON exit.id = place.exit_id

View Replies !

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:

View Replies !

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)

View Replies !

Left Outer Join And ORDER BY Question
This question is best understood when illustrated with an example:

SELECT dept_name AS dept,
TRIM(CONCAT(fname,' ',lname)) AS name,
title,
number,
comments
FROM emp
LEFT OUTER JOIN dept
ON dept.dept_id = emp.dept_id
WHERE 1=1
ORDER BY dept ASC,
emp_order ASC
Note that the last condition of the ORDER BY clause references a field that is not selected in this SELECT statement. This works perfectly in MySQL, but I'm wondering if this is consistent with standard SQL? And, whether it's standard SQL or not, is this considered good practice? I have no use for emp_order except for determining the order of rows returned, so it just doesn't make sense to SELECT it, but at the same time it seems odd to reference a field I'm not SELECTing.

View Replies !

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?

View Replies !

Is Left Join And Left Outer Join Are Equal?
I just want to inquire that In Mysql,

Is left join and left outer join are equal.

View Replies !

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.

View Replies !

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?

View Replies !

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

View Replies !

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.

View Replies !

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?

View Replies !

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?

View Replies !

Alias Same Column Name Twice With LEFT JOINS?
My current code is

SELECT * FROM error_reports
LEFT JOIN nodes AS nodes1 ON nodes1.node_id = error_reports.node_error_label_id
LEFT JOIN nodes AS nodes2 ON nodes2.node_id = error_reports.node_error_status_id

The db structure is as follows

error_reports table
error_id - node_error_label_id - node_error_status_id

nodes table
node_id - node_label

Which gets me the correct data. The problem is when trying to echo out the data with PHP I have to use the column number and not the label because I get two columns with the same name, node_label from the nodes1 and nodes2 aliased tables. Is there a way to alias these two columns in the query so they are different?

View Replies !

Left Joins Galore, And Then Grouping?
So basically I have some forum software that I'm working on, and as I'm sure you're familiar, a forum database is essentially just a tree of 1-to-many over and over again. As far as I'm aware, the best way to approach a tree of limited depth (aside from using recursion) is using Left Join over and over again.

The Desired Result and Layout:

This is for the main page, where it displays all the categories, boards, and how many threads/posts are in each one. Each field is prefixed by the first letter of its table (e.g c,b,t,and p respectively). I can get all the categories and boards just fine, but I can't get the number of threads or posts. I approached those with a Group By clause, and then count(), but it only displays the first board and category, rather than all the boards and categories.

The Broken Query:....

View Replies !

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?

View Replies !

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

View Replies !

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.

View Replies !

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

View Replies !

How To Convert SELECT Query (with Left Joins) Into UPDATE ?
SELECT t0.type, t0.datekey, t1.datekey as prev_seen
FROM t_detail t0
LEFT JOIN t_detail t1 ON t0.type = t1.type AND t0.count-1 = t1.count
order by t0.type, t0.datekey ASC

It creates a table with the 'most recent previously seen' date for each date, count is already used to number the 'types'.
I used it to create a new_table - OK and added an auto increment id - OK.

Now I want to modify the select query to update new_table (which is a complete refresh of its contents, that's ok) how to do with the left join?

View Replies !

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.

View Replies !

Faster To Use C DBI
How does C,PHP and Perl DBI compare in speed with MySQL?

View Replies !

Faster To Use
is it faster to use the field names of a table or * to select all the fields. I know its not good to use * if you dont need all the fields but does it make any difference if you need them all?

View Replies !

What Would Be Faster
// OPTION 1:

SELECT id, title, type, source
FROM ads_banners
WHERE live = 1
AND ads_zones_id = 1
AND id >= (SELECT FLOOR(MAX(id) * RAND())
FROM ads_banners
WHERE live = 1
AND ads_zones_id = 1)
ORDER BY id
LIMIT 1

// OPTION 2:

SELECT id, title, type, source
FROM ads_banners
WHERE live = 1
AND ads_zones_id = 1
ORDER BY RAND()
LIMIT 1

View Replies !

Which One Is Faster?
is there any difference in speed between next two types of mysql queries?
1. insert into myTable set...
2. or insert into myTable (fields) values (values)
point is that i have to insert a lot of rows into one table, and i wonder which sql to use.

View Replies !

Achieving Faster
mysql 4.0.22 (win32)
Can anyone recommend best practices for the fastest way to remove large
numbers of rows at once?
I am diving my deletes into chunks (1000 rows at a time) but this still is
very slow.

View Replies !

Faster Way To Calculate
I'm looking for a faster way to calculate a 5-day moving average in a MySQL table. I have a database of stock prices for various different ticker symbols. I have a column that I use to store the 5-day moving average calculation, which is just the average stock price over the last 5 days.

What I am presently doing is querying the entire table, and sorting by ticker (ascending), then date (ascending). Then I am simply obtaining a row of data at a time and writing the calculated moving average back into the table for that particular row. Since each row has a unique row number associated with it, I can easily write a particular value to any specific row within the table.

View Replies !

What's Faster With LIKE &amp; CONCAT?
I have a quick question about searching with LIKE.

I'm using InnoDB type tables because I will be doing a LOT of inserts in relation to selects. (Lots of storage, seldom selecting to view.)

I need to search 4 different columns as a part of a search query. However, 3 of these columns are very short--VARCHAR(150), VARCHAR(63), VARCHAR(10)--and one column of VARCHAR(4000).

Currently I have ....

View Replies !

Is MATCH AGAINST Really Faster Than LIKE?
I'm currently using MySQL 4.1 and performing keyword searches on multiple columns. The original expression looks like: ....

View Replies !

Faster Queries...
If my database table was extremely large, would I benefit from breaking it down into multiple databases or tables to achieve faster query results?

View Replies !

Are Transactions Faster?
I know that database engines that support transactions tend to be slower than engines without support for transactions (e.g. innodb and myisam).

But are transactions faster than separate queries?

Example:

I need to run 15 edit queries.

Will it be faster if I just run them one by one or if I wrap them inside a transaction? My understanding of transactions is that the database gets queried only once with all queries inside the transaction and then it runs it one by one. So it should be faster or no?

View Replies !

Faster RAND() Query
I need to select from my db table ONE (1) random row, just one. So, I am doing this:

SELECT categories.cat_name, subcategories.sub_name, map.faq
FROM categories, subcategories, map
WHERE categories.cat_id=map.cat AND subcategories.sub_id=map.sub
ORDER BY RAND() LIMIT 1"

categories=10 rows
subcategories=20 rows
map=100 rows

View Replies !