I'm new to the list,though I've been using MySQL in relatively simple modes
for some time now. If you want to join three tables, t1, t2, t3. If you want
to
select from t1 where t1.c2 has a value in t2.c12 AND where t1.c3 has a value
in t3.c13, then first make sure that t1.c2 and t2.c12 have similar types (e.g.
both char(10)), then make sure that t1.c3 and t3.c13 have similar types, then
create four indexes (one each for t1.c2, t1.c3, t1.c12 and t1.c13). Then the
query should be something like
SELECT t1.* FROM t1,t2,t3 WHERE t1.c1 = t2.c12 AND t1.c3 = t3.c13;
You should also read up as much as you can on EXPLAIN, and you should
use EXPLAIN on any SELECT that might end up being really big.
HTH,
Lezz Giles
"Frederick L. Steinkopf" wrote:
> I can't comment on whatever Borland client you're talking about but MySQL
> supports multiple table joins. It's all in your sql coding. I built a
> yellow pages directory using MySQL that consists of several tables. One has
> 500,000 records another has 2,000,000 records, others are smaller having
> between 3 and 1000 records. Most calls involve joining both the two large
> tables and three or four of the smaller tables.
> Fred Steinkopf
> ----- Original Message -----
> From: "Erlend Stromsvik" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Monday, August 06, 2001 7:45 AM
> Subject: Re: Borland MySQL client
>
> > Hi
> >
> > I having a little trouble with mysql, with the lack of sub-selects.
> >
> > Yes, I have read about joins, both the manual and from the MySQL book by
> > Paul Dubois, but none of those provide any 'real' info about what to do.
> >
> > It's ok to do join on table1, table2 and table3 as long as they are
> limited
> > to 10 records each, as in all of the examples you find on the net. But
> what
> > about my tables. I have one with 1,14 million and one with 136.000 and a
> > third with 65.000 records. What I want to do is to check for a match
> between
> > a record from table1 in table2 and from table1 to table3
> >
> > ex. from table1 to 2:
> > select * from table1 where col1 in (select col2 from table2);
> >
> > If I where to do a join on those tables I would, to my knowledge, end up
> > with a temp table with 1,14 * 136.000 * 65.000 records.. And that's a HUGE
> > table. I'm actually not even going to try it, because I'm pretty sure it
> > won't work.
> >
> > The only solution I seem to find, is to include two new columns in table1,
> > where you give them either TRUE or FALSE based on the info put into table2
> > and table3.
> >
> > So whenever I do an insert in table2 or 3, I also update corresponding
> > record in Table1 with TRUE in either one of the two columns. <- Would
> this
> > be the 'right' way to do it? :)
> > This way I only have to retrieve the record from table1, check for TRUE
> > value in the two columns, if they are TRUE retrieve record from table2 and
> > from table3.
> >
> > Would be nice to hear from someone who manages good sized databases.
> >
> >
> > Regards
> > Erlend Stromsvik
> >
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Leslie Giles Software Release Manager Avici Systems Inc
[EMAIL PROTECTED] Phone: 978.964.2030 Fax: 978.964.2100
"Do not fear the merge"
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
