I need a single row from 2 different tables. It is rather trivial to
create a join that will join these two tables and give me all the
information I want in one query. It is also fairly easy to just execute
two different queries with out any join and get the data I need. Since
the both
Miguel Vaz wrote:
I have three tables:
TABLE Person
- id_person, name, id_levelA, id_sizeA, id_levelB, id_sizeB
TABLE Levels
- id, desc
TABLE Sizes
- id, desc
Hi! You can always join a table twice :)
SELECT
p.id_person
, lA.desc as levelA
, sA.desc as sizeA
I need a query that returns everything from the Person table,
replacing the id_levelA... with the desc from the tables Levels and Sizes.
I can get a result with one of them replaced using JOIN, but not
several replacements using the same reference tables (levels and sizes). :-P
Heres
On 3/21/06, Robert DiFalco wrote:
I apologize if this is a naive question but it appears through my
testing that a RIGHT JOIN may out perform an INNER JOIN in those cases
where they would produce identical result sets. i.e. there are no keys
in the left table that do not exist in the right
I apologize if this is a naive question but it appears through my
testing that a RIGHT JOIN may out perform an INNER JOIN in those cases
where they would produce identical result sets. i.e. there are no keys
in the left table that do not exist in the right table.
Is this true? If so
- Original Message -
From: Jochem van Dieten [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, March 21, 2006 3:53 AM
Subject: Re: RIGHT JOIN better than INNER JOIN?
On 3/21/06, Robert DiFalco wrote:
I apologize if this is a naive question but it appears through my
testing
Robert,
A RIGHT JOIN would potentially return more results than an INNER JOIN.
I do not see how it could be, in the absolute, be faster.
How large were the tables and how did you do your testing? What platform
did you use and were you InnoDB or MyISAM?
-Original Message-
From: Robert
Martjin,
Of course one should use the right JOIN for the job. But let me ask you,
which join would you use here?
You have a table called Descriptors, it has a field called nameID which
is a unique key that relates to a Names table made up of a unique
identity and a VARCHAR name. I think most
Robert,
Of course one should use the right JOIN for the job. But let me ask you,
which join would you use here?
You have a table called Descriptors, it has a field called nameID which
is a unique key that relates to a Names table made up of a unique
identity and a VARCHAR name. I think
) a RIGHT JOIN still provides a substantial performance
improvement over a FULL JOIN in this case, my customers would want me to
provide that rather than have me tell them it is an inappropriate join
or that I am asking the database server developers to improve their
query optimizer.
I wasn't really
(after buffer
tuning et al) a RIGHT JOIN still provides a substantial performance
improvement over a FULL JOIN in this case, my customers would want me to
provide that rather than have me tell them it is an inappropriate join
or that I am asking the database server developers to improve their
query
Robert,
Your restatement of your original question uses FULL JOIN as if it means
the same things as INNER JOIN: that's simply not correct. A full join
contains the results of an inner join PLUS the orphan rows from the
right-hand table in the join PLUS the orphan rows from the left-hand table
I apologize if this is a naive question but it appears through my
testing that a RIGHT JOIN may out perform an INNER JOIN in those cases
where they would produce identical result sets. i.e. there are no keys
in the left table that do not exist in the right table.
Is this true? If so
fashion
- etc. etc.
You simply can't make a categorical statement that a right join will perform
better than an inner join - or vice versa - in every case in DB2. All
versions of DB2 use a cost-based optimizer that makes great efforts to give
the optimum access path (and therefore optimum performance
I am using MySQL 5.0.15 on windows and cannot run this query:
SELECT a.*,b.name, c.fullname,d.fullname
FROM access_authorization a, building b
LEFT JOIN users c ON a.createdby=c.id
LEFT JOIN users d ON a.modifiedby=d.id
WHERE a.sortcode=b.sortcode AND
a.sortcode like '1,2,1,6%' LIMIT 0, 25
I
Ryan Allbaugh [EMAIL PROTECTED] wrote on 12/19/2005 04:41:39 PM:
I am using MySQL 5.0.15 on windows and cannot run this query:
SELECT a.*,b.name, c.fullname,d.fullname
FROM access_authorization a, building b
LEFT JOIN users c ON a.createdby=c.id
LEFT JOIN users d ON a.modifiedby=d.id
cat/dog/bird...
i've tried various derivatives of the following...
mysql select cattbl.name as cat,
- dogtbl.name as dog, birdtbl.name as bird
- from dogtbl
- left join cattbl on cattbl.id=dogtbl.catid
- from birdtbl
- left join dogtbl on birdtbl.dogid
to produce
the results set with the names of all three
cat/dog/bird...
i've tried various derivatives of the following...
mysql select cattbl.name as cat,
- dogtbl.name as dog, birdtbl.name as bird
- from dogtbl
- left join cattbl on
cattbl.id=dogtbl.catid - from
You only need to specify from tabel on the first table. Like this.
select
cattbl.name as cat, dogtbl.name as dog, birdtbl.name as bird
from dogtbl
left join cattbl on cattbl.id=dogtbl.catid
left join birdtbl on birdtbl.dogid=dogtbl.id;
-Eric
On Mon, 5 Jul 2004 09:28:02 -0700, bruce [EMAIL
Hi,
with the tables eg:
Product: id, product, cost
ProductTag: productId, TagId
Tag: id, tag
I have created the ability to selectively assign tags to products if
required via a many-to-many relationship.
Now I want to join the tables in MySQL so that I can see all products
and in the tag column
Matthew Shalorne wrote:
Hi,
with the tables eg:
Product: id, product, cost
ProductTag: productId, TagId
Tag: id, tag
I have created the ability to selectively assign tags to products if
required via a many-to-many relationship.
Now I want to join the tables in MySQL so that I can see all
,
for all Products and all Tags (having data all across where they match-up
and having nulls in the columns where they don't until the query engine
supports the FULL OUTER JOIN clause.
Now, there IS a work-around using a LEFT JOIN UNIONed to a RIGHT JOIN but
I don't know what version of MySQL you have
I am looking for an expert opinion on the speed difference between fetching
related data from 2 tables with a join and fetching them in to single
selects.
The scenario is kind of the following:
SELECT a , b, c FROM table1 WHERE a='x'; # gets b='y'
SELECT b, d , e, f FROM table2 WHERE b='y
On Fri, Nov 22, 2002 at 08:13:46PM -0500, Philip Mak wrote:
SELECT *
FROM boards
LEFT JOIN boardAdmins
ON boardAdmins.userId = #{userId}
AND boardAdmins.boardId = boards.id
LEFT JOIN boardMembers
ON boardMembers.userId = #{userId
sql,query
Which way is faster?
Way 1:
SELECT *
FROM users
LEFT JOIN boardAdmins ON boardAdmins.userId = users.id
LEFT JOIN boardMembers ON boardMembers.userId = users.id
WHERE id = 5;
Way 2:
SELECT * FROM users WHERE id = 5;
SELECT * FROM boardAdmins WHERE userId = 5;
SELECT * FROM
On Fri, Nov 22, 2002 at 06:20:14PM -0500, Philip Mak wrote:
sql,query
Why not just:
SELECT * FROM users, boardAdmins, boardMembers WHERE id = 5;
You're not really 'joining', since boardAdmins and boardMembers don't have the
structure JOINs are made for (it seems).
--
Michael T. Babcock
CTO,
have the structure JOINs are made for (it seems).
Oops! I was typing my example from memory, and did it wrong.
Sorry, it's supposed to be like this:
SELECT *
FROM boards
LEFT JOIN boardAdmins
ON boardAdmins.userId = #{userId}
AND boardAdmins.boardId = boards.id
LEFT
[EMAIL PROTECTED] writes:
Description:
It seems that that the use of LEFT JOIN when the joined table
uses multiple primary key conditions fails to include rows which
are clearly matching on those conditions.
How-To-Repeat:
mysql create table foo (fooID smallint
On 1-Dec-2001 16:49 Sinisa Milivojevic wrote:
|
| Thank you for the repeatable test case.
I've just tested this with 3.23.46 and it still fails.
regards.
--
-Dale
-
Before posting, please check:
As it is such a big query, I don't have time to look at it for you but it
may be easier if you convert the RIGHT JOINS to LEFT JOINS for starters.
The manual does recommend LEFT JOINS, mainly for portability.
First part would be something like
(leg_activity LEFT JOIN leg_comm_hist
Thanks! I was able to work on the query today, and manually assemble a query
that did the same thing. And thus, was a lot faster. The main contention
MySQL seems to have is the nested joins, such as when Access does this:
FROM (((leg_version_hist RIGHT JOIN (leg_comm_hist RIGHT JOIN
31 matches
Mail list logo
