These are the tables in question:
RFQ (Request for Quote)
Part
Inventory
Inventory items ALWAYS have a partID.
RFQ items ALWAYS have a partID.
However, sometimes, RFQ items have an inventoryID as well. Now, we have a
redundancy problem. Because, in those instances when the RFQ has an
Thanks a lot Shawn. As always, your advice has been very helpful.
On 2/3/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 02/02/2006 02:01:11
PM:
I have a table `requirement` which is left joining to a table
`inventory`
based on a matching
the
join to only those records that would come back from the limit set.
Thanks,
Scott Klarenbach
I am importing records from a text file into my DB. Each record in the text
file corresponds to six tables in the DB due to normalization.
So for example, an inventory record containing a part, vendor,
contactPerson, mpn etc...in ONE line of a text file, requires 6 inserts into
the DB.
Further
sort of
thing, where the view internally compiles the where criteria from the
underlying table.
Scott Klarenbach
On 1/6/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 01/06/2006 08:13:10
PM:
Hello,
I'm new to views and am discovering massive
Hello,
I'm new to views and am discovering massive performance hits in the views
I've created once the records start to climb above 20,000 or so. Does
anyone know of a great primer/tutorial site for optimizing views in MySQL,
or even generally? What are the best practices etc...? I find when I
] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 11/28/2005 01:58:22
PM:
I'd like to do the following in my view
select
fieldOne,
fieldTwo,
if(fieldThree.length0) then fieldThree as Company
else fieldFour as Company
from table;
I realize
I'd like to do the following in my view
select
fieldOne,
fieldTwo,
if(fieldThree.length0) then fieldThree as Company
else fieldFour as Company
from table;
I realize this syntax isn't correct and length doesn't exists, but is
this possible? I've seen it done in SQLServer, but can't
are overridden by an order by clause when calling the view, but
this isn't what I'm referring to. The myView statement has no order
by clause, yet I can't seem to order the list when calling the view.
Thanks,
Scott Klarenbach
--
MySQL General Mailing List
For list archives: http://lists.mysql.com
if they
call the view with their own ORDER BY clause.
Scott Klarenbach.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I'm using Mysql 5.0.7 and I've noticed the following very strange
functionality, perhaps someone can shed some light on it for me.
2 Tables (Request and Inventory)
Request
id (int),
partNumber varchar(60)
Inventory
id(int),
MPN varchar(60),
MPNClean varchar(60)
I have about 1500
I've used msyqldump to retrieve the structure and data of my db, but
can I use it to only spit out the structure, ie, the Create Table
statements, but none of the inserts.
Thanks.
Scott.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Does MySQL 5 provide native XML support? ie, can I have a stored
procedure return an XML string instead of a recordset? Can I pass in
an XML string/doc and have the DB update relational tables based on
it?
Thanks.
Scott
--
MySQL General Mailing List
For list archives:
The following query is in desperate need of optimization. Any gurus
out there who can share some insights, I'd greatly appreciate it.
I have a request table, from which I'm pulling all records. Each
request record has a part number, and for each requested part number,
I'd like to query the
Thanks.
On 6/1/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005 06:57:19
PM:
-- How is 'xFxIxExLxDx1x' easier to search than 'FIELD1'?
It's not easier. It is; however, accurate for the purpose at hand.
FIELD1 isn't
Windows 2003 server. I'm using PHP 5.0.4 and MySQL 5.0.4. Any
help is appreciated. Thanks.
On 5/27/05, Scott Klarenbach [EMAIL PROTECTED] wrote:
select greatest(max(col1), max(col2), max(col3), max(col4)) from table
works the best, as Keith pointed toward initially. Remember, I forgot
Is this not the proper way to use a function in a select statement?
SELECT
t.field1,
t.field2,
functionPerformAdditionalQueryFromField(t.field2) AS 'customField'
FROM Table t
I'd like to perform the function on every row in the result set, and
store the returned value of that function in EACH
every time you issue the SQL request. OR issue
the command set GLOBAL wait_timeout=28000; Then issue the select
DVP
Dathan Vance Pattishall http://www.friendster.com
-Original Message-
From: Scott Klarenbach [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 31, 2005
I'm trying to replicate this PHP behavior in a MySQL stored procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and the string is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use
Can I select the maximum value across multiple columns?
ie, I'd like to select the highest value of buyCost AND sellCost in a
table...where buy and sell are two different columns in the same
table.
i actually have 4 comparisons to run, and don't want to have to
execute 4 queries.
--
MySQL
Here's what I came up with in case anyone else needs a quick fix. A
regular expression replace would've been nicer, but, you do what you
gotta do...
CREATE PROCEDURE `test`(`par` varchar(60))
BEGIN
DECLARE nChars INT DEFAULT CHAR_LENGTH(par);
DECLARE nCounter INT DEFAULT 1;
Unimin Corporation - Spruce Pine
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005 01:30:35
PM:
I'm trying to replicate this PHP behavior in a MySQL stored procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character
Ivey [EMAIL PROTECTED] wrote:
Scott Klarenbach wrote:
Can I select the maximum value across multiple columns?
You want the GREATEST() function:
http://dev.mysql.com/doc/mysql/en/comparison-operators.html
--
Keith Ivey [EMAIL PROTECTED]
Smokefree DC
http://www.smokefreedc.org
select greatest(max(col1), max(col2), max(col3), max(col4)) from table
works the best, as Keith pointed toward initially. Remember, I forgot
to mention that I wanted the greatest for the whole table, not just
for each rowso, 10, 12, 8 is not what I wanted...out of
10 2 3
5 4 8
1 12 7
i
' respectively, and not worry about whether the data is
erroneous or valid; just to basically ignore all the characters and
let a human decide what they want.
If you have a more elegant solution, I'm all ears :-).
On 5/27/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL
problem passing in the {count,offset} parameters
for LIMIT clauses, and the {filename} for LOAD DATA INFILE.
Thanks for any insights.
Scott Klarenbach
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I've upgraded from 5.0.2 to 5.0.3 Beta, and now there is a glitch in
one of my regular expression queries.
The expression works like this: a query for 'search' returns true for
a matching 'search' field, but, querying 's$$#e%ar^c)(h' must also
return true for a 'search' field. In other words, I
Thanks for your help, that's going to work great!
sk
On 5/2/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 05:39:36
PM:
Thanks Shawn, that helps a lot. I like your general idea of handling
it at application level. I guess my
Hello,
I'm using MySQL 5.0.3 Beta, and I'm hoping for some general
tips/experience you guys may have had regarding optomistic vs
pessimistic locking in a web app (PHP 5.0.3)
I'm more of a windows programmer, and I've always implemented
pessimistic over optomistic as it's much more professional
user, otherwise, prompt them
saying it's now been locked by another user and your changes won't be
saved. But this is the same user inconvenience caused by optomistic
locking.
Thanks,
Scott.
On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 04
time, and would be annoyed if they had
to log back in every time...
On 4/29/05, Scott Klarenbach [EMAIL PROTECTED] wrote:
Thanks Shawn, that helps a lot. I like your general idea of handling
it at application level. I guess my main concern w/ web apps and
locking the record (even at app level
Do this.
Save the create tables commands in a textfile on your harddrive called
'C:\tables.sql'
Load mysql from the command line.
@ the prompt, type the following
mysqlcreate database `myDBName`;
mysqluse `myDBName`;
mysqlsource C:\tables.sql;
that's it!
On Apr 2, 2005 10:26 AM, Niki
I've got a good deal of experience using mysql, but never in a large
production environment with many concurrent users.
Using the InnoDB engine, what is the general practice for ensuring
data integrity when multiple users are writing to the same table?
Should I explicitly lock the table before I
Is there a way to represent infinity in mysql?
I've got a range field in my GUI, which is x...
if the user chooses this field, in the DB, I store it as:
id | from | to | other
2 | x | infinity | etc...
this is because there are situations of x and between x AND y,
so from and to is the
http://dev.mysql.com/doc/mysql/en/alter-table.html
ALTER TABLE dtd_test ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD
PRIMARY KEY (id);
On Wed, 16 Mar 2005 14:56:59 -0500, Ed [EMAIL PROTECTED] wrote:
Hi all,
I am using MySQL Command Line and have created a table called dtd_test. It
has
See my original post:
ALTER TABLE dtd_test ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD
PRIMARY KEY (id);
Primary key could've just as easily been another column. You have to
include another ADD command after the comma.
On Wed, 16 Mar 2005 15:25:14 -0800, Scott Klarenbach
[EMAIL PROTECTED
SELECT id, id FROM data should work just fine.
On Tue, 15 Mar 2005 17:43:29 -0600, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Hi there!
I need to do this:
From this table
+--+
|id|Data |
|--|---|
| 1|Something 1|
| 2|Something 2|
| 3|Something 3|
|
Oh, sorry, I didn't look close enough at your question...never mind :-)
On Tue, 15 Mar 2005 16:02:59 -0800, Scott Klarenbach
[EMAIL PROTECTED] wrote:
SELECT id, id FROM data should work just fine.
On Tue, 15 Mar 2005 17:43:29 -0600, [EMAIL PROTECTED] [EMAIL PROTECTED]
wrote:
Hi
SELECT (SELECT id from data WHERE id=1), (SELECT id from data WHERE id=4);
This willl return you the 2 columns in one row.
Otherwise, if you're looking to return multiple queries into one
result set, then UNION is what you're looking for. ie (select id from
data) UNION (select id from data).
What are the results?
sk
On Mon, 14 Mar 2005 14:22:38 -0800, Nick Zukin [EMAIL PROTECTED] wrote:
I'm trying to do a multitable query and am having problems.
I have three tables: vendors, products, and vendorproducts. The
vendorproducts table creates a many to many relationship between the
cider', BUT returns false for 'ple'.
But, without seeing the results and what you'd hoped them to be, it's
tough to narrow down the problem.
sk
On Mon, 14 Mar 2005 15:08:28 -0800, Scott Klarenbach
[EMAIL PROTECTED] wrote:
What are the results?
sk
On Mon, 14 Mar 2005 14:22:38 -0800, Nick
ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext AFTER
ColumnNameToPutAfter
Note that long text is required (put in the correct column type you
intend to move)
Alternate:
INSERT INTO new_table SELECT columns-in-new-order FROM old_table;
DROP table old_table;
ALTER TABLE new_table
Also:
ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext BEFORE
ColumnNameToPutBefore
ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext FIRST
ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext LAST
will work, depending on what you're looking to do.
sk
--
Ya, all my tables are InnoDB unfortunately, and they need to stay that way ;-).
On Sun, 13 Mar 2005 13:48:26 +0200, Amr Mostafa [EMAIL PROTECTED] wrote:
There is the mysqlhotcopy, it's faster too.
However, It will only work if all your tables are MyIsam.
Scott Klarenbach wrote:
I'm
/to/mysql is :)
For more details/information, read this :
http://dev.mysql.com/doc/mysql/en/disaster-prevention.html
- Amr
Scott Klarenbach wrote:
Ya, all my tables are InnoDB unfortunately, and they need to stay that way
;-).
On Sun, 13 Mar 2005 13:48:26 +0200, Amr Mostafa [EMAIL
I'm using Mysql 5.0.2 w/ Windows 2003 server, and there is a bug with
the mysqldump utility. The bug is actually with the Describe table
statement, or Show fields from table statement...
It's been documented on mysql.com, so I'm wondering if there is a
simple alternative to mysqldump that I can
That's incredibly slow. I pull 1000 records through PHP in 1 second
on a P4 2.4 Ghz. Are you pulling the entire recordset and then
limiting it in your app code? Or are you using a limit clause in the
DB?
Also, Peter's point about indexing might help. Without specific SQL
examples, it's
Is there a flag in MYSQL to automatically escape special characters
like single quotes with a backslash? Instead of using a C API or PHP
addslashes() funciton for each field I'd need to escape?
Thanks,
Scott.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
Haha! Great point. I guess it's time for me to call it a day and get
some sleep :-).
Thanks,
Scott.
On Tue, 08 Mar 2005 18:23:24 -0500, Keith Ivey [EMAIL PROTECTED] wrote:
Scott Klarenbach wrote:
Is there a flag in MYSQL to automatically escape special characters
like single quotes
Gary's got another point about the transactions.
I'd still look to using mysqldump first if possible, if they have the
ability it will be remarkably faster.
Otherwise, turning transactions off before the insert, and locking the
table as well, (if you haven't already done that) could prove to
I have a client that wants to search table fields for strings, and
ignore any-non alphanumeric character in the field. (match only the
alphanumeric portion of the field, and discard the rest)
for example, a search for apple would return true on the following record
a**__-p p + l ^^ @e
I
I can't seem to pass in the LIMIT clause variables into a stored procedure
i.e.
Select * from table limit param1, param2;
I get a syntax error compiling the procedure, but when I replace
param1 and param2 with hard coded ints, ie, 10, 10, it compiles and
works fine.
Is this functionality not
I can't seem to make the Load Data statement work inside of a stored procedure.
ie
LOAD DATA LOCAL INFILE file.txt INTO my_table
this works fine in PHP, but when I use it in a procedure, and pass in
the file name as a parameter, it won't compile.
LOAD DATA LOCAL INFILE fileParameter INTO
53 matches
Mail list logo
