From: Graeme B. Davis
Date: February 29 2000 7:02pm
Subject: Re: LEFT JOIN/JOIN question
List-Archive: http://lists.mysql.com/mysql/29505
Message-Id: <010d01bf82e7$8f27a6e0$b16a2799@tows517>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

but the results shouldn't take that long... the number of records matching
the WHERE clause in "mails" is about 30, and all I want to do is match up
r.ticket=m.ticketnum  even if r.ticket is null....(and still end up with 30
rows)

r has about 300000 rows, so I'm thinking my query is taking a long time
because of something I did in the query... do you see any problems?

ideas?

thanks!

graeme

----- Original Message -----
From: <sinisa@stripped>
To: <GraemeD@stripped>
Cc: <mysql@stripped>
Sent: Tuesday, February 29, 2000 12:49 PM
Subject: Re: LEFT JOIN/JOIN question


> Graeme B. Davis writes:
>  > I have a straight join that works:
>  >
>  > SELECT m.sbj, r.assignedto FROM mails AS m, remedy.remedy AS r WHERE
>  > (m.type='' OR m.type IS NULL) AND m.ticketnum=r.ticket ORDER BY m.date
DESC;
>  >
>  > But I can't turn this into a LEFT JOIN for some reason.
>  >
>  > Is this how I would do it? :
>  >
>  > SELECT m.sbj, r.assignedto FROM mails AS m, remedy.remedy AS r LEFT
JOIN
>  > remedy.remedy ON r.ticket=m.ticketnum WHERE (m.type='' OR m.type IS
NULL)
>  > AND m.ticketnum=r.ticket ORDER BY m.date DESC;
>  >
>  > (for this particular query, I get:
>  > ERROR 1114: The table 'SQL3f_0' is full
>  > )
>  >
>  > Any ideas?
>  >
>  > Thanks,
>  > Graeme
>  >
>  > p.s.  remedy is the db and it has a table called remedy as well
>
>
> Hi!!
>
> Start your MySQL server with --big-tables support or set BIG_TABLES
> run time option.
>
> There are other options, but available under 3.23.
>
> You are eveidently using 3.22.
>
> Regards,
>
> Sinisa
>
> +----------------------------------------------------------------------+
> | TcX  ____  __     _____   _____  ___     ==  mysql@stripped            |
> |     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sinisa Milivojevic          |
> |    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sinisa@stripped     |
> |   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Larnaka, Cyprus             |
> |  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                             |
> |  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                            |
> |             /*/             \*\                Developers Team       |
> +----------------------------------------------------------------------+
>