Hi,

I have several tables linked in various ways so that an inner join is
possible. However, at the same time and in the same SQL query, I'd also
like to query by some field values in one of the tables.

Two quick questions:

a)Will MySQL allow joins that involve more than two tables (in my case,
perhaps as many as 5)?

b)Can limits on a key field be included in the join in the same SQL
statement as does the join, i.e. ... WHERE N>3 AND N<20 ... or something
like that.

Thanks, Dave.

-------
P.S.--It might seem that I'm a lazy bum and unwilling to just try it. My
situation is that I'm working on design documentation for the database and
working through in my head whether everything can be done in approximately
O(N) time. The book I have doesn't mention joins on more than two tables.
I will get the database set up and try it soon. Thanks for your
patience.

-----------------------------------
David T. Ashley (dta (AT) e3ft (DOT) com)
Thousand Feet Consulting, LLC


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

David T. Ashley wrote:

wrong.


SELECT p.name, c.name
FROM parent p
INNER JOIN child c ON c.parent_id=p.id AND c.age < 18
WHERE p.sex in ('male','unspecified');

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

"David T. Ashley" <dta (AT) e3ft (DOT) com> wrote on 07/02/2006 14:03:04:

Yes, you can do multi-way joins, and people often do. My biggest is 3-way,
but some people do at least 5-way. Beware that it is easy to specify
operations which will heavily load the system if you are not careful.

The constraints in the WHERE statement are *logically* and syntactically
done on the huge table produced by the joins. However, the MySQL optimiser
is not stupid and will perform the filter upstream of the JOIN where
possible. Some experimentation and use of the EXPLAIN statement may be
necessary to find the best ordering for queries.

Alec




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

On Tue, February 7, 2006 10:46 am, Alec.Cawley (AT) Quantel (DOT) Com wrote:
My thought process was that if all the fields involved--both in the JOIN
and limits on any fields--were key fields, then the operation should be
approximately O(log N).

But, now that I think about it:

a)A simple limit operation or search operation on a KEY field should be
O(log N) (i.e. doesn't MySQL build index tables or something on key fields
to get approximately that behavior?), BUT

b)I think you are right about the caution ... just thinking about it, it
isn't clear that a JOIN will be O(log N) when the only condition is
something like (table1.a = table2.b). It seems that a limiting condition
(sex=MALE or date>birthday1 and date<birthday2 or whatever) would have to
be applied first to a key field (by the optimizer?) or else the JOIN would
be something like O(N) or maybe even worse.

Thanks for pointing out the EXPLAIN keyword. That may be helpful.

I'd be curious on any perspective ... what computational complexity is an
inner join with no other conditions (i.e. the only condition is table1.a =
table2.b)? I'd guess it isn't O(log N).

Well that concludes my newbie questions. Thanks for the help. I'll now
begin using MySQL ...
-----------------------------------
David T. Ashley (dta (AT) e3ft (DOT) com)
Thousand Feet Consulting, LLC


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

At 3:46 pm +0000 7/2/06, Alec.Cawley (AT) Quantel (DOT) Com wrote:
My record is and 8-way join (7 tables, one twice). So there! ;-)

Yes indeed. You need to think carefully about what indices you need on your tables. As has already been said, EXPLAIN SELECT is your friend!

James Harvard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw