Re: SELECT query question

ID FROM Table2 WHERE Source2_Name = 'name' UNION SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name' -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Monday, July 27, 2009 1:09 PM To: mysql@lists.mysql.com Subject: Re:

RE: SELECT query question

¢ndido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Monday, July 27, 2009 1:09 PM To: mysql@lists.mysql.com Subject: Re: SELECT query question select * from MainTable MT left join Table1 T1 on MT.Main_ID = T1.MainID left join Table2 T2 on MT.Main_ID = T2.MainID left join Table

Re: SELECT query question

select * from MainTable MT left join Table1 T1 on MT.Main_ID = T1.MainID left join Table2 T2 on MT.Main_ID = T2.MainID left join Table3 T3 on MT.Main_ID = T3.MainID where T1.Source1_Name = "anything" or T2.Source2_Name = "anything" or T3.Source3_Name = "anything" Not tested. -

Re: Select query locks tables in Innodb

problem disappeared and the selects behave as one would expect. Many thanks to all who offered advice. Carl - Original Message - From: "Perrin Harkins" To: "Carl" Cc: Sent: Friday, March 13, 2009 1:40 PM Subject: Re: Select query locks tables in Innodb 2009/3/

Re: Select query locks tables in Innodb

2009/3/12 Carl : > I am still a little puzzled about how we could have a relatively large set > of records (100,000+) and yet not cause any table to be locked as the server > has only 8GB of memory. What's the relationship you're implying between memory and locking? Multi-version concurrency doesn

Re: Select query locks tables in Innodb

's suggestions also. > > Carl > > > > > > - Original Message - From: "Brent Baisley" > To: "Carl" > Sent: Thursday, March 05, 2009 1:12 PM > Subject: Re: Select query locks tables in Innodb > > > Ok, so you have 687 unique or

Re: Select query locks tables in Innodb

elp and Baron's suggestions also. Carl - Original Message - From: "Brent Baisley" To: "Carl" Sent: Thursday, March 05, 2009 1:12 PM Subject: Re: Select query locks tables in Innodb Ok, so you have 687 unique organization serial numbers. That's not ver

Re: Select query locks tables in Innodb

on Schwartz" To: "Carl" Cc: Sent: Wednesday, March 04, 2009 8:11 PM Subject: Re: Select query locks tables in Innodb I don't think it locks the tables. The behavior may be similar, but I seriously doubt that's what's happening. Take a snapshot of SHOW INNODB STAT

Re: Select query locks tables in Innodb

gt; Since the report query is only reading data, I am puzzled why it locks the > tables.  Any ideas? > > TIA, > > Carl > > > - Original Message - From: "Baron Schwartz" > To: "Carl" > Cc: > Sent: Wednesday, March 04, 2009 2:29 PM > Subject: Re:

Re: Select query locks tables in Innodb

-- From: "Baron Schwartz" To: "Carl" Cc: Sent: Wednesday, March 04, 2009 2:29 PM Subject: Re: Select query locks tables in Innodb Carl, Locked status in SHOW PROCESSLIST and a table being locked are different. There is a bug in MySQL that shows Locked status for queries

Re: Select query locks tables in Innodb

Carl, Locked status in SHOW PROCESSLIST and a table being locked are different. There is a bug in MySQL that shows Locked status for queries accessing InnoDB tables in some cases. What version of MySQL are you using? The table is not really locked, you're just seeing that as a side effect of wh

Re: Select query locks tables in Innodb

Carl, Locked status in SHOW PROCESSLIST and a table being locked are different. There is a bug in MySQL that shows Locked status for queries accessing InnoDB tables in some cases. What version of MySQL are you using? The table is not really locked, you're just seeing that as a side effect of wh

Re: Select query locks tables in Innodb

the isolation level but I believe it is whatever was set out of the box (five years ago.) Thanks, Carl - Original Message - From: "Perrin Harkins" To: "Carl" Cc: Sent: Wednesday, March 04, 2009 1:49 PM Subject: Re: Select query locks tables in Innodb 2009

Re: Select query locks tables in Innodb

report query. It is a foreign key on one of the files that is used. TIA, Carl - Original Message - From: "Baron Schwartz" To: "Brent Baisley" Cc: "Carl" ; Sent: Tuesday, March 03, 2009 5:50 PM Subject: Re: Select query locks tables in Innodb On Tue,

Re: Select query locks tables in Innodb

2009/3/4 Carl : > However, when I had all the pieces in the query > (copy attached), I could easily see it was locking tables using the Server > Monitor in Navicat. I don't know what that is, but I think you'd better look at something closer to the bone, like SHOW INNODB STATUS. > Explain (copy a

Re: Select query locks tables in Innodb

Message - From: "Baron Schwartz" To: "Brent Baisley" Cc: "Carl" ; Sent: Tuesday, March 03, 2009 5:50 PM Subject: Re: Select query locks tables in Innodb On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley wrote: A SELECT will/can lock a table. It almost al

Re: Select query locks tables in Innodb

om: "Baron Schwartz" To: "Brent Baisley" Cc: "Carl" ; Sent: Tuesday, March 03, 2009 5:50 PM Subject: Re: Select query locks tables in Innodb On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley wrote: A SELECT will/can lock a table. It almost always does in MyISAM (no inse

Re: Select query locks tables in Innodb

On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley wrote: > A SELECT will/can lock a table. It almost always does in MyISAM (no > insert/updates), almost never does in InnoDB. There is an exception to > every rule. The problem is most likely in the 107488 rows part of the > query. That's too many rows

Re: Select query locks tables in Innodb

On Tue, Mar 3, 2009 at 10:53 AM, Carl wrote: > A query that is selecting data for a report locks the files that it accesses > forcing users who are attempting to enter transactions to wait until the > select query is finished. Is it an INSERT INTO...SELECT FROM? Those lock. Also, have you ver

Re: Select query locks tables in Innodb

A SELECT will/can lock a table. It almost always does in MyISAM (no insert/updates), almost never does in InnoDB. There is an exception to every rule. The problem is most likely in the 107488 rows part of the query. That's too many rows for InnoDB to keep a version history on so it's likely just lo

Re: Select Query

On Fri, May 23, 2008 at 11:20 PM, Velen <[EMAIL PROTECTED]> wrote: > Hi, > > I wanted to know when doing a select query how is it executed : > > > If there is 1000 records with price<10, 3000 records with flag='Y' and the > table contains 200,000 records. > > Select code, description, price, flag

Re: Select query problem

Barry wrote: Nenad Bosanac schrieb: Hi I have one problem that i can`t resolve. still need advice or is it solved? IF!!! you need IF!! :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select Query taking time

Ratheesh K J wrote: Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running til

Re: Select Query taking time

On Monday 24 July 2006 09:06, Duncan Hill wrote: > On Monday 24 July 2006 09:05, Ratheesh K J wrote: > > Hello All, > > > > I run a select query to see its speed. It took around 5 seconds. Now i > > run the same query simultaneously twice usng two instances of the client > > tool. It took 10 second

Re: Select Query taking time

On Monday 24 July 2006 09:05, Ratheesh K J wrote: > Hello All, > > I run a select query to see its speed. It took around 5 seconds. Now i run > the same query simultaneously twice usng two instances of the client tool. > It took 10 seconds for both the queris to complete. Its not 5 secs + 5 > secs.

Re: Select query problem

Nenad Bosanac schrieb: Hi I have one problem that i can`t resolve. still need advice or is it solved? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.

Re: SELECT Query GROUP BY

No problem, glad to help. I noticed your comment in an earlier message about it seeming like a workaround - I don't think it seems like a workaround at all. Having a table with the possible values makes for a normal database structure, and an approach that should keep you from having to modif

Re: SELECT Query GROUP BY

Thank you Dan, [...] > Otherwise it's just not possible to show > what's not there - in your case, think of this: how would MySQL know to > show 5 when there are no 5's, but not also show the count for every > other integer that's not there? (6, 7, 8, .. 1048576, 1048577, etc.) [...] Sure, easy

Re: SELECT Query GROUP BY

The easiest thing to do would be to create an additional table containing all the possible valid values for contract level, then join on that table to show counts. Otherwise it's just not possible to show what's not there - in your case, think of this: how would MySQL know to show 5 when there

Re: SELECT Query GROUP BY

Thank you, Peng Yi-fan but incase there is no contract with the level 5, it will not be shown. I would like to see: level amount 1 34 2 0 3 18 4 986 5 0 I could add it in the application, but I try to do it within the Query. btw. the right join I mentioned, doesn

Re: SELECT Query GROUP BY

The schema of your contract should be like this: Contract (id, level, ...) where column 'id' is the primary key, isn't it? If so, you can try this: SELECT COUNT(id) FROM contract GROUP BY level - Original Message - From: "Jay" <[EMAIL PROTECTED]> To: Sent: Thursday, May 11, 2006 5:4

Re: select query taking too long

I"m a little confused by the query you posted.. it looks like it would work, although with many redundant subqueries to get there. From your requirement, I don't understand why you needs to wrap it in a self- referencing subquery.. Why does this not give you the same logical value? selec

Re: select query taking too long

Unfortunately, I cannot create indexes for these tables. These are on production and I cannot modify the tables in anyway. Also, none of the columns are unique in nature - they just serve as a reporting store. Is there anyway that I can tune the select query itself and hope some performance enhanc

Re: select query taking too long

On Wed, 2005-10-19 at 17:39 -0400, Anoop kumar V wrote: > I have 2 tables used for reporting and there are no primary keys or > indexes for either. I am trying to run a select query to identify some > rows that need to be removed. But for around 100,000 rows the query is > taking too long. Can some

Re: select/query from two tables

You didn't try it, did you 8-). In a nutshell a basic SELECT statement looks like: SELECT /columns list/ FROM /tables list/ WHERE /conditions list/ The /columns list/ is where you specify all of the values you want from the database, including constant and computed values The /tables list/

Re: select/query from two tables

No. You only get one FROM clause, so it's "SELECT columns FROM tables...". See the manual for complete details of SELECT syntax . Michael leegold wrote: On Fri, 8 Oct 2004 12:22:37 -0400, [EMAIL PROTECTED] said: I think that this will work: $query =

Re: select/query from two tables

On Fri, 8 Oct 2004 12:22:37 -0400, [EMAIL PROTECTED] said: > I think that this will work: > > $query = "SELECT page.*, url.* FROM `page` LEFT JOIN `keywords` USING > (`page_id`) LEFT JOIN URL USING (`page_id`) WHERE MATCH > (`keywords`.`keyword_txt`) AGAINST ('$keyword' > IN BOOLEAN MODE)"; Sor

Re: select/query from two tables

I think that this will work: $query = "SELECT page.*, url.* FROM `page` LEFT JOIN `keywords` USING (`page_id`) LEFT JOIN URL USING (`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST ('$keyword' IN BOOLEAN MODE)"; Shawn Green Database Administrator Unimin Corporation - Spruce Pine "lee

Re: select query that uses a temporary table

"Lorderon" <[EMAIL PROTECTED]> wrote: > Mabye, is there a way to tell MySQL to limit the temporary table up to 500 > rows? so, when a row is matching into the top 500 rows, the last row will be > dropped out (in case the table is on limit), and the new matched row will be > inserted into the right

Re: select query help needed

Daniel, >I have a database with the following (simplified) structure: >[Products] >ProductID >ProductName >[PurchaseRecords] >ProductID >CustomerID > >I need to find all of the rows in the table Products which do not have >at least one corresponding row in PurchaseRecords. How do I transla

Re: select query

Ratna Rajesh Thangudu said: my table looks like this : code size 1n3j 14 1n3j 32 1n3j 37 1n9j 14 1n9j 32 1n9j 14 1nm4 14 1nm4 37 1nm4 32 1nmi14 1nmi14 1oo314 1oo314 1oo414 1oo414 I want to select those rows with 'size' 14, 32 an

RE: SELECT Query assistance please

Got it, thanks SELECT PROJECTCODE.ID AS 'ID', PROJECTCODE.Name AS 'Reference', EVENT.ID, EVENT.ID_PROJECTCODE FROM PROJECTCODE LEFT JOIN EVENT ON PROJECTCODE.ID = EVENT.ID_PROJECTCODE WHERE EVENT.ID IS NULL ORDER BY PROJECTCODE.Name -Original Message- From: Luc Foisy Sent: Thursday, N

Re: select query syntax help

Dan, SELECT ResourceTable.* FROM ResourceTable LEFT JOIN ResourceLinkTable ON ResourceTable.ResourceID = ResourceLinkTable.ResourceID WHERE ResourceLinkTable.ResourceID IS NULL; Regards, Thomas On Thu, 30 Oct 2003, Dan Lamb wrote: > Hello All, > > I have two table the look like thi

RE: select query syntax help [ANSWER]

This is a common question. The syntax looks like this: SELECT a.* FROM tbl_a AS a LEFT JOIN tbl_b AS b ON a.id = b.id WHERE b.id.id IS NULL; The idea is you're retrieving a recordset of the two tables where the rows are joined on the id. For tbl_b, the id field has no value (its null) so

Re: Select Query-Display current month and last 11 months...

Maybe like: SELECT something FROM tablename WHERE date_column > DATE_SUB(CONCAT(YEAR(NOW()),'-',MONTH(NOW()),'-','01'), INTERVAL @n MONTH) @n is the number of months you want. If you want data from the current month, @n would be 0. -- Diana Soares On Mon, 2003-10-06 at 07:23, [EMAIL PROTECTED

Re: Select query question

Luis Lebron wrote: I have a test results table that looks like this student_id test_id score 1 1 90 1 1 100 1 1 80 2 1 95 2 1 85 2

Re: SELECT query with OUTER JOIN - problem

On Mon, Jun 02, 2003 at 12:48:38PM +0200, Dejan Milenkovic wrote: > I have two tables, one is containing data about courses and the second one > is containing data about course start date. > Is it possible to list all courses with one query which should also return > earliest scheduled start dates

Re: select query to give sum and distinct values

I didn't test it but you may try something like: SELECT ddi, sum(tot_dur) FROM table GROUP BY ddi ORDER BY ddi On Mon, 2003-01-20 at 12:59, Steve Mansfield wrote: > Using mysql 3.23.51 > I have a mysql table that holds records for telephone traffic. > The table fields are as follows: > > id

RE: SELECT query

> -Original Message- > From: Paul DuBois [mailto:[EMAIL PROTECTED]] > Sent: Saturday, December 28, 2002 6:44 PM > To: Gloria L. McMillan; [EMAIL PROTECTED] > Subject: Re: SELECT query > > > At 14:38 -0700 12/28/02, Gloria L. McMillan wrote: > >RE:

Re: SELECT query

At 14:38 -0700 12/28/02, Gloria L. McMillan wrote: RE: MySQL SELECT and COUNT or SUM > Hi, all! I think this SELECT command does almost what I need. SELECT CW03survey.Q6, CW03survey.Q7, CW03survey.Q8 FROM CW03survey WHERE CW03survey.Q3 = '1'

Re: SELECT query

RE: MySQL SELECT and COUNT or SUM [EMAIL PROTECTED] wrote: > Your message cannot be posted because it appears to be either spam or > simply off topic to our filter. To bypass the filter you must include > one of the following words in your message: > > sql,query,queries,smallint > > If you just r

RE: Select Query

gt; -Original Message- > From: John Ragan [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 03, 2002 10:47 AM > To: [EMAIL PROTECTED]; Chris Kay > Subject: Re: Select Query > > > > if you have an ms. windows machine for a front end, > i recommend that you downlo

Re: Select Query

John Ragan wrote: I have a select where I want to get ID 15 && id 25 Can I do something like WHERE ID = 15,25 Or do I have to do WHERE ID = 15 && ID = 25 >> >> You've got your boolean definitions wrong ... if you want two values, you want the rows where t

RE: Select Query

- 843.379.AIRE(2473)| +--+ -Original Message- From: John Ragan [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 03, 2002 10:47 AM To: [EMAIL PROTECTED]; Chris Kay Subject: Re: Select Query if you have an ms. windows machine for a front

Re: Select Query

if you have an ms. windows machine for a front end, i recommend that you download corereader from http://CoreReader.com/ . ( it's free. ) it does point&click queries, so you can quickly experiment with them until you get what you want. it installs at the novice level, so set it to the pro

Re: Select Query

Chris Kay wrote: >Query ( that gets past the anti spam ) > >Question is.. > >I have a select where I want to get ID 15 && id 25 > >Can I do something like WHERE ID = 15,25 > I don't think this will work, In SQL you can do WHERE id IN (15, 25) This IN() function doesn't seems working in

RE: Select Query

- -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Thursday, 3 October 2002 12:24 PM To: Chris Kay; [EMAIL PROTECTED] Subject: Re: Select Query At 12:00 +1000 10/3/02, Chris Kay wrote: >Query ( that gets past the anti spam ) > >

Re: Select Query

At 12:00 +1000 10/3/02, Chris Kay wrote: >Query ( that gets past the anti spam ) > >Question is.. > >I have a select where I want to get ID 15 && id 25 > >Can I do something like WHERE ID = 15,25 > >Or do I have to do WHERE ID = 15 && ID = 25 ... WHERE ID IN (15,25) > >--

Re: select query

Hi, Sorry but MySQL does not support subqueries yet. Bye and Good Luck! --- Narcis GRATIANU <[EMAIL PROTECTED]> wrote: > > > When I try this: > > SELECT article, dealer, price > FROM shop s1 > WHERE price=(SELECT MAX(s2.price) > FROM shop s2 > WHERE s1.article

Re: select query

MySQL doesn't support sub-selects...You must use joins and temporary tables. Check: http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html it gives some info about it. On Fri, 2002-07-12 at 16:37, Narcis GRATIANU wrote: > > > When I try this: > > SELECT article, dealer, price > FROM shop

Re: select query optimization

Hi. As I said, or at least, tried to say, a key over all used columns should help. Adding the column from ORDER BY avoids the "using filesort". Adding YIADDR (which I simply overlooked last time) should add "using index", as I mentioned. This means, that the data file is not touched at all, but o

Re: select query optimization

ben, I did that about three minutes after I got Davids email. Things seem to be working a lot faster now, and the "using filesort" is gone. i am assuming there isn't much more i can do to make this thing faster, but anything else i can do would be awesome! heres is the explain: mysql> expla

Re: select query optimization

Hi. (I am replying to the wrong mail, because I already delete the one from Steve...) The slow part is probably the "using filesort". I am not sure, if it will work, but try a key over all used column, i.e. INDEX ( POOL, STATE, TIMESTAMP ) If it works (i.e. if MySQL correctly "sees" that it ca

RE: select query optimization

Katen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 10, 2002 8:47 AM To: Lopez David E-r9374c; [EMAIL PROTECTED] Subject: RE: select query optimization i made the change, but it looks like it didn't speed the query up at all. here are the results from the first explain: mysql> e

RE: select query optimization

i made the change, but it looks like it didn't speed the query up at all. here are the results from the first explain: mysql> explain select TIME_STAMP, YIADDR from RADPOOL where STATE=0 and POOL='GLOBAL-POOL-SJC' ORDER BY TIME_STAMP limit 1; +-+--+---+---

RE: select query optimization

Katen Try using a compound index with STATE and POOL INDEX ipoolstate( POOL, STATE ) Use EXPLAIN SELECT to see what mysql thinks. David PS anybody know if KEY is the same as INDEX? -Original Message- From: Steve Katen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 10, 2002 8

RE: select query optimization

Steve Have you tried using compound index: INDEX( POOL, STATE ) Just a thought. David -Original Message- From: Steve Katen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 10:13 AM To: [EMAIL PROTECTED] Subject: select query optimization i am running a basic install of m

Re: SELECT query with TRIM and LIKE

Thanks Nathan, I think I will go with "SELECT * FROM table_name WHERE Name LIKE > '%$name%'"; NOBBY - Original Message - From: Nathan Bank <[EMAIL PROTECTED]> To: Tshering Norbu <[EMAIL PROTECTED]> Sent: Friday, February 08, 2002 12:25 PM Subject:

Re: SELECT query with TRIM and LIKE

Thanks Nathan, I think I will go with "SELECT * FROM table_name WHERE Name LIKE > '%$name%'"; NOBBY - Original Message - From: Nathan Bank <[EMAIL PROTECTED]> To: Tshering Norbu <[EMAIL PROTECTED]> Sent: Friday, February 08, 2002 12:25 PM Subject:

Re: SELECT QUERY Problem

Hi, > Table 1: names > Id | name > 15 | George > 16 | Suzy > Table 2 : scores_1 > Id | score > 15 | 85 > 15 | 60 > 15 | 70 > 15 | 95 > Table 3 : scores_2 > Id | score > 15 | 50 > 15 | 55 > 15 | 60 > 15 | 45 > What I want to end up with is a selection that would pick up George and his > highest

Re: SELECT Query in PHP

ssage- >From: Mike [mailto:[EMAIL PROTECTED]] >Sent: Friday, October 26, 2001 9:33 PM >To: Woolsey, Fred >Cc: '[EMAIL PROTECTED] ' >Subject: Re: SELECT Query in PHP > > >I think the worst part about the books is that with things like PHP, >Apache and MySQL,

RE: SELECT Query in PHP

To: 'Mike'; 'Woolsey, Fred' Cc: [EMAIL PROTECTED] Subject: RE: SELECT Query in PHP Yes, I think HTML, mySQL and PHP is a marriage made in heaven but... Online docs is like reading stereo instructions in a foreign language most of the time. I tried the webmonkey.com tutori

RE: SELECT Query in PHP

PM To: Woolsey, Fred Cc: '[EMAIL PROTECTED] ' Subject: Re: SELECT Query in PHP I think the worst part about the books is that with things like PHP, Apache and MySQL, by the time you find (i.e. from browsing at the local library) and sit down to learn, it's talking about MySQL 3.22, A

Re: SELECT Query in PHP

I think the worst part about the books is that with things like PHP, Apache and MySQL, by the time you find (i.e. from browsing at the local library) and sit down to learn, it's talking about MySQL 3.22, Apache 1.2, and PHP3. Nothing beats the online documentation.. amen to that. :) You might

RE: SELECT Query in PHP

Funny... I find the PHP, HTML and MySQL combo to be a marriage made in heaven. Also, I have not yet seen a book (and I've bought a few of them) that beats the PHP and MySQL documentation available <> on the web. Follow the URLs in the other e-mails and you will find the truth... OK, maybe I exag

Re: SELECT Query in PHP

Any books that you buy will probably be derived from: http://www.mysql.com/documentation/mysql/bychapter/ http://www.php.net/manual/en/ Regards, Gary "SuperID" Huntress === FreeSQL.org offering free database hosting to developers Visit http://

RE: SELECT Query in PHP

> I am a newbie at this and I cannot find the damn answer to it! I want > to display the table in an html format with PHP but I cannot get it to > work! With MS SQL Server it was sooo easy! With PHP and mySQL it's a > pain in the butt! I cannot find any documentation on this and I order 3 > bo

Re: SELECT query

- Original Message - From: "Craig Meyers" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, July 04, 2001 3:20 Subject: SELECT query > List, > > Our site is running mysql server version 3.22.32 > I'm a power user - not the DBA, so forgive my ignorance. > > Is it possible to c

Re: select query for duplicate records

That makes sense, sound like I'm redoing in PHP what the UNIQUE column already does. So all I need to do is modify my PHP trap using mysql_affected_rows() = 0, retun a error messagu to the used indicating a rcord already exists. Will try it, THX! Pete Paul DuBois wrote: > > At 5:46 PM -0500 6/27

Re: select query for duplicate records

At 5:46 PM -0500 6/27/01, Pete Kuczynski wrote: >Thanks Paul! >ps works great! >I modified the database with a UNIQUE column like you recommended >earlier, this will now help in the PHP script to trap the duplicate >entry and advise the user of the dup. But if hostname now has a UNIQUE index on i

Re: select query for duplicate records

Thanks Paul! ps works great! I modified the database with a UNIQUE column like you recommended earlier, this will now help in the PHP script to trap the duplicate entry and advise the user of the dup. 1 $result = mysql_query($query); // check if row is returned, if yes error, if

Re: select query for duplicate records

>Hi, >How would a word a select statment, to search a database for duplicate >entries in one field. > >For example, the fields: device, hostname, IP, comments > >I want to find all instances where there my be two devices with the same >hostname. > >Thanks! > >Pete Sir, try the following. SELECT

Re: select query for duplicate records

Peter, That comes pretty close to the distinct -thread in this mailinglist. You might want to try: SELECT device, count(hostname) FROM your_table GROUP BY device HAVING count(hostname)>1; This would display all devices with at least 2 (or more) hostnames. If you always have the same IP address,

Re: select query for duplicate records

At 3:13 PM -0500 6/27/01, Pete Kuczynski wrote: >Hi, >How would a word a select statment, to search a database for duplicate >entries in one field. > >For example, the fields: device, hostname, IP, comments > >I want to find all instances where there my be two devices with the same >hostname. SEL

Re: Select query

On 6/7/01 5:33 PM, "Gary Huntress" <[EMAIL PROTECTED]> wrote: > I'll bet it's a roundoff problemtry select * from sequence_protein where > mol_wt> 53211.62 and mol_wt < 53211.63 > > > Regards, > Gary "SuperID" Huntress > === > FreeSQL.org

Re: Select query

On 6/7/01 4:50 PM, "Eric Fitzgerald" <[EMAIL PROTECTED]> wrote: > When comparing float values, you have to use '' around the value. > > Your query should look like this: > select distinct sequence_id from sequence_protein where mol_wt = '53211.62'; Ok, I should have mentioned that I tried the q

Re: Select query

MAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 08, 2001 8:19 Subject: Re: Select query > On 6/7/01 4:50 PM, "Eric Fitzgerald" <[EMAIL PROTECTED]> wrote: > > > When comparing float values, you have to use '' around the value. > > &g

Re: Select query

When comparing float values, you have to use '' around the value. Your query should look like this: select distinct sequence_id from sequence_protein where mol_wt = '53211.62'; - Original Message - From: "Hannes Niedner" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, June 07

Re: Select query

Switch the column type to double. From the manual: If you are comparing FLOAT or DOUBLE columns with numbers that have decimals, you can't use =! This problem is common in most computer languages because floating-point values are not exact values: mysql> SELECT * FROM table_name WHERE float_co

RE: select query?

On Fri, 04 May 2001, Roger Karnouk wrote: > Something like this should work: > select ip, mac, count(mac) > from ipmac > group by ip,mac having count(mac) > 1 > hope this helps Yes! Thanks Roger - this is just the thing. group by ip, mac having count(mac) > 1 returns nothing, but if we use gr

RE: select query?

select ip, mac from ipmac group by mac having count(*) > 1; > I have a table with IP/MAC address combinations and I want to track > which MAC address might have used an IP address other than the one > originally assigned. > > Is there some way that I can query my table for duplicate MAC > addres

Re: select query question

n" <[EMAIL PROTECTED]> To: "Jon Rosenberg" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, May 02, 2001 1:48 PM Subject: RE: select query question > create temporary table foo as select patientnumber, count(*) as rcount > from source_table > grou

RE: select query question

create temporary table foo as select patientnumber, count(*) as rcount from source_table group by patientnumber having count(*) > 1; select count(*) from foo; -Original Message- From: Jon Rosenberg [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 02, 2001 1:29 PM To: [EMAIL PROTECTED] Sub

Re: select query syntax

supposed you numbered all rows in the table incrementally in field nid: select mydata from mytable where mod(nid, N)=offset; fill in N and offset (0..n-1) On Sat, 28 Apr 2001 14:26:46 +0400 Igor V Yermakov <[EMAIL PROTECTED]> wrote: > i have 400 rows in my mysql database table > and i wont ge