Please help with query to show duplicate addresses... TIA!
I am trying to come up with a query that shows duplicate last names and the order numbers for each occurance of. I can get as far as determining the duplicates but my query result only outputs one order for each. Here is my current query, an example of the results and an example of the results I want. P.S. This is just an example, looking at duplicate last names is seldom of any practical value! Thanks for any help! customer_last_name order_number +---+-+ +smith + 1 + +smith + 2 + +smith + 3 + +-+ SELECT customer_last_name, order_number, COUNT(customer_last_name) AS duplicate_customer_last_names FROM orders GROUP BY customer_last_name HAVING (duplicate_customer_last_names 1) Result: customer_last_name order_number duplicate_customer_last_names +---+-+-+ +smith + 1 + 3 + +-+-+ Desired Result: customer_last_name order_number duplicate_customer_last_names +---+-+-+ +smith + 1 + 3 + +smith + 2 + 3 + +smith + 3 + 3 + +-+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another newbie question - using OR in WHERE clauses
Is there a simpler way to write something like: SELECT * FROM tablename WHERE columname = 'Bob' OR columname = 'Mike' OR columname = 'Betty' Clearly this does not work: WHERE columname = 'Bob' OR 'Mike' OR 'Betty' Thanks!
Visual/Wizard style software for creating complex queries/joins?
Greetings! Is anyone familiar with/can recommend any software capable of helping design complex (well for me anyway) queries/updates/inserts with joins? Thanks for any info! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reset Auto-Incriment?
I realize that this might be problematic/devastating if you had an AI PK and did this, however in my case there is no problem as there is no related data in the database yet lol. My question is, how can I reset AI? For example I deleted several rows and now my AI starts with the AI number after the last row that was deleted. Ie. 1,2,3,4,9,10,11 when I want to start numbering at 5 not 9! Even if I insert/update with non-nulls then the sequence is still pushed ahead. Is there a way to reset this? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Variable names, colum names
Greetings! Is it common practice to name script variables/form data in say PHP to match the appropriate colums in the db tables? I can see how this makes sense, however in the little work I have done I preferred to do the opposite as it seems to be helpful to keep a distinction. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to get value of autoincriment after inserting NULL?
Greetinsg. If I have a table like with a column being the PK for the table and being an Auto Increment value, what is the best way to return this value to my script? It is possible that additional rows may have been added during the small wait. Ie. Col 1 Col 2 Col 3 AA# SmallText SmallText 123 Foo Bar 124 GoodGuy So my script (PHP) for adding records to the database inserts NULL,text,text into the table but I need to echo back the # that was created! My thought was to maybe grab the last entry in the database before the insert, perform the insert and then query where the file # is greater than that last entry and where the text matches the columns appropriately. I imagine there has to be a better way! Thanks for any help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Best way to get value of autoincriment after inserting NULL?
Thanks (to all who replied) If I lock the table however, if another user is trying to insert (via php page) another record they will get an error right and I will need to make a wait+retry script? Thanks! -Original Message- From: Tobias Asplund [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 12:56 PM To: Paul Fine Cc: [EMAIL PROTECTED] Subject: Re: Best way to get value of autoincriment after inserting NULL? On Mon, 15 Dec 2003, Paul Fine wrote: If I have a table like with a column being the PK for the table and being an Auto Increment value, what is the best way to return this value to my script? If you insert a row LAST_INSERT_ID() will return the primary key value in this setup. The other way is if you're not inserting a record, but just want to know the next value that will be used. SHOW TABLE STATUS LIKE 'tablename'; It is possible that additional rows may have been added during the small wait. It is possible another value is inserted in between the query for the next number and an insert afterwards. To stop this from happening you can place a read-lock on the table, this won't allow any other processes to insert rows until you release the lock. LOCK TABLE tablename READ; Get Auto-increment value Do your stuff... UNLOCK TABLES; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please analyze my project table design
Greetings, my hands on school project is a small real estate database.
I am using MySQL 3.23 without InnoDB, but would like to migrate to 4.x
w/InnoDB at some point.
I would greatly appreciate any feedback on this design.
These are the business rules:
1) 1 matter may have several vendors and/or several purchasers
2) 1 matter will have 1 lawyer and 1 agent
Thank you for any advice!
CREATE TABLE other_lawyers (
other_lawyer_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
other_lawyer_fname VARCHAR(25) NULL,
other_lawyer_lname VARCHAR(25) NULL,
other_lawyer_email VARCHAR(25) NULL,
other_lawyer_address VARCHAR(50) NULL,
other_lawyer_city VARCHAR(15) NULL,
other_lawyer_provice VARCHAR(15) NULL,
other_lawyer_postal VARCHAR(6) NULL,
other_lawyer_phone VARCHAR(10) NULL,
other_lawyer_fax VARCHAR(10) NULL,
other_lawyer_firm VARCHAR(40) NULL,
PRIMARY KEY(other_lawyer_id)
);
CREATE TABLE agents (
agent_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
agent_fname VARCHAR(25) NULL,
agent_lname VARCHAR(25) NULL,
agent_email VARCHAR(25) NULL,
agent_address VARCHAR(50) NULL,
agent_city VARCHAR(15) NULL,
agent_provice VARCHAR(15) NULL,
agent_postal VARCHAR(6) NULL,
agent_phone VARCHAR(10) NULL,
agent_fax VARCHAR(10) NULL,
agent_agency VARCHAR(40) NULL,
PRIMARY KEY(agent_id)
);
CREATE TABLE matters (
file_number SMALLINT UNSIGNED NOT NULL,
agents_agent_id INTEGER UNSIGNED NOT NULL,
other_lawyers_other_lawyer_id INTEGER UNSIGNED NOT NULL,
sale_or_purchase ENUM('s','p') NULL,
property_address VARCHAR(50) NULL,
property_city VARCHAR(15) NULL,
property_province VARCHAR(15) NULL,
price FLOAT(8,2) NULL,
file_open_date DATE NULL,
file_posession_date DATE NULL,
PRIMARY KEY(file_number),
INDEX matters_FKIndex1(other_lawyers_other_lawyer_id),
INDEX matters_FKIndex2(agents_agent_id)
);
CREATE TABLE vendors (
vendor_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
matters_file_number SMALLINT UNSIGNED NOT NULL,
vendor_fname VARCHAR(25) NULL,
vendor_lname VARCHAR(25) NULL,
vendor_email VARCHAR(25) NULL,
vendor_address VARCHAR(50) NULL,
vendor_city VARCHAR(15) NULL,
vendor_provice VARCHAR(15) NULL,
vendor_phone_home VARCHAR(10) NULL,
vendor_phone_work VARCHAR(10) NULL,
vendor_postal VARCHAR(6) NULL,
vendor_fax VARCHAR(10) NULL,
vendor_firm VARCHAR(40) NULL,
PRIMARY KEY(vendor_id),
INDEX vendors_FKIndex1(matters_file_number)
);
CREATE TABLE purchasers (
purchasers_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
matters_file_number SMALLINT UNSIGNED NOT NULL,
purchaser_fname VARCHAR(25) NULL,
purchaser_lname VARCHAR(25) NULL,
purchaser_email VARCHAR(25) NULL,
purchaser_address VARCHAR(50) NULL,
purchaser_city VARCHAR(15) NULL,
purchaser_provice VARCHAR(15) NULL,
purchaser_phone_home VARCHAR(10) NULL,
purchaser_phone_work VARCHAR(10) NULL,
purchaser_postal VARCHAR(6) NULL,
purchaser_fax VARCHAR(10) NULL,
purchaser_firm VARCHAR(40) NULL,
PRIMARY KEY(purchasers_id),
INDEX purchasers_FKIndex1(matters_file_number)
);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting data into table1 should insert data into table2's FK ???
Du If I have two tables with the first table containing a field which is FK of table 2, when I insert a value into this field, should it not automagically insert a value into that PK in the second table? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TINYINT(1) vs ENUM?
Normally for a column with 2 possible values, I would use TINYINT(1) and programmatically assign a value (ie. Yes/No) to 0/1. I assume that this is the correct: way? Now how about using ENUM instead? Is there any difference in overhead? Thanks!
TINYINT(1) vs ENUM ?
Normally for a column with 2 possible values, I would use TINYINT(1) and programmatically assign a value (ie. Yes/No) to 0/1. I assume that this is the correct: way? Now how about using ENUM instead? Is there any difference in overhead? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migration to INODB?
First of all I would like to thank everyone who has helped with my past posts. I'm not sure what the correct etiquette is, to post a thanks to every poster? Anyway, my situation is this. The production server is currently running mysql 3.23. I do not want to migrate to 4.x right away, however I am trying to develop a database that may benefit from InnoDB features. My question is, how hard is it to migrate to MyISAM to InnoDB tables? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Choosing between VARCHAR and TEXT
Can anyone tell me what is better to use for items such as names and addresses? I suspect VARCHAR(max anticipated length) but why? Is it because Text will pad? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on Win32 vs Linux ?
Greetings. Can anyone comment on the stability of MySQL on Win32? Unfortunately I may have to use IIS for a project and am just wandering if I can expect the same sort of stability and performance using MySQL (and PHP) on a Windows Server. Thanks for any advice! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The correct way to deal with name_1, name_2, name_3
Greetings, I would be greatful for any advice on the correct way to do this. If I have something dynamic, for example customer names where usually there are 1 or 2 unique customer names but possibly say up to 10, what is the correct design? For example I could simply create a table with name_1, name_2, name_3 10. I am sure there is a more efficient way to do this. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: The correct way to deal with name_1, name_2, name_3
Thanks. I do mean Do you mean one column for each name, in which case these would comprise 10 columns, most of which would have NULL most of the time? Is this your suggestion in this case: Table 1 | blahblah | blah | blah | CustID (PK) | Table 2 | CustID (FK) | Name | Table 2 Sample Data | 11 | Bart | | 11 | Jamie| | 11 | Bob | Therefore I can select NAME from Table 2 where CustID matches Table 1 and thus have all the names required? Thanks! -Original Message- From: Stephen Fromm [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 10:01 AM To: Paul Fine; [EMAIL PROTECTED] Subject: Re: The correct way to deal with name_1, name_2, name_3 - Original Message - From: Paul Fine [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 17, 2003 10:37 AM Subject: The correct way to deal with name_1, name_2, name_3 Greetings, I would be greatful for any advice on the correct way to do this. If I have something dynamic, for example customer names where usually there are 1 or 2 unique customer names but possibly say up to 10, what is the correct design? For example I could simply create a table with name_1, name_2, name_3 10. I am sure there is a more efficient way to do this. Could you be more specific? Do you mean one column for each name, in which case these would comprise 10 columns, most of which would have NULL most of the time? Or do you mean a separate row, one of whose columns is customer_name, with one of 10 values? I can't say much because of lack of detail (i.e., what's in the rest of the table), but I'd have TWO tables. In the table you're discussing, I'd have a column called cust_id. In another table, the customer table, I'd have two entries, cust_id and cust_name. cust_id in the original table would be a foreign key pointing at the customer table... Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4 Built in SSL?
Thanks but if I am not mistaken, you are talking about SSH tunneling the connection not what I am looking for. I am interested in how to implement SSL. Thanks! -Original Message- From: Ferguson, Michael [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 2:47 PM To: Paul Fine Subject: RE: MySQL 4 Built in SSL? What I have done is load putty on my WIN2K box. Connect to the nix box - make sure authentication is correctly configured Launch mysql and work on it from the command line prompt -Original Message- From: Paul Fine [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:32 PM To: [EMAIL PROTECTED] Subject: MySQL 4 Built in SSL? Would anyone be kind enough to provide an example of using MySQL 4 with it's apparent built in SSL functionality to connect from a Win client to *Nix box? Am I correct in assuming that this new functionality means that I will not have to use stunnel or ssh tunneling? Thanks for any info! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQLD Dump - Locks Database
Can anyone please tell me what if anything happens with database locking during the execution of a mysqldump? Ie. What happens to database transactions occurring while performing a dump? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Digest Again, PLEASE SOMEONE HELP?
Thanks. Well I can't figure out what the problem is, I keep getting single e-mails for each post :( even after unsubscribing and resubscribing with digest selected. -Original Message- From: Rob A. Brahier [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 7:44 AM To: Paul Fine; [EMAIL PROTECTED] Subject: RE: Digest Again, PLEASE SOMEONE HELP? Digest mode for this list is 2 digests a day. -Original Message- From: Paul Fine [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:37 PM To: [EMAIL PROTECTED] Subject: Digest Again, PLEASE SOMEONE HELP? Unless I am misinformed, subscription to the DIGEST format of this list should cause all messages to come in one daily e-mail? Can someone else who is subscribed this way please at least let me know that this is at least the case for them? I continue to get every single post as an individual e-mail. I have tried canceling and re-subscribing with the DIGEST option selected, to no avail. I have also not received any response from admin. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4 Built in SSL?
Would anyone be kind enough to provide an example of using MySQL 4 with it's apparent built in SSL functionality to connect from a Win client to *Nix box? Am I correct in assuming that this new functionality means that I will not have to use stunnel or ssh tunneling? Thanks for any info! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Digest Again, PLEASE SOMEONE HELP?
Unless I am misinformed, subscription to the DIGEST format of this list should cause all messages to come in one daily e-mail? Can someone else who is subscribed this way please at least let me know that this is at least the case for them? I continue to get every single post as an individual e-mail. I have tried canceling and re-subscribing with the DIGEST option selected, to no avail. I have also not received any response from admin. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie - How to determine hardware requirements?
Greetings. Does anyone have any suggestions besides trial and error for determining how resource intensive my database is going to be? Is this just something the a developer just gets a feel for after developing a number of databases or are there some guidelines I can use? Thanks for any help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL List Digest ???
Greetings. I have posted a few times to no avail on this matter. Choosing to receive a message digest should send me the daily posts in a single e-mail instead of getting individual e-mails every time someone posts? I cannot get it to happen. I have cancelled my sub and re-sub'd choosing the digest option. Am I missing something? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL List Digest ???
Thanks. I tried that awhile back to no avail. Am I correct that I should be receiving only a single large e-mail each day? Thanks! -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 1:22 PM To: Paul Fine Cc: [EMAIL PROTECTED] Subject: Re: MySQL List Digest ??? On Thu, Nov 06, 2003 at 12:52:21PM -0600, Paul Fine wrote: Greetings. I have posted a few times to no avail on this matter. Choosing to receive a message digest should send me the daily posts in a single e-mail instead of getting individual e-mails every time someone posts? I cannot get it to happen. I have cancelled my sub and re-sub'd choosing the digest option. Am I missing something? I'd try the contact listed in the message headers: List-Help: mailto:[EMAIL PROTECTED] Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,006,804,503 queries (431/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Digest Problem?
I cannot seem to get this list as a digest :( I have unsubscribed and re-subscribed multiple times choosing the digest option to no avail. I keep receiving single e-mails. Thanks for any help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
