LEFT JOIN is the way to go, but you need to make the invoice ID part of the
JOIN condition rather than putting it in the WHERE clause.
SELECT i.IDItems, i.Name, IFNULL(inv.Qty, 0) AS Qty
FROM Items i LEFT JOIN Invoice inv ON i.IDItems = inv.IDItems
AND
shaun thornburgh wrote:
Hi,
I am having trouble with a LEFT JOIN. I have three tables: Users,
Projects and Allocations. Users can be allocated to zero or more
projects and a project can be allocated to zero or more users. Table
descriptions are below.
How can i select a users details and all
Hi everyone.
It occurred to me that I may need a table in my database
related to the primary table to hold such things as login time and date etc.
Creating the table is straight forward enough but how do I create
the relational link or do I simply ensure that the foreign key is
List,
How can i issue a query that can select last dateposted (FIELD)?
My table:
- dateposted, format: -MM-DD
This is my current sql query which catches in desc order for
dateposted and list only 10 sql data.
select
dateposted,
filename,
description
from datafiles
where office = 'AC/PA'
To have a better performence I have installed a new server running
solaris8 and mysql version 3.23.53. The old server is running solaris 7
and mysql 3.23.48-max.
I also transferred the database to the new server and tried to connect
the database with mysql front. Everything worked fine.
Then
Are you files being created in /tmp? How big is this partition?
I wondered about that, but I've set the mysql tmpdir to
/proxydb/mysql/tmp, and the error does actually say: Error writing
file '/proxydb/mysql/tmp/STTbtrac'
Also I couldn't see any mysql files in /tmp. It's tricky isn't it?!
JS.
This is my current sql query which catches in desc order for
dateposted and list only 10 sql data.
select
dateposted,
filename,
description
from datafiles
where office = 'AC/PA'
order by
dateposted desc limit 10;
This will probably work fine. Anyway, make sure that dateposted has
DATE field
hello,
reading the documentation I'm confused to as which is better:
SELECT *
FROM T1,T2
where T1.K1 = T2.K2 AND T1.K2=T2.K2 AND ...otherconditions...
or should I do
SELECT *
FROM T1 NATURAL JOIN T2
WHERE ..other conditions..
or should I
SELECT *
FROM T1 JOIN T2 USING (K1,K2)
WHERE ..other
Can you explicitly create temporary tables?
-Original Message-
From: J S
To: [EMAIL PROTECTED]
Sent: 7/16/04 3:54 AM
Subject: RE: ERROR 3 at line 1: Error writing file
'/proxydb/mysql/tmp/STTbtrac' (Errcode: 27
Are you files being created in /tmp? How big is this partition?
I
What error messages are you receiving?
-Original Message-
From: Peter Bruggink
To: [EMAIL PROTECTED]
Sent: 7/16/04 3:19 AM
Subject: [CONNECTION PROBLEMS]
To have a better performence I have installed a new server running
solaris8 and mysql version 3.23.53. The old server is running
Have you looked at InnoDB tables?
-Original Message-
From: Michael Mason
To: 'MySQL Mailing List'
Sent: 7/16/04 2:06 AM
Subject: Creating Relational Links
Hi everyone.
It occurred to me that I may need a table in my database related to the
primary table to hold such things as login
hi
that may be but it's got weird beahviour. for example,
when u get that in an edit box (like the one u compose
mail in) it looks like: (the upper comma). but
when i read ur mail, it appeared as it's html
character code. in fact, while reading this u may see
it as the character code and not the
Can you explicitly create temporary tables?
Yes, I have full priviledges. Do you mean I could copy the internet_usage
table to a temp table with new keys?
JS.
-Original Message-
From: J S
To: [EMAIL PROTECTED]
Sent: 7/16/04 3:54 AM
Subject: RE: ERROR 3 at line 1: Error writing file
Yes. Can you
CREATE TEMP TABLE ?
-Original Message-
From: J S
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 7/16/04 7:34 AM
Subject: RE: ERROR 3 at line 1: Error writing file
'/proxydb/mysql/tmp/STTbtrac' (Errcode: 27
Can you explicitly create temporary tables?
Yes, I have full
Hi.
Is it possible to make a select from different tables that reside in different
databases that have different usernames? The fact is that I'm using Helm Web Hosting
Control tool for creating databases and users, and it let's me create only one user
per database, so I can't assign a user to
In article [EMAIL PROTECTED],
[EMAIL PROTECTED] writes:
Have you considered splitting this into two queries? One query can gather
information from your token and token_ins tables. The other would join the
first queries results to the other tables to complete your original query.
Depending
Louie,
Do you want just the most recent date? You could run :
SELECT MAX(dateposted)
FROM datafiles
WHERE office='AC/PA'
OR if you want just the most recent record:
SELECT dateposted, filename
FROM datafiles
WHERE office='AC/PA'
ORDER BY dateposted desc
LIMIT 1;
OR you could also ask for
Does you have the rights to use the GRANT and REVOKE commands directly? If
not those, do you have rights to INSERT and DELETE from the users table of
the mysql database? If you do, you could possibly bypass their one user
per databse rule and create your own.
Otherwise I think you are stuck as
Yes. Can you
CREATE TEMP TABLE ?
I managed this:
mysql CREATE TEMPORARY TABLE test
- select * from internet_usage;
Query OK, 324936160 rows affected (54 min 31.64 sec)
Records: 0 Duplicates: 324936160 Warnings: 0
Can you create a temp table with keys then?
-Original Message-
Yes you can.
-Original Message-
From: J S
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 7/16/04 8:35 AM
Subject: RE: ERROR 3 at line 1: Error writing file
'/proxydb/mysql/tmp/STTbtrac' (Errcode: 27
Yes. Can you
CREATE TEMP TABLE ?
I managed this:
mysql CREATE TEMPORARY TABLE
Hi again.
PROCESS LIST is:
Creating tmp file
Repair by sorting
The problem occurs while repair by sorting.
myisam parameters have been increased accordingly:
myisam max extra sort file size = 15000M
myisam max sort file size = 15000M
There must be a bug somewhere? What do you suggest?
Vincent
Hi!
Hello list,
I have already installed mysql 4.1.3 beta to my
windows xp. I set default-caharacter-set to latin5 and
default-collation to latin5_turkish_ci in my.ini
configuration file. Because I want latin5 my default
character set. Then I restarted mysql service and
looked up character set and
Donny Simonton wrote:
Matt,
I've been reading this thread for a while and at this point, I would say
that you would need to provide the table structures and queries that you are
running.
For example, we have one table that has 8 billion rows in it and it close to
100 gigs and we can hammer it all
Does anyone have any experience setting up a group of servers for high
reliability/availability?
We are planning to deploy a WebObjects application but need to design a
configuration that will give us a VERY high level of availability. Our
current thoughts are to have three machines:
1.
Hello, I am upgrading from MYSQL 3.22.30 to 3.23.58 on a Tru64 Alpha 4.0F.
The set of commands below are used to create a Make file. My problem is
that I want include files to be placed under /usr/local/mysql/include;
However, with the configuration below, they are placed under
Hi all,
System details:
PowerMac G5 1.6 GHz dual processor
3 G RAM
Mac OS X (10.3.4)
Application : MYSQL (mysql Ver 12.20 Distrib 4.0.13,
for apple-darwin6.6 (powerpc)) + APPACHE
I have couple of database on a single installation of
MySQL. One of these databases is static (only selects)
and
Mysql 4.0.14
2 Tables:
Table1: Indexed on Field1 (primary key), Field2
Table2: Indexed on Field1 (primary key), Field2
(Foreign key from table1)
When I do this
explain select a.*, b.* from table1 a, table2 b, where
a.field1 = b.field2 and a.field2 = 'value'. returns
the result:
Table type
Kart v wrote:
sort buffer size 2097144
join buffer size 131072
max heap table size 16777216
max join size 4294967295
max sort length 1024
myisam max sort file size 2147483647
myisam sort buffer size 8388608
Upto my understanding mysql runs on a single thread
and is not capable of utilizing both
What does the cardinality look like for table1?
-Original Message-
From: A Z
To: [EMAIL PROTECTED]
Sent: 7/16/04 10:53 AM
Subject: Performance
Mysql 4.0.14
2 Tables:
Table1: Indexed on Field1 (primary key), Field2
Table2: Indexed on Field1 (primary key), Field2
(Foreign key from
--- Ware Adams [EMAIL PROTECTED] wrote:
sort buffer size 2097144
join buffer size 131072
max heap table size 16777216
max join size 4294967295
max sort length 1024
myisam max sort file size 2147483647
myisam sort buffer size 8388608
Upto my understanding mysql runs on a single thread
Here's another option to load without requiring a primary key (requires a
LOT of extra disk space and fast CPU, and a batch window to run).
Load the new daily data into the table without checking for dupes.
Then create a new version of the table with distinct values.
Something like this
Kart v wrote:
Also it would be helpful if you could give me some hints on what
system variables to set and what table types to use. After doing the
basic setup, I will play with the queries to optimize them.
Your key buffer looks very small, assuming you are using MyISAM
tables:
key buffer size
You need to first determine what the bottleneck is. If it's disk I/O
that's slowing you down, then adding another instance of MySQL may
actually hurt you. If that table is physically large, it's probably
your disk subsystem holding you back. I don't know what you disk setup
is like. For about
Since you have a temp table created (no keys I assume), use the command
mysqldump -v -e -n -t dbname tablename filename.sql
This should create insert statements with many values in a single
insert. Then use the client program to insert them to you db.
mysql -u matt -p dbname filename.sql
This
Is there a way to force the results of
Show Index From table
Into a table?
TIA
Dan
You will have to put the value in a resultset and scroll through the
resultset. Natively you can do this with MySQL 4.0.x but you could use a
programming language.
-Original Message-
From: Daniel Cummings
To: [EMAIL PROTECTED]
Sent: 7/16/04 1:19 PM
Subject: Show Index Into Cursor?
Is
How do I prevent IFNULL and ISNULL from returning a null?
I have the following query where this is occurring,
Select IFNULL(sum(qty),0)
from inventory
where partnumber=111
group by partnumber;
If the partnumber has never been in inventory then the sum and ifnull
functions both return null.
Hi Ed,
How do I prevent IFNULL and ISNULL from returning a null?
I have the following query where this is occurring,
Select IFNULL(sum(qty),0)
from inventory
where partnumber=111
group by partnumber;
If the partnumber has never been in inventory then the sum and ifnull
functions both
Victor,
Natively you can't do this? With SqlPassthrough into FoxPro it returns a
cursor but I was hoping to store it into a temporary table on the Backend
(MySql) because the access code is so much cleaner.
Dan
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
You could write your own user defined function similar to show index if you
wanted to achieve this natively.
-Original Message-
From: Daniel Cummings
To: 'Victor Pendleton'
Cc: [EMAIL PROTECTED]
Sent: 7/16/04 2:25 PM
Subject: RE: Show Index Into Cursor?
Victor,
Natively you can't do
matt
the issue is insert speed, I get 150k-1M records daily, of
these, only
5-10 % are new records, I load the EBCDIC file into a temp table, and
then do insert ignore into historytable select * from temp table
Since you have a temp table created (no keys I assume), use the command
If your not using the cpu fully, you definitely need
to optimize
mysqld's settings and/or optimize your queries.
Even if the cpu is
running full out, optimizing queries to examine
fewer rows can help.
Yes Ware, the CPU is not fully utilized. It shows just
10 -15% utilization. Could you
Kart v wrote:
If your not using the cpu fully, you definitely need to optimize
mysqld's settings and/or optimize your queries. Even if the cpu is
running full out, optimizing queries to examine fewer rows can help.
Yes Ware, the CPU is not fully utilized. It shows just 10 -15%
utilization. Could
Kart v wrote:
If your not using the cpu fully, you definitely need to optimize
mysqld's settings and/or optimize your queries. Even if the cpu is
running full out, optimizing queries to examine fewer rows can help.
Yes Ware, the CPU is not fully utilized. It shows just 10 -15%
utilization. Could
Hello,
I'm trying to solve a task in Mysql 4:
I have a table
rec_id | record_ref | keyword
**
1| 2| apple
2| 2|orange
3| 2|apple
4| 3|mango
**
How can I
Hello,
I'm trying to solve a task in Mysql 4:
I have a table
rec_id | record_ref | keyword
**
1| 2| apple
2| 2|orange
3| 2|apple
4| 3|mango
**
How can I
Hi everybody,
I have the following scenario. Several computers with shared disk in a
LAN. Each of these computer has a MySQL server that serves several
databases. I have several clients that communicate with a Java process
that I have in each computer to answer queries for a given database
(it
Is there a way of getting the number of updated rows without using the API
calls?
TIA
Dan
I think one way to solve it would be to move your data to a new copy of
your table. Assuming record_ref and keyword are separate fields...
CREATE TABLE newtable SELECT * FROM oldtable GROUP BY CONCAT(
record_ref, keyword );
Wes
On Jul 16, 2004, at 7:08 PM, L a n a wrote:
Hello,
I'm trying to
In article [EMAIL PROTECTED],
[EMAIL PROTECTED] (Ton Hospel) writes:
The previous mail is about version 4.0.20 by the way.
Another thing I notice is that in COM_FIELD_LIST the parsing for
the wildcard seem iffy. The code does:
if
What are you using to perform the deletes?
-Original Message-
From: Daniel Cummings
To: [EMAIL PROTECTED]
Sent: 7/16/04 7:10 PM
Subject: Get Updated Rows
Is there a way of getting the number of updated rows without using the
API
calls?
TIA
Dan
--
MySQL General Mailing List
51 matches
Mail list logo
