RE: SQL Question

you'll need to write a recursive routine to call the same sql statement and iterate from the largest value and attenuate that value by 1 each time # file: test.pl been a few years but this should work #initialize your variable that you will attenuate my $global = "9876543210"; #number of rows actu

Re: SQL question: find items tagged with specific tags

Ingo Weiss schrieb: Thanks, Sebastian! I have tried this one before. The problem is that it finds all items the tags of which include EITHER 'blue' OR 'red', not 'blue' AND 'red': oh ... "and" ..., i missred SELECT DISTINCT items.* FROM items INNER JOIN taggings ON item

Re: SQL question: find items tagged with specific tags

Thanks, Sebastian! I have tried this one before. The problem is that it finds all items the tags of which include EITHER 'blue' OR 'red', not 'blue' AND 'red': mysql> SELECT DISTINCT items.title from items inner join taggings on (items.id = taggings.item_id) inner join tags on (tags.id =

Re: SQL question: find items tagged with specific tags

Ingo Weiss schrieb: Hi all, I have an application where items can be tagged. There are three tables 'items', 'taggings' and 'tags' joined together like this: items inner join taggings on (items.id = taggings.item_id) inner join tags on (tags.id = taggings.tag_id) Now I have been struggli

Re: SQL question

Is it just this line I need to change? INNER JOIN url_categories uc ON uc.ID=bt.category_ID; Would it change to something like: INNER JOIN url_categories uc ON CAST(uc.ID as CHAR)=delimit(bt.category_ID) Just guessing! Thanks - that's what I thought. I really don't have much experience with

Re: SQL question

Thanks - that's what I thought. I really don't have much experience with mySQL. If it's not too much trouble, could someone give me a bit more help on how to do that please? Ed. no, those won't match based on just the datatype change.. you will have to define a user defined function to do

Re: SQL question

Thanks, that's interesting. Actually the uc.ID column is still type tinyint as it holds only one number, but are you saying if I change this to varchar my query will work e.g. 15 = 15:17 would work? What is the type of the 'uc.ID' column? If it's varchar, your match will work fine. If it

Re: SQL Question: alternative to crazy left joins?

Thanks Shawn, Believe you me, I share your reaction to this architecture...I had to spend 2 hours coding a ruby script to get the data into the kludgy form needed for the data import (though I do find that thing kind of fun...but it's not the best use of my time on the job). Fortunately the data

Re: SQL Question: alternative to crazy left joins?

eth1 <[EMAIL PROTECTED]> wrote on 03/28/2006 03:04:13 PM: > > Hi All, > > I'm migrating to a contact relationship management system (CRM) for one of > my clients from a proprietary Access database. The CRM system can import > our donor's contact history, but only in a non-normalized format

FW: Re: SQL Question

. mysql> SELECT ABS(2); -> 2 mysql> SELECT ABS(-32); -> 32 This function is safe to use with BIGINT values. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, January 06, 2006 12:19 PM To: Mester József; mysql Subject: [SPAM] - Re:

RE: [SPAM] - Re: SQL Question - Bayesian Filter detected spam

. mysql> SELECT ABS(2); -> 2 mysql> SELECT ABS(-32); -> 32 This function is safe to use with BIGINT values. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, January 06, 2006 12:19 PM To: Mester József; mysql Subject: [SPAM] - Re:

Re: SQL Question

- Original Message - From: "Mester József" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]>; "mysql" Sent: Friday, January 06, 2006 12:07 PM Subject: Re: SQL Question Hy If you know which values are supposed to be negative, wouldn

Re: SQL Question

Hy If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Thank you. Actually my first

Re: SQL Question

Hello. Do you want something similare to this: SELECT SUM(IF(moving like 'Move-',-moved_quantities,moved_quantites)) FROM DB; Have a look here: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Mester József wrote: > Hy > > I want to sum quantites but ther

Re: SQL question

Hi All! I have a MySQL database (I have them using MySql at work for more stuff now!), and the definition is as follows: uid mediumint(6) NOT NULL auto_increment, ym varchar(6) default NULL, fileid varchar(8) default NULL, off char(3) default NULL, PRIMARY KEY (`uid`) TYPE=MyISAM uid is not re

Re: SQL question.... Trying to improve upon my PHP solution.

At 11:53 AM 11/29/2004, you wrote: On 11/29/04 12:27 PM, "mos" <[EMAIL PROTECTED]> wrote: > Mike, >Your solution is way too complicated (it makes my head hurt). > Try this: > > set @n=0; > update tmp set rnd = @n := @n + 1 order by RAND() > > Mike I'll give this a shot. Follow-up question:

Re: SQL question.... Trying to improve upon my PHP solution.

On 11/29/04 1:26 PM, "Rhino" <[EMAIL PROTECTED]> wrote: > I don't think this is a question about SQL at all; I think you already know > how to write the SQL to select, insert, update or delete rows. > > I think that what you really want to know is if there is a more efficient > way to shuffle you

Re: SQL question.... Trying to improve upon my PHP solution.

- Original Message - From: "Mike Zornek" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, November 29, 2004 12:08 PM Subject: SQL question Trying to improve upon my PHP solution. > I have a table of members, about 13,000 rows. > > Each night I need to shuffle the table. I h

Re: SQL question.... Trying to improve upon my PHP solution.

On 11/29/04 12:27 PM, "mos" <[EMAIL PROTECTED]> wrote: > Mike, >Your solution is way too complicated (it makes my head hurt). > Try this: > > set @n=0; > update tmp set rnd = @n := @n + 1 order by RAND() > > Mike I'll give this a shot. Follow-up question: I've had a lot of trouble with

Re: SQL question.... Trying to improve upon my PHP solution.

At 11:08 AM 11/29/2004, you wrote: I have a table of members, about 13,000 rows. Each night I need to shuffle the table. I have a small int column called random_position. Currently I am creating a position list (based on the count of the members), shuffle it, then while iterating through the member

Re: SQL question, SELECT DISTINCT

How about INSERT INTO original_table SELECT MAX(f1), f2, f3 FROM new_table GROUP BY f2, f3; Michael Stephen E. Bacher wrote: I had a similar problem, but my criteria for selecting the value of "f1" was different; it's a date field and I wanted only the rows with the most recent date value in th

Re: SQL question, SELECT DISTINCT

I had a similar problem, but my criteria for selecting the value of "f1" was different; it's a date field and I wanted only the rows with the most recent date value in that field, so only the "latest" of otherwise identical entries got inserted. I ended up doing something like this: create tempor

Re: SQL question, SELECT DISTINCT

Disregard by last message it's a repeat. THANKS for the help! On Mon, 16 Aug 2004 14:32:27 -0400, "Michael Stassen" <[EMAIL PROTECTED]> said: > Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target > table, leave it out of the SELECT, and let it auto-generate IDs. > Something

Re: SQL question, SELECT DISTINCT

On Mon, 16 Aug 2004 13:57:13 -0400, "Michael Stassen" <[EMAIL PROTECTED]> said: > You were perfectly clear. We understand that you only want to test f2 > and f3 for uniqueness. The question is, which of the possible values > of f1 do you want to get. Do you see? For a particular unique f2, f3

Re: SQL question, SELECT DISTINCT

Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target table, leave it out of the SELECT, and let it auto-generate IDs. Something like this: INSERT INTO original_table (f2, f3) SELECT DISTINCT f2, f3 FROM new_table; I did that in the same order as your original message, t

Re: SQL question, SELECT DISTINCT

On Mon, 16 Aug 2004 13:57:13 -0400, "Michael Stassen" <[EMAIL PROTECTED]> said: > You were perfectly clear. We understand that you only want to test f2 > and f3 for uniqueness. The question is, which of the possible values > of f1 do you want to get. Do you see? For a particular unique f2, f3

Re: SQL question, SELECT DISTINCT

You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put

Re: SQL question, SELECT DISTINCT

On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: > Let me see if I can explain it a little betterIf you need to move all > 3 columns to the new table but you only want *1* row where f2 and f3 have > a unique combination of values, how do you want to choose *which* value > of > f1

Re: SQL question, SELECT DISTINCT

Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the

Re: SQL question, SELECT DISTINCT

On Mon, 16 Aug 2004 11:36:32 -0400, [EMAIL PROTECTED] said: > It all depends on which values of f1 you want to ignore. > > f1 f2 f3 > - - -- > val1-1 val2 val3 > val1-2 val2 val3 > val1-3 val2 val3 > > Which value of f1 would you want in your new table? Which one

Re: SQL question, SELECT DISTINCT

It all depends on which values of f1 you want to ignore. f1 f2 f3 - - -- val1-1 val2 val3 val1-2 val2 val3 val1-3 val2 val3 Which value of f1 would you want in your new table? Which ones to ignore? Are there other columns (beyond these 3) to move as well? Yours,

Re: sql question

Maybe something like: Select LIKE pkg_name%, LIKE site_%, version from table group by LIKE pkg_name% , LIKE site_%; but I'm still a beginner. Respectfully, Ligaya Turmelle <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hello all, > > I've a table like this : > > site_1 pkg_name_

RE: SQL question: Finding duplicates

> > row | foo > 1 | a > 2 | c > 3 | b > 4 | c > 5 | a > 6 | d > > the statement would return me rows 1, 2, 4, and 5. > CREATE TEMPORARY TABLE temptable SELECT * FROM test GROUP BY foo HAVING COUNT( * ) >1 ORDER BY foo ASC ; SELECT * FROM test, temptable WHERE test.foo = temptable.f

RE: Sql question

you want to do insert into mytable (column1, column2, column3) (select thiscolumn, '1', now() from anotherTable); > Mysql 3.. > > I can't figure this one out > > > > I need to move data from one mysql table to another > > The hurdle for me is adding additional column values. > > Here is w

Re: Sql question

Keith Schuster <[EMAIL PROTECTED]> wrote: > Mysql 3.. > > I can't figure this one out > > I need to move data from one mysql table to another > > The hurdle for me is adding additional column values. > > Here is what I have. > > > insert into mytable (column1, column 2, column3) > (Select thi

Re: (SQL Question) WHERE NOT IN A LIST

At 12:29 -0500 7/18/03, Tom O'Neill (MySQL User) wrote: Hi, Is there a way I can run a query that will delete all items that are not in a list? For example I have a bunch of records in a table and I want to remove all of them that are not in a comma delimited list that I have recieved from anothe

Re: SQL question

Jake Johnson wrote: Nice approach Bruce, but I too won't have any problems with your case because I am grouping by sku and stock in the sub-query. You're right; you do avoid the problem with the specific sample data I gave you. Sorry about that! But, there are still potential problems because

Re: SQL question

Nice approach Bruce, but I too won't have any problems with your case because I am grouping by sku and stock in the sub-query. Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com Shop Plutoid for the best pric

Re: SQL question

Jake Johnson wrote: This is one quick way to get the newest records of a group if you are grouping by the sku and stock. select stock, sku, qty from table where concat(dt_tm,stock,sku) IN ( select concat(max(dt_tm), stock, sku) from table group by stock, sku ) Another approach (also assuming a cur

Re: SQL question

This is one quick way to get the newest records of a group if you are grouping by the sku and stock. select stock, sku, qty from table where concat(dt_tm,stock,sku) IN ( select concat(max(dt_tm), stock, sku) from table group by stock, sku ) Regards, Jake Johnson [EMAIL PROTECTED] -

Re: SQL question - no CREATE VIEW in mysql

Luca, > In Oracle I could create a view from the initial table, what about MySQL? MySQL will support views as of version 5.1. I cannot find it in the todo (http://www.mysql.com/doc/en/TODO.html) but I saw it elsewhere. Regards, -- Stefan Hinz <[EMAIL PROTECTED]> iConnect GmbH

Re: SQL Question

On 16 Dec 2002, at 23:50, Stefan Hinz, iConnect (Berlin wrote: > > I need to construct a query to find out what customers in the database > > have not > > booked shipments with us. That means there would be no > > records in the shipment table for a given customer id. > > If I get this right, it

Re: SQL Question

rmany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: "Darren Young" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, December 16, 2002 11:24 PM Subject: RE: SQL Question > > I have 2 tables i

RE: SQL Question

I have 2 tables in our MySQL database like this: TABLE: customers +--+---+--+-+-+- ---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+---

re: SQL question

John, Tuesday, November 05, 2002, 1:50:32 AM, you wrote: JJ> I have a callers table and a citylist table. Both tables have a field JJ> 'town' and both tables have a field called 'zipcode'. JJ> The citylist is a list of cities and their zip codes. JJ> citylist.city and citylist.zipcode JJ> The c

Re: SQL question

me sort of mask. BobJ - Original Message - From: "William McCormick" <[EMAIL PROTECTED]> To: "John Almberg" <[EMAIL PROTECTED]>; "Mysql" <[EMAIL PROTECTED]> Sent: Friday, September 27, 2002 10:08 AM Subject: RE: SQL question > I don'

RE: SQL question

I just tried it and it works fine. It doesn't solve my similar problem, which is related to letters coming *before* numbers, but the parser doesn't reject it. Wish I could offer a solution, but I'm not very knowledgable about MySQL internals & config. -Derek > -Original Message- > From:

Re: SQL question

RDER BY > clause. Doesn't even work on an INT field. > > -- John > > > -Original Message- > > From: gerald_clark [mailto:[EMAIL PROTECTED]] > > Sent: Friday, September 27, 2002 9:52 AM > > To: John Almberg > > Cc: Mysql > > Subject: Re: SQ

RE: SQL question

I don't know that there is knowing the way sorts usually work I'd say no. This is my first posting to this group and I am just in the initial phases of supporting MySQL as a backend for out application. If the numbers always precede the letters I would split them off into two separate columns and

Re: SQL question

t: Thursday, September 26, 2002 9:51 PM >>To: Mysql >>Subject: Re: SQL question >> >> >>Try: >> >>SELECT fieldname FROM table ORDER 0+fieldname; >> >> >>Hope it helps. >> >> >>Mihail >> >>- Original Messag

Re: SQL question

002 9:51 PM >>To: Mysql >>Subject: Re: SQL question >> >> >>Try: >> >>SELECT fieldname FROM table ORDER 0+fieldname; >> >> >>Hope it helps. >> >> >>Mihail >> >>- Original Message - >>From: "Jo

RE: SQL question

That gives a syntax error, unfortunately. -- JOhn > -Original Message- > From: Mihail Manolov [mailto:[EMAIL PROTECTED]] > Sent: Thursday, September 26, 2002 9:51 PM > To: Mysql > Subject: Re: SQL question > > > Try: > > SELECT fieldname FROM table ORDER 0+

Re: SQL question

Try: SELECT fieldname FROM table ORDER 0+fieldname; Hope it helps. Mihail - Original Message - From: "John Almberg" <[EMAIL PROTECTED]> To: "Mysql" <[EMAIL PROTECTED]> Sent: Thursday, September 26, 2002 8:04 PM Subject: SQL question > I'm trying to sort a table on a character-type

Re: sql question

create table newTableOne select A.name as groupA, B.name as groupB, C.name as groupC from tableOne, tableTwo as A, tableTwo as B, tableTwo as C where tableOne.groupA = A.ref_id and tableOne.groupB = B.ref_id and tableOne.groupC = C.ref_id; Then re-add your indices and integrity constraints, doubl

RE: sql question

Calculating a connectivity matrix from this sort of data is not what the relational database is best designed to do, but it's a common problem - it applies to family tree databases, used-on lists and so on. (What you're actually wanting to do, if I remember the jargon correctly from school, is cal

Re: SQL Question...

gt; Sent: Sunday, April 07, 2002 10:29 AM Subject: Re: SQL Question... > Thanks, My book that I have been studying from, only show commands and no > examples. MySQL web had want I wanted at the very top. > > I have finish my page now. > > Chuck > > on 4/6/02 11:46 PM, Georg R

Re: SQL Question...

Thanks, My book that I have been studying from, only show commands and no examples. MySQL web had want I wanted at the very top. I have finish my page now. Chuck on 4/6/02 11:46 PM, Georg Richter at [EMAIL PROTECTED] wrote: > On Sunday, 7. April 2002 05:56, Chuck \"PUP\" Payne wrote: > Hi, >>

Re: SQL Question...

On Sat, 06 Apr 2002 22:56:11 -0500, Chuck \"PUP\" Payne wrote: >Hello again, > >I am trying to set up a SQL statement using NOW(), what I am wanting >to do >is to use NOW() then do a search on any date less than 7 days. >Before I get >several e-mails asking why. I am trying to a news base database

Re: SQL Question...

On Sunday, 7. April 2002 05:56, Chuck \"PUP\" Payne wrote: Hi, > > I am trying to set up a SQL statement using NOW(), what I am wanting to do > is to use NOW() then do a search on any date less than 7 days. Before I get > several e-mails asking why. I am trying to a news base database for the > co

Re: SQL question -- can this be done?

t; Sent: Saturday, March 09, 2002 1:26 PM Subject: Re: SQL question -- can this be done? > I asked almost the exact same question just a few days ago. > > As of MySQL 3.23.2 you can use COUNT and DISTINCT together: > > SELECT COUNT(DISTINCT ipAddress) ... > > On 9/3/02 at 1:1

Re: SQL question -- can this be done? FOUND IT!

Aha! Figured it out... SELECT COUNT(DISTINCT ipAddress) FROM table WHERE initDate >= DATE_ADD(now(), INTERVAL -60 MINUTE) AND id = [whatever id I'm looking for...]; Works semantically like it sounds. Probably the only variation I didn't try before posting! (always works that way...) Thanks, --j

Re: SQL question -- can this be done?

I asked almost the exact same question just a few days ago. As of MySQL 3.23.2 you can use COUNT and DISTINCT together: SELECT COUNT(DISTINCT ipAddress) ... On 9/3/02 at 1:16 pm, Jeff Kilbride <[EMAIL PROTECTED]> wrote: > I have a table with 3 fields: > > initDate datetime not null > id int u

Re: SQL question

From: Lance Rochelle <[EMAIL PROTECTED]> > SQL question which I am new to. > How would I count the number of times a specific entry is in field. For > instance I have a table that has the following two fields select count(*) as number, hostname from your_table group by hostname; --- Rodney

RE: SQL question

select count(hostname), hostName from tableName group by hostname Cal http://www.calevans.com -Original Message- From: Lance Rochelle [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 8:57 PM To: [EMAIL PROTECTED] Subject: SQL question SQL question which I am new to. How w

RE: SQL question

> SQL question which I am new to. > How would I count the number of times a specific entry is in field. For > instance I have a table that has the following two fields > numberhostname > 1 10.1.1.1 > 2 10.1.1.2 > 3 10.1.1.3 > 4

Re: SQL question

At 9:57 PM -0400 9/4/01, Lance Rochelle wrote: >SQL question which I am new to. >How would I count the number of times a specific entry is in field. For >instance I have a table that has the following two fields >numberhostname >1 10.1.1.1 >2 10.1.1.2 >3

RE: SQL question

Dave Rigby wrote: > Hi, > > Could you create a linked list of the links- i.e. have a 'Next' field, > which contains the ID of the next URL in order. You will also need a > field to store if the link is the first (i.e. root) of the list. Then > you can just iterate though the rows, using the nex

Re: SQL question

Hi, Could you create a linked list of the links- i.e. have a 'Next' field, which contains the ID of the next URL in order. You will also need a field to store if the link is the first (i.e. root) of the list. Then you can just iterate though the rows, using the next field to tell you which URL is

Re: SQL question (set operator)

"Marie-Christine Fauvet" <[EMAIL PROTECTED]> wrote: > I'd like to program in the SQL language supported by MySQL the following > query: > > select R.A, R.B from R > minus > select S.A, S.B from S > > It doesn't work (syntax error, set operators are not supported). > So, I've tried: > > select

Re: SQL question

>I'm having a lot of difficulty trying to figure this out. I have a table >with a list of projects that I would like to arrange and view as a tree. >This is my table: > >++--+--+-+-++ >| Field | Type | Null | Key | Default | Ext

RE: SQL question

I don't think it's possible purely with SQL using MySQL. I tried researching it just a few days ago, and found Oracle supports a clause CONNECT BY ... PRIOR (more info at http://www.arsdigita.com/books/sql/trees.html) which does this for you, but it's still in the MySQL todo list (http://www.mysql