Odd select question
I seem to recall a SQL select syntax along these lines: SELECT col1, col2 WHERE col1 IN (set) Is this or similar syntax in MySQL or is my dotage coming upon me Thanks in advance, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Odd select question
There is! But I would definitely check the online doc for further and more complete info. Cheers! Claudio On Oct 12, 2009 9:47 PM, Bruce Ferrell bferr...@baywinds.org wrote: I seem to recall a SQL select syntax along these lines: SELECT col1, col2 WHERE col1 IN (set) Is this or similar syntax in MySQL or is my dotage coming upon me Thanks in advance, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: Odd select question
that's legal where set is a comma-delimited list of items of the same datatype as col1 On Mon, Oct 12, 2009 at 2:41 PM, Bruce Ferrell bferr...@baywinds.orgwrote: I seem to recall a SQL select syntax along these lines: SELECT col1, col2 WHERE col1 IN (set) Is this or similar syntax in MySQL or is my dotage coming upon me Thanks in advance, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
update select question
I have the following query... SELECT c.NLCID, n.publishdate FROM newsletter n JOIN newslettersection s using (NLID) JOIN newslettercontent c using(NLCID) WHERE contenttype = 1 AND n.publishdate AND c.`timestamp` = '-00-00 00:00:00' I want to run an update on newslettercontent and set its timestamp column to be the publishdate from the newsletter table using the join rules in that query. Is there a way to do that in a query? I should probably just write a quick script to do it since I could probably do that in less time than I have spent trying to figure out a query to do it. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update select question
Chris W schrieb: I have the following query... SELECT c.NLCID, n.publishdate FROM newsletter n JOIN newslettersection s using (NLID) JOIN newslettercontent c using(NLCID) WHERE contenttype = 1 AND n.publishdate AND c.`timestamp` = '-00-00 00:00:00' I want to run an update on newslettercontent and set its timestamp column to be the publishdate from the newsletter table using the join rules in that query. Is there a way to do that in a query? yes, you can, see multi-table update http://dev.mysql.com/doc/refman/5.0/en/update.html -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update select question
update newslettercontent c set c.timestamp= (select n.publishdate from newsletter n where n.NLCID= c.NLCID); This should work. On 4/16/08, Chris W [EMAIL PROTECTED] wrote: I have the following query... SELECT c.NLCID, n.publishdate FROM newsletter n JOIN newslettersection s using (NLID) JOIN newslettercontent c using(NLCID) WHERE contenttype = 1 AND n.publishdate AND c.`timestamp` = '-00-00 00:00:00' I want to run an update on newslettercontent and set its timestamp column to be the publishdate from the newsletter table using the join rules in that query. Is there a way to do that in a query? I should probably just write a quick script to do it since I could probably do that in less time than I have spent trying to figure out a query to do it. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A sql/select question.
table users iduser_iduser_name 1 M11 Shirley 2 M12 Bruce 3 M13 Fred 4 M14 Albert 5 M15 Elizabeth 6 T11 Helen 7 T12 Tracy 8 T13 Charles 9 T14 Jack 10 T15 Ann table job_records job_idsubmit_user_idsubmit_timefixed_user_id fixed_time SC12033214495468 T13 1203321449 M11 1203321763 SC12033215980303 T13 1203321598 M11 1203321788 SC12033216636547 T12 1203321663 M11 1203321796 SC12033216729280 T14 1203321672 M13 1203321803 SC12033216819810 T15 1203321681 M15 1203321809 SC12033216898223 T11 1203321689 M13 1203321816 How to select out data with below format? job_idsubmit_user_idsubmit_timefixed_user_id fixed_time SC12033214495468 Charles 1203321449 Shirley 1203321763 SC12033215980303 Charles 1203321598 Shirley 1203321788 SC12033216636547 Tracy 1203321663 Shirley 1203321796 SC12033216729280 Jack 1203321672 Fred 1203321803 SC12033216819810 Ann 1203321681 Elizabeth 1203321809 SC12033216898223 Helen 1203321689 Fred 1203321816 select t1.job_id, t2.user_name, t1.submit_time, t1.fixed_user_id, t1.fixed_time from job_records as t1 left join users as t2 on (t1.submit_user_id = t2.user_id); The output is, job_iduser_namesubmit_timefixed_user_idfixed_time SC12033214495468 Charles 1203321449 M11 1203321763 SC12033215980303 Charles 1203321598 M11 1203321788 SC12033216636547 Tracy 1203321663 M11 1203321796 SC12033216729280 Jack 1203321672 M13 1203321803 SC12033216819810 Ann 1203321681 M15 1203321809 SC12033216898223 Helen 1203321689 M13 1203321816 Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A sql/select question.
2008/2/18 King C. Kwok [EMAIL PROTECTED]: table users iduser_iduser_name 1 M11 Shirley 2 M12 Bruce 3 M13 Fred 4 M14 Albert 5 M15 Elizabeth 6 T11 Helen 7 T12 Tracy 8 T13 Charles 9 T14 Jack 10 T15 Ann table job_records job_idsubmit_user_idsubmit_timefixed_user_id fixed_time SC12033214495468 T13 1203321449 M11 1203321763 SC12033215980303 T13 1203321598 M11 1203321788 SC12033216636547 T12 1203321663 M11 1203321796 SC12033216729280 T14 1203321672 M13 1203321803 SC12033216819810 T15 1203321681 M15 1203321809 SC12033216898223 T11 1203321689 M13 1203321816 How to select out data with below format? job_idsubmit_user_idsubmit_timefixed_user_id fixed_time SC12033214495468 Charles 1203321449 Shirley 1203321763 SC12033215980303 Charles 1203321598 Shirley 1203321788 SC12033216636547 Tracy 1203321663 Shirley 1203321796 SC12033216729280 Jack 1203321672 Fred 1203321803 SC12033216819810 Ann 1203321681 Elizabeth 1203321809 SC12033216898223 Helen 1203321689 Fred 1203321816 select t1.job_id, t2.user_name, t1.submit_time, t1.fixed_user_id, t1.fixed_time from job_records as t1 left join users as t2 on (t1.submit_user_id = t2.user_id); The output is, job_iduser_namesubmit_timefixed_user_idfixed_time SC12033214495468 Charles 1203321449 M11 1203321763 SC12033215980303 Charles 1203321598 M11 1203321788 SC12033216636547 Tracy 1203321663 M11 1203321796 SC12033216729280 Jack 1203321672 M13 1203321803 SC12033216819810 Ann 1203321681 M15 1203321809 SC12033216898223 Helen 1203321689 M13 1203321816 Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] First off it is usally a good idea to explain exactly what is not working for you. The 'below format' is pretty ambiguous. It looks like all you need to do in order to get what you want is to join on the user table again and alias a few columns. select t1.job_id, t2.user_name as `submit_user_id`, t1.submit_time, t3.user_name as `fixed_user_id`, t1.fixed_time from job_records as t1 left join users as t2 on t1.submit_user_id = t2.user_id left join users as t3 on t1.fixed_user_id = t3.user_id; Is that what you are going for? If not, then please explain in a bit better detail what is problematic. -- Rob Wultsch
Re: A sql/select question.
Hi Rob, That is just what I need. I can't use 'join' very well yet. Thank you very much. -- King Kwok -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select question
I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I only want to be able to select item 2 rather than both 1 and 2. How do I do that? Thanks Mat
Re: Select question
Matthew Stuart schrieb: I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I only want to be able to select item 2 rather than both 1 and 2. How do I do that? Hi Mat, TOP 2 is not MySQL? However, MySQL knows LIMIT [1] which is more powerful, try: SELECT * FROM Content ORDER BY ContentID DESC LIMIT 1,1 regards -Ralf [1]: http://dev.mysql.com/doc/refman/5.0/en/select.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select question
Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Matthew Stuart [mailto:[EMAIL PROTECTED] Sent: Thursday, October 25, 2007 6:55 AM To: MySQL email support Subject: Select question I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I only want to be able to select item 2 rather than both 1 and 2. How do I do that? Thanks Mat [JS] What does adding LIMIT 2,1 do? I'm not sure what SELECT top 2 * means. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select question
I have three tables, all of which have a 'name' column. If I do: select table1.*, table2.*, table3.* from I'll end up with a result set that has three 'name' fields, but no way to distinguish which table the field belongs to. I know I can select individual columns like: select table1.name as foo, table2.name as bar ... but I need all the columns from each table and that will be very tedious. Is there a way to set a prefix for each table so that the results come out like tablename.column? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select question
Erich, Is there a way to set a prefix for each table so that the results come out like tablename.column? Use a scripting or application language to automate parameterise query generation. SQL is just a partial computing language. PB - Erich C. Beyrent wrote: I have three tables, all of which have a 'name' column. If I do: select table1.*, table2.*, table3.* from I'll end up with a result set that has three 'name' fields, but no way to distinguish which table the field belongs to. I know I can select individual columns like: select table1.name as foo, table2.name as bar ... but I need all the columns from each table and that will be very tedious. Is there a way to set a prefix for each table so that the results come out like tablename.column? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar(5) and select question
Hi,
2006/6/29, Joerg Bruehe [EMAIL PROTECTED]:
Hi Pooly, all,
Pooly wrote:
Hi,
I stumbled on one issue yesterday which took me some time to figure out.
the table is :
create table tt ( PCname varchar(5) not null default '');
insert into tt values ('Centaure');
So, by mistake I inserted names which were too long for the field, but
then I tried to do queries on this particular value :
Select * from tt WHERE PCname='Centaure';
which returns obviously no result. How comes the 'Centaure' in the
SELECT is not cut has it is in the INSERT ?
The rules of SQL allow you to compare even such values which you could
not assign.
So you may compare values of character string columns of different
length, and the SQL specification is that the shorter string is
effectively right-padded with blanks before they are compared
(in other words: trailing blanks are insignificant).
Ok, thanks for the explanation !
--
http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
varchar(5) and select question
Hi,
I stumbled on one issue yesterday which took me some time to figure out.
the table is :
create table tt ( PCname varchar(5) not null default '');
insert into tt values ('Centaure');
So, by mistake I inserted names which were too long for the field, but
then I tried to do queries on this particular value :
Select * from tt WHERE PCname='Centaure';
which returns obviously no result. How comes the 'Centaure' in the
SELECT is not cut has it is in the INSERT ?
--
http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar(5) and select question
On Thursday 29 June 2006 08:18 am, Pooly wrote:
Hi,
I stumbled on one issue yesterday which took me some time to figure out.
the table is :
create table tt ( PCname varchar(5) not null default '');
insert into tt values ('Centaure');
(5) indicates a display width.
So, by mistake I inserted names which were too long for the field, but
then I tried to do queries on this particular value :
Select * from tt WHERE PCname='Centaure';
which returns obviously no result. How comes the 'Centaure' in the
SELECT is not cut has it is in the INSERT ?
SELECT probably doesn't care about the display width with regards to WHERE
clauses, it just wants to see if the field matches and be done with it. The
real answer to your issue would be to increase the display width, possibly
through ALTER TABLE.
--
http://www.w-fenec.org/
--
Chris White
PHP Programmer/DBlame Me
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar(5) and select question
Hi Pooly, all,
Pooly wrote:
Hi,
I stumbled on one issue yesterday which took me some time to figure out.
the table is :
create table tt ( PCname varchar(5) not null default '');
insert into tt values ('Centaure');
So, by mistake I inserted names which were too long for the field, but
then I tried to do queries on this particular value :
Select * from tt WHERE PCname='Centaure';
which returns obviously no result. How comes the 'Centaure' in the
SELECT is not cut has it is in the INSERT ?
The rules of SQL allow you to compare even such values which you could
not assign.
So you may compare values of character string columns of different
length, and the SQL specification is that the shorter string is
effectively right-padded with blanks before they are compared
(in other words: trailing blanks are insignificant).
HTH,
Joerg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question - query for records over a series of dates
I think you might be one to something here... is there such a thing as a while loop in MySQL? i.e. can I fill a table with data via a MySQL query? I guess I could do it via PHP... I could create a temp table with one column of dates for the range I am looking for and then LEFT JOIN my log table and match the dates. Having a dedicated table would work but would be kind of a waste of space / resources. These queries will not be run that often. Dan T On Aug 16, 2005, at 10:26 PM, Michael Stassen wrote: Add a table: CREATE TABLE `dates` (`date` DATE, UNIQUE KEY `date_idx` (`date`) ); Insert one row into dates for each day. Now you can use something like this: SELECT dates.date, COUNT(*) as hits FROM dates LEFT JOIN table on dates.date = DATE(table.date_impression) WHERE dates.date BETWEEN '2005-08-12' AND '2005-08-16' GROUP BY dates.date; Populating the dates table initially is a small (one-time) pain. You could keep it filled with a once-a-day script to insert the current date. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question - query for records over a series of dates
Dan Tappin wrote: I think you might be one to something here... is there such a thing as a while loop in MySQL? i.e. can I fill a table with data via a MySQL query? I guess I could do it via PHP... I could create a temp table with one column of dates for the range I am looking for and then LEFT JOIN my log table and match the dates. Having a dedicated table would work but would be kind of a waste of space / resources. These queries will not be run that often. Dan T No while loop, but this can be done in mysql, so long as you already have a table with enough rows. For example, to create and fill a dates table, starting with 1995-01-01 and ending with 2005-12-31: # create the table with 2 extra columns, one of which is auto_increment: CREATE TABLE dates (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, date DATE, junk INT, UNIQUE date_idx (date) ); # add enough rows to the table to cover the desired date range: INSERT INTO dates (junk) SELECT id FROM big_table LIMIT 4018; # use the auto_increment generated ids as offsets from the start # date to fill the date column: UPDATE DATES SET date = '1994-12-31' + INTERVAL id DAY; # drop the now useless, extra columns: ALTER TABLE dates DROP COLUMN id, DROP COLUMN junk; Voila, dates has one row for each day from 1995-01-01 to 2005-12-31. This example MyISAM table with 10 years worth of rows in it takes up 63,461 bytes on my disk, including the index. If that's a waste of space / resources, I think it's time to buy more disk. You certainly could create such a table on the fly, with just the rows you need, as a temporary table, but that will be relatively slow compared to simply using a pre-existing, dedicated table to satisfy your queries. Besides, at that small size, I don't really see the downside. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT question - query for records over a series of dates
I have a table full of data... a log of sorts. Each row has a timestamp. I want to generate some reports based on this data. For example I want a COUNT(*) of the rows for each day for the past week, 30 days, 12 months etc. I have no problem generating the query but I am stuck on a creative way to deal with the periods with no data. For example: SELECT COUNT(*) as hits, DATE(date_impression) as date FROM table GROUP BY date +---+-+ |hits |date | +---+-+ |39 | 2005-08-12 | |27 | 2005-08-13 | |38 | 2005-08-15 | |28 | 2005-08-16 | +---+-+ Now the problem is that there could be days with no data (the 14th in my example). Ideally I want to show the last 7 days (or what ever period I want) and show the COUNT(*) including the days with no data like... +---+-+ |hits |date | +---+-+ |39 | 2005-08-12 | |27 | 2005-08-13 | | 0 | 2005-08-14 | |38 | 2005-08-15 | |28 | 2005-08-16 | +---+-+ Now I can manipulate the data afterwards (i.e. look for empty rows via PHP where this is going to end up...) but it would be much easier to get the data direct from MySQL. Is there any (easy) way to do this in MySQL? Dan T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question - query for records over a series of dates
Dan Tappin wrote: I have a table full of data... a log of sorts. Each row has a timestamp. I want to generate some reports based on this data. For example I want a COUNT(*) of the rows for each day for the past week, 30 days, 12 months etc. I have no problem generating the query but I am stuck on a creative way to deal with the periods with no data. For example: SELECT COUNT(*) as hits, DATE(date_impression) as date FROM table GROUP BY date +---+-+ |hits |date | +---+-+ |39 | 2005-08-12 | |27 | 2005-08-13 | |38 | 2005-08-15 | |28 | 2005-08-16 | +---+-+ Now the problem is that there could be days with no data (the 14th in my example). Ideally I want to show the last 7 days (or what ever period I want) and show the COUNT(*) including the days with no data like... +---+-+ |hits |date | +---+-+ |39 | 2005-08-12 | |27 | 2005-08-13 | | 0 | 2005-08-14 | |38 | 2005-08-15 | |28 | 2005-08-16 | +---+-+ Now I can manipulate the data afterwards (i.e. look for empty rows via PHP where this is going to end up...) but it would be much easier to get the data direct from MySQL. Is there any (easy) way to do this in MySQL? Dan T Not that I know of. When I have to do things like this, I write a script which does a separate query per day. Unfortunately PHP's date functions are pretty horrible, so I also use mysql as a calculator to find the next day in my loop. This is terribly inefficient, but very easy, and our server is high powered and under no load :) Maybe someone has a better idea though. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question - query for records over a series of dates
Dan Tappin wrote: I have a table full of data... a log of sorts. Each row has a timestamp. I want to generate some reports based on this data. For example I want a COUNT(*) of the rows for each day for the past week, 30 days, 12 months etc. I have no problem generating the query but I am stuck on a creative way to deal with the periods with no data. For example: SELECT COUNT(*) as hits, DATE(date_impression) as date FROM table GROUP BY date +---+-+ |hits |date | +---+-+ |39 | 2005-08-12 | |27 | 2005-08-13 | |38 | 2005-08-15 | |28 | 2005-08-16 | +---+-+ Now the problem is that there could be days with no data (the 14th in my example). Ideally I want to show the last 7 days (or what ever period I want) and show the COUNT(*) including the days with no data like... +---+-+ |hits |date | +---+-+ |39 | 2005-08-12 | |27 | 2005-08-13 | | 0 | 2005-08-14 | |38 | 2005-08-15 | |28 | 2005-08-16 | +---+-+ Now I can manipulate the data afterwards (i.e. look for empty rows via PHP where this is going to end up...) but it would be much easier to get the data direct from MySQL. Is there any (easy) way to do this in MySQL? Dan T Add a table: CREATE TABLE `dates` (`date` DATE, UNIQUE KEY `date_idx` (`date`) ); Insert one row into dates for each day. Now you can use something like this: SELECT dates.date, COUNT(*) as hits FROM dates LEFT JOIN table on dates.date = DATE(table.date_impression) WHERE dates.date BETWEEN '2005-08-12' AND '2005-08-16' GROUP BY dates.date; Populating the dates table initially is a small (one-time) pain. You could keep it filled with a once-a-day script to insert the current date. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and select question
Hi, Look at select ... for update here : http://dev.mysql.com/doc/mysql/en/innodb-locking-reads.html Mathias Selon $B2 9bJv(B [EMAIL PROTECTED]: i use this mail first . i have a problem in under sql program: UPDATE NGLDENHDT SET EDT_HUK_FLG = :EDT_HUK_FLG WHERE KAI_CDE = :KAI_CDE AND EDT_NUM = (SELECT MAX(EDT_NUM) FROM NGLDENHDT WHERE KAI_CDE = :KAI_CDE_T1 AND EDT_NUM != ''); the error message was: You can't specify target table 'NGLDENHDT' for update in FROM clause what can i do ,help me ? thank you very much!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and select question
$B2+9bJv(B wrote: (B i use this mail first . (B (B i have a problem in under sql program: (B (B (B UPDATE (B NGLDENHDT (B SET (B EDT_HUK_FLG = :EDT_HUK_FLG (B WHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (BFROM NGLDENHDT (BWHERE KAI_CDE = :KAI_CDE_T1 (BAND EDT_NUM != ''); (B (B the error message was: (B (B You can't specify target table 'NGLDENHDT' for update in FROM (B clause (B (B (BYou can't update and select with a subquery at the same time the very (Bsame table. (Btry a 2-fold action : (BBEGIN; (BSELECT @val:= MAX(EDT_NUM) (B FROM NGLDENHDT (BWHERE KAI_CDE = :KAI_CDE_T1 (BAND EDT_NUM != ''; (B UPDATE (B NGLDENHDT (B SET (B EDT_HUK_FLG = :EDT_HUK_FLG (B WHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = @val; (BCOMMIT; (B (B (B what can i do ,help me ? (B (B (B thank you very much!! (B (B (B (B-- (BPhilippe Poelvoorde (BCOS Trading Ltd. (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and select question
From: "$B2+9bJv(B" (B (B UPDATE (B NGLDENHDT (B SET (B EDT_HUK_FLG = :EDT_HUK_FLG (B WHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (BFROM NGLDENHDT (BWHERE KAI_CDE = :KAI_CDE_T1 (BAND EDT_NUM != ''); (B (B the error message was: (B (B You can't specify target table 'NGLDENHDT' for update in FROM (B (BFor a small table you could use: (B (BUPDATE (BNGLDENHDT (BSET (BEDT_HUK_FLG = :EDT_HUK_FLG (BWHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM != '') (BORDER BY (B EDT_NUM DESC (BLIMIT 1; (B (BThis would sort the table on EDT_NUM with the biggest number first and then (Bonly update a single record. (B (BRegards, Jigal. (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update and select question
i use this mail first . (B (Bi have a problem in under sql program: (B (B (BUPDATE (BNGLDENHDT (BSET (BEDT_HUK_FLG = :EDT_HUK_FLG (BWHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (B FROM NGLDENHDT (B WHERE KAI_CDE = :KAI_CDE_T1 (B AND EDT_NUM != ''); (B (Bthe error message was: (B (BYou can't specify target table 'NGLDENHDT' for update in FROM (Bclause (B (B (Bwhat can i do ,help me ? (B (B (B thank you very much!!
update and select question
(Bi use this mail first . (B (Bi have a problem in under sql program: (B (B (BUPDATE (BNGLDENHDT (BSET (BEDT_HUK_FLG = :EDT_HUK_FLG (BWHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (B FROM NGLDENHDT (B WHERE KAI_CDE = :KAI_CDE_T1 (B AND EDT_NUM != ''); (B (Bthe error message was: (B (BYou can't specify target table 'NGLDENHDT' for update in FROM (Bclause (B (B (Bwhat can i do ,help me ? (B (B (B thank you very much!!
FW: update and select question
http://dev.mysql.com/doc/mysql/en/update.html (B (BSee the last line on the page. You cannot, in the most current stable (Bversion of MySQL, SELECT from the table you are trying to UPDATE. (B (BJ.R. (B (B-Original Message- (BFrom: $B2+9bJv(B [mailto:[EMAIL PROTECTED] (BSent: Tuesday, April 26, 2005 11:26 PM (BTo: mysql@lists.mysql.com (BSubject: update and select question (B (B (Bi use this mail first . (B (Bi have a problem in under sql program: (B (B (BUPDATE (BNGLDENHDT (BSET (BEDT_HUK_FLG = :EDT_HUK_FLG (BWHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (B FROM NGLDENHDT (B WHERE KAI_CDE = :KAI_CDE_T1 (B AND EDT_NUM != ''); (B (Bthe error message was: (B (BYou can't specify target table 'NGLDENHDT' for update in FROM (Bclause (B (B (Bwhat can i do ,help me ? (B (B (B thank you very much!! smime.p7s Description: S/MIME cryptographic signature
SELECT question
I know to most of you this will seem like a mundane question, but I was hoping someone can tell me how to select the last record in a table that meets certain criteria. Like to see who hosted the last party in CHicago. SELECT host FROM PARTY WHERE city=chicago; PARTY ID |host | city | st | 237|1256 | Chicago | IL | 244|945 | Chicago | IL | 355|2987 | Boston | MA | I need a query that would give me the one with highest ID i.e. host no. 945? Thanks for any help. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT question
[snip] I know to most of you this will seem like a mundane question, but I was hoping someone can tell me how to select the last record in a table that meets certain criteria. Like to see who hosted the last party in CHicago. SELECT host FROM PARTY WHERE city=chicago; PARTY ID |host | city | st | 237|1256 | Chicago | IL | 244|945 | Chicago | IL | 355|2987 | Boston | MA | I need a query that would give me the one with highest ID i.e. host no. 945? [/snip] SELECT host FROM party WHERE city = 'Chicago' ORDER BY ID DESC LIMIT 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question
ORDER BY host DESC LIMIT 1 On Wed, 29 Dec 2004 14:18:02 -0800 (PST), Richard Reina [EMAIL PROTECTED] wrote: I know to most of you this will seem like a mundane question, but I was hoping someone can tell me how to select the last record in a table that meets certain criteria. Like to see who hosted the last party in CHicago. SELECT host FROM PARTY WHERE city=chicago; PARTY ID |host | city | st | 237|1256 | Chicago | IL | 244|945 | Chicago | IL | 355|2987 | Boston | MA | I need a query that would give me the one with highest ID i.e. host no. 945? Thanks for any help. Richard -- 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]
Select Question
Is there a way to convert the following result set: id f2 --- --- 1 b 1 c to: id f2 -- -- 1 b,c Using a select or any other functions? Basically, I want to put f2 fields together when ids are the same. Thanks, -Jalil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Question
If you have mysql 4.1, you can use GROUP_CONCAT(). SELECT id, GROUP_CONCAT(f2) FROM yourtable GROUP BY id; See the manual for details http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html. Michael Feghhi, Jalil wrote: Is there a way to convert the following result set: id f2 --- --- 1 b 1 c to: id f2 -- -- 1 b,c Using a select or any other functions? Basically, I want to put f2 fields together when ids are the same. Thanks, -Jalil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Question
Check out the GROUP_CONCAT() function, see if this help: http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Feghhi, Jalil [EMAIL PROTECTED] wrote on 10/05/2004 11:20:21 AM: Is there a way to convert the following result set: id f2 --- --- 1 b 1 c to: id f2 -- -- 1 b,c Using a select or any other functions? Basically, I want to put f2 fields together when ids are the same. Thanks, -Jalil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Question
If your MySQL version is 4.1 or later, you could try GROUP_CONCAT the query should be ( not tested ): select id,GROUP_CONCAT(f2) from tablename group by id regards, Giulio Il giorno 05/ott/04, alle 17:20, Feghhi, Jalil ha scritto: Is there a way to convert the following result set: id f2 --- --- 1 b 1 c to: id f2 -- -- 1 b,c Using a select or any other functions? Basically, I want to put f2 fields together when ids are the same. Thanks, -Jalil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question
Hi Roger, I took the suggestion from Brad Eacker and use BETWEEN and now works without problem. However, I decided to do a couple more tests and what I found was that the problem occurs on MySQL version 4.0.18-standard using InnoDB on Linux but does not occur on Mac OS X using the same MySQL version. Andre On 9/20/04 5:52 PM, Roger Baklund [EMAIL PROTECTED] wrote: * Andre Matos I am performing a SELECT and I am getting 0 rows when I run the SELECT direct in the MySQL database and getting 1 when I run using PHP. This is my select: SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND (ScanStatusID 90 OR ScanStatusID 98); Looks ok. I realized latter analyzing this select that I made a mistake using OR at this point: (ScanStatusID 90 OR ScanStatusID 98), it should be AND. Are you sure about that? However, in both cases, I am still getting 0 rows from the database, which is correct. My problem is using the PHP to run the SELECT, if I use OR using the PHP, I got 1 as a result, and if I use AND I got 0 as a result. This is correct, if you have one record with ScanStatusID in the range 90-98. Is anyone can tell me what is going on? You seem to be misinterpreting how logical expressions work. A SQL select statement is a description of the (sub-)set of data you wish to retrieve from the database. This description often includes a WHERE clause, describing wanted records, which again often includes a logical expression. The expression is built up by operands and operators. The logical operators relevant in SQL is AND, OR and NOT. NOT is a negation, this operator takes one operand, the the result is the opposite of the operand. NOT true is false, and NOT false is true. The other two operators, AND and OR, need two operands, one on each side. For the AND operator, BOTH sides of the operator must be true for this part of the expression to be true. For the OR operator, ANY of the sides of the operator must be true for that part of the expression to be true. So, for your expression above, you can not say ...ScanStatusID 90 AND ScanStatusID 98..., because ScanStatusID can not be below 90 AND above 98. ScanStatusID is a single number, it can be below 90 OR above 98. Not both at the same time. -- Roger -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT question
Hi List, I am performing a SELECT and I am getting 0 rows when I run the SELECT direct in the MySQL database and getting 1 when I run using PHP. This is my select: SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND (ScanStatusID 90 OR ScanStatusID 98); I realized latter analyzing this select that I made a mistake using OR at this point: (ScanStatusID 90 OR ScanStatusID 98), it should be AND. However, in both cases, I am still getting 0 rows from the database, which is correct. My problem is using the PHP to run the SELECT, if I use OR using the PHP, I got 1 as a result, and if I use AND I got 0 as a result. Is anyone can tell me what is going on? Thanks in advance for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question
* Andre Matos I am performing a SELECT and I am getting 0 rows when I run the SELECT direct in the MySQL database and getting 1 when I run using PHP. This is my select: SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND (ScanStatusID 90 OR ScanStatusID 98); Looks ok. I realized latter analyzing this select that I made a mistake using OR at this point: (ScanStatusID 90 OR ScanStatusID 98), it should be AND. Are you sure about that? However, in both cases, I am still getting 0 rows from the database, which is correct. My problem is using the PHP to run the SELECT, if I use OR using the PHP, I got 1 as a result, and if I use AND I got 0 as a result. This is correct, if you have one record with ScanStatusID in the range 90-98. Is anyone can tell me what is going on? You seem to be misinterpreting how logical expressions work. A SQL select statement is a description of the (sub-)set of data you wish to retrieve from the database. This description often includes a WHERE clause, describing wanted records, which again often includes a logical expression. The expression is built up by operands and operators. The logical operators relevant in SQL is AND, OR and NOT. NOT is a negation, this operator takes one operand, the the result is the opposite of the operand. NOT true is false, and NOT false is true. The other two operators, AND and OR, need two operands, one on each side. For the AND operator, BOTH sides of the operator must be true for this part of the expression to be true. For the OR operator, ANY of the sides of the operator must be true for that part of the expression to be true. So, for your expression above, you can not say ...ScanStatusID 90 AND ScanStatusID 98..., because ScanStatusID can not be below 90 AND above 98. ScanStatusID is a single number, it can be below 90 OR above 98. Not both at the same time. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question
Andre Matos writes: SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND (ScanStatusID 90 OR ScanStatusID 98); I realized latter analyzing this select that I made a mistake using OR at this point: (ScanStatusID 90 OR ScanStatusID 98), it should be AND. The second rendition (ScanStatusID 90 AND ScanStatusID 98) will return no rows every time. Since ScanStatusID can not be less than 90 and greater than 98 at the same time :) Something that I've found to improve readability of these kinds of tests is to use BETWEEN(a, b) And if you need to exclude a range the use of NOT BETWEEN(a, b). This way it becomes obvious what you are looking for. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
basic select question...
hi... a basic select question/issue that should work mysql select * from instructorTBL; ++--++-+---++ | name | universityID | deptID | email | phone | ID | ++--++-+---++ | dr. smith1 |1 | 2 | [EMAIL PROTECTED] | | 1 | ++--++-+---++ 1 row in set (0.00 sec) why don't any of the following work...??? as you can see, there is information in the tbl... mysql indicates it should work... what simple mistake am i missing..?? this is getting embarrassing... mysql select instructorTBL.ID - from instructorTBL - where instructorTBL.name = dr smith1; Empty set (0.00 sec) mysql select ID - from instructorTBL - where instructorTBL.name = dr smith1; Empty set (0.01 sec) mysql select * - from instructorTBL - where instructorTBL.name = dr smith1; Empty set (0.00 sec) thanks... -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic select question...
Possibly because, if there is no typo, your table's name field is: dr. smith1 (note the period after dr) But your query is looking for dr smith1 Does that fix it for you? Wes On Jul 5, 2004, at 4:18 PM, bruce wrote: hi... a basic select question/issue that should work mysql select * from instructorTBL; ++--++-+---++ | name | universityID | deptID | email | phone | ID | ++--++-+---++ | dr. smith1 |1 | 2 | [EMAIL PROTECTED] | | 1 | ++--++-+---++ 1 row in set (0.00 sec) why don't any of the following work...??? as you can see, there is information in the tbl... mysql indicates it should work... what simple mistake am i missing..?? this is getting embarrassing... mysql select instructorTBL.ID - from instructorTBL - where instructorTBL.name = dr smith1; Empty set (0.00 sec) mysql select ID - from instructorTBL - where instructorTBL.name = dr smith1; Empty set (0.01 sec) mysql select * - from instructorTBL - where instructorTBL.name = dr smith1; Empty set (0.00 sec) thanks... -bruce -- 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]
INSERT ... SELECT question
I have a table with just about 100 columns, and I would like to duplicate a row exactly, except for one column, which is the AUTO_INCREMENT column. Using 'INSERT INTO tbl_name SELECT * FROM tbl_name WHERE id_col = 1' doesn't work, because it produces the following error: 'ERROR 1062: Duplicate entry '1' for key 1'. Because I'd like the application using this database to be simpler to maintain, I'd prefer to not have to change the code each time a field is added...so is there a way to duplicate a row, but still have it automatically assigned an value for the AUTO_INCREMENT column? Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT ... SELECT question
Can you roughly outline the schema of the two tables? If the key value doesn't have to match from the input to the final result, you can always insert into a list of fields, skipping the auto_increment/key column, and they will continue to be generated.. Assuming you have two tables with id_col, col2, col3: INSERT INTO table (col2, col3) SELECT col2, col3 FROM table2 WHERE id_col=1; Regards, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 19:37 To: MySQL Subject: INSERT ... SELECT question I have a table with just about 100 columns, and I would like to duplicate a row exactly, except for one column, which is the AUTO_INCREMENT column. Using 'INSERT INTO tbl_name SELECT * FROM tbl_name WHERE id_col = 1' doesn't work, because it produces the following error: 'ERROR 1062: Duplicate entry '1' for key 1'. Because I'd like the application using this database to be simpler to maintain, I'd prefer to not have to change the code each time a field is added...so is there a way to duplicate a row, but still have it automatically assigned an value for the AUTO_INCREMENT column? Thanks, Eric -- 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: INSERT ... SELECT question
There is only 1 table. I want to replicate a record in a table except the AUTO_INCREMENT column. Your solution would work, but I'd prefer to not have to maintain a list of columns in the application. Worst case I'll have the application generate the query based on the table definition, but I was hoping that MySQL had something built in to make this easier. Thanks, Eric -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 2:11 PM To: 'Eric J. Janus'; 'MySQL' Subject: RE: INSERT ... SELECT question Importance: Low Can you roughly outline the schema of the two tables? If the key value doesn't have to match from the input to the final result, you can always insert into a list of fields, skipping the auto_increment/key column, and they will continue to be generated.. Assuming you have two tables with id_col, col2, col3: INSERT INTO table (col2, col3) SELECT col2, col3 FROM table2 WHERE id_col=1; Regards, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 19:37 To: MySQL Subject: INSERT ... SELECT question I have a table with just about 100 columns, and I would like to duplicate a row exactly, except for one column, which is the AUTO_INCREMENT column. Using 'INSERT INTO tbl_name SELECT * FROM tbl_name WHERE id_col = 1' doesn't work, because it produces the following error: 'ERROR 1062: Duplicate entry '1' for key 1'. Because I'd like the application using this database to be simpler to maintain, I'd prefer to not have to change the code each time a field is added...so is there a way to duplicate a row, but still have it automatically assigned an value for the AUTO_INCREMENT column? Thanks, Eric -- 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: INSERT ... SELECT question
Oh, if only there were views!! That would make this easy. Maybe soon (*please*). :) Another approach (more cumbersome) might be to insert the rows you need to duplicate into a temporary table, update the id_col adding max(id_col) from the original table to each, and then to insert from the temporary table back into the original. I don't like that at all, though. It seems messy. Hopefully someone here can come up with a better idea! Cheers, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 20:12 To: Matt Chatterley; 'MySQL' Subject: RE: INSERT ... SELECT question There is only 1 table. I want to replicate a record in a table except the AUTO_INCREMENT column. Your solution would work, but I'd prefer to not have to maintain a list of columns in the application. Worst case I'll have the application generate the query based on the table definition, but I was hoping that MySQL had something built in to make this easier. Thanks, Eric -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 2:11 PM To: 'Eric J. Janus'; 'MySQL' Subject: RE: INSERT ... SELECT question Importance: Low Can you roughly outline the schema of the two tables? If the key value doesn't have to match from the input to the final result, you can always insert into a list of fields, skipping the auto_increment/key column, and they will continue to be generated.. Assuming you have two tables with id_col, col2, col3: INSERT INTO table (col2, col3) SELECT col2, col3 FROM table2 WHERE id_col=1; Regards, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 19:37 To: MySQL Subject: INSERT ... SELECT question I have a table with just about 100 columns, and I would like to duplicate a row exactly, except for one column, which is the AUTO_INCREMENT column. Using 'INSERT INTO tbl_name SELECT * FROM tbl_name WHERE id_col = 1' doesn't work, because it produces the following error: 'ERROR 1062: Duplicate entry '1' for key 1'. Because I'd like the application using this database to be simpler to maintain, I'd prefer to not have to change the code each time a field is added...so is there a way to duplicate a row, but still have it automatically assigned an value for the AUTO_INCREMENT column? Thanks, Eric -- 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: INSERT ... SELECT question
Views would be nice. :) That idea sounds like it would work in a single-user envrinoment, which I'm not. I agree, messy. I'll just write a function to build a field list from a specified table leaving out the AUTO_INCREMENT field...I can then do something like this: SQL = INSERT INTO tbl_name( + generate_field_list(tbl_name) + ); SQL += SELECT + generate_field_list(tbl_name) FROM tbl_name WHERE where_clause Just a little more work that I was hoping for though! Thanks for the help. Eric -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 2:25 PM To: 'Eric J. Janus'; 'MySQL' Subject: RE: INSERT ... SELECT question Importance: Low Oh, if only there were views!! That would make this easy. Maybe soon (*please*). :) Another approach (more cumbersome) might be to insert the rows you need to duplicate into a temporary table, update the id_col adding max(id_col) from the original table to each, and then to insert from the temporary table back into the original. I don't like that at all, though. It seems messy. Hopefully someone here can come up with a better idea! Cheers, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 20:12 To: Matt Chatterley; 'MySQL' Subject: RE: INSERT ... SELECT question There is only 1 table. I want to replicate a record in a table except the AUTO_INCREMENT column. Your solution would work, but I'd prefer to not have to maintain a list of columns in the application. Worst case I'll have the application generate the query based on the table definition, but I was hoping that MySQL had something built in to make this easier. Thanks, Eric -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 2:11 PM To: 'Eric J. Janus'; 'MySQL' Subject: RE: INSERT ... SELECT question Importance: Low Can you roughly outline the schema of the two tables? If the key value doesn't have to match from the input to the final result, you can always insert into a list of fields, skipping the auto_increment/key column, and they will continue to be generated.. Assuming you have two tables with id_col, col2, col3: INSERT INTO table (col2, col3) SELECT col2, col3 FROM table2 WHERE id_col=1; Regards, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 19:37 To: MySQL Subject: INSERT ... SELECT question I have a table with just about 100 columns, and I would like to duplicate a row exactly, except for one column, which is the AUTO_INCREMENT column. Using 'INSERT INTO tbl_name SELECT * FROM tbl_name WHERE id_col = 1' doesn't work, because it produces the following error: 'ERROR 1062: Duplicate entry '1' for key 1'. Because I'd like the application using this database to be simpler to maintain, I'd prefer to not have to change the code each time a field is added...so is there a way to duplicate a row, but still have it automatically assigned an value for the AUTO_INCREMENT column? Thanks, Eric -- 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: INSERT ... SELECT question
No, no, no, no need to make it that complicated! :-) If your table has the columns col1, col2, col3, col4, etc, and col1 is the autoincrement column, and you want to duplicate the entire table, something like this should work: INSERT INTO t1(col2, col3, col4, ...) SELECT col2, col3, col4, ... FROM t1 The trick is that you select rows from your table that contain all columns except the auto_increment column, and then re-insert them without specifying a value for the auto_increment column, thereby assigning them a new value. If you don't want to duplicate the entire table, just add an appropriate where-clause to the above statement. /Henrik -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: den 29 mars 2004 21:28 To: Matt Chatterley; 'MySQL' Subject: RE: INSERT ... SELECT question Views would be nice. :) That idea sounds like it would work in a single-user envrinoment, which I'm not. I agree, messy. I'll just write a function to build a field list from a specified table leaving out the AUTO_INCREMENT field...I can then do something like this: SQL = INSERT INTO tbl_name( + generate_field_list(tbl_name) + ); SQL += SELECT + generate_field_list(tbl_name) FROM tbl_name WHERE where_clause Just a little more work that I was hoping for though! Thanks for the help. Eric -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 2:25 PM To: 'Eric J. Janus'; 'MySQL' Subject: RE: INSERT ... SELECT question Importance: Low Oh, if only there were views!! That would make this easy. Maybe soon (*please*). :) Another approach (more cumbersome) might be to insert the rows you need to duplicate into a temporary table, update the id_col adding max(id_col) from the original table to each, and then to insert from the temporary table back into the original. I don't like that at all, though. It seems messy. Hopefully someone here can come up with a better idea! Cheers, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 20:12 To: Matt Chatterley; 'MySQL' Subject: RE: INSERT ... SELECT question There is only 1 table. I want to replicate a record in a table except the AUTO_INCREMENT column. Your solution would work, but I'd prefer to not have to maintain a list of columns in the application. Worst case I'll have the application generate the query based on the table definition, but I was hoping that MySQL had something built in to make this easier. Thanks, Eric -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 2:11 PM To: 'Eric J. Janus'; 'MySQL' Subject: RE: INSERT ... SELECT question Importance: Low Can you roughly outline the schema of the two tables? If the key value doesn't have to match from the input to the final result, you can always insert into a list of fields, skipping the auto_increment/key column, and they will continue to be generated.. Assuming you have two tables with id_col, col2, col3: INSERT INTO table (col2, col3) SELECT col2, col3 FROM table2 WHERE id_col=1; Regards, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 19:37 To: MySQL Subject: INSERT ... SELECT question I have a table with just about 100 columns, and I would like to duplicate a row exactly, except for one column, which is the AUTO_INCREMENT column. Using 'INSERT INTO tbl_name SELECT * FROM tbl_name WHERE id_col = 1' doesn't work, because it produces the following error: 'ERROR 1062: Duplicate entry '1' for key 1'. Because I'd like the application using this database to be simpler to maintain, I'd prefer to not have to change the code each time a field is added...so is there a way to duplicate a row, but still have it automatically assigned an value for the AUTO_INCREMENT column? Thanks, Eric -- 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: INSERT ... SELECT question
That would work, BUT, I'm looking for a way that I don't have to change the code in the application when I change the structure of the table. If I add a column, I want its value copied to the new record, but don't want to have to change the application to do so. Your query requires me to alter the application everytime I change the table, which I don't always want to do. -Original Message- From: Henrik Schröder [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 3:07 PM To: 'Eric J. Janus'; 'MySQL' Subject: RE: INSERT ... SELECT question Importance: Low No, no, no, no need to make it that complicated! :-) If your table has the columns col1, col2, col3, col4, etc, and col1 is the autoincrement column, and you want to duplicate the entire table, something like this should work: INSERT INTO t1(col2, col3, col4, ...) SELECT col2, col3, col4, ... FROM t1 The trick is that you select rows from your table that contain all columns except the auto_increment column, and then re-insert them without specifying a value for the auto_increment column, thereby assigning them a new value. If you don't want to duplicate the entire table, just add an appropriate where-clause to the above statement. /Henrik -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: den 29 mars 2004 21:28 To: Matt Chatterley; 'MySQL' Subject: RE: INSERT ... SELECT question Views would be nice. :) That idea sounds like it would work in a single-user envrinoment, which I'm not. I agree, messy. I'll just write a function to build a field list from a specified table leaving out the AUTO_INCREMENT field...I can then do something like this: SQL = INSERT INTO tbl_name( + generate_field_list(tbl_name) + ); SQL += SELECT + generate_field_list(tbl_name) FROM tbl_name WHERE where_clause Just a little more work that I was hoping for though! Thanks for the help. Eric -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 2:25 PM To: 'Eric J. Janus'; 'MySQL' Subject: RE: INSERT ... SELECT question Importance: Low Oh, if only there were views!! That would make this easy. Maybe soon (*please*). :) Another approach (more cumbersome) might be to insert the rows you need to duplicate into a temporary table, update the id_col adding max(id_col) from the original table to each, and then to insert from the temporary table back into the original. I don't like that at all, though. It seems messy. Hopefully someone here can come up with a better idea! Cheers, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 20:12 To: Matt Chatterley; 'MySQL' Subject: RE: INSERT ... SELECT question There is only 1 table. I want to replicate a record in a table except the AUTO_INCREMENT column. Your solution would work, but I'd prefer to not have to maintain a list of columns in the application. Worst case I'll have the application generate the query based on the table definition, but I was hoping that MySQL had something built in to make this easier. Thanks, Eric -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 2:11 PM To: 'Eric J. Janus'; 'MySQL' Subject: RE: INSERT ... SELECT question Importance: Low Can you roughly outline the schema of the two tables? If the key value doesn't have to match from the input to the final result, you can always insert into a list of fields, skipping the auto_increment/key column, and they will continue to be generated.. Assuming you have two tables with id_col, col2, col3: INSERT INTO table (col2, col3) SELECT col2, col3 FROM table2 WHERE id_col=1; Regards, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 19:37 To: MySQL Subject: INSERT ... SELECT question I have a table with just about 100 columns, and I would like to duplicate a row exactly, except for one column, which is the AUTO_INCREMENT column. Using 'INSERT INTO tbl_name SELECT * FROM tbl_name WHERE id_col = 1' doesn't work, because it produces the following error: 'ERROR 1062: Duplicate entry '1' for key 1'. Because I'd like the application using this database to be simpler to maintain, I'd prefer to not have to change the code each time a field is added...so is there a way to duplicate a row, but still have it automatically assigned an value for the AUTO_INCREMENT column? Thanks, Eric -- 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
Re: another insert select question
Jason Joines [EMAIL PROTECTED] wrote: Table employees: | idnumber | email | phone | address | Table webusers: - | idnumber | userid | website | - Table employees is completely populated. Table webusers has the idnumber and website fields completely populated. What query can I use to insert the email address from each persones record in the employees table into the userid field of their corresponding record in the webusers table? If I've got you right you need UPDATE, not INSERT .. SELECT. UPDATE webuser, employees SET webuser.userid=employees.email WHERE webuser.idnumber=employees.idnumber; The above UPDATE statement will work from v4.0.4. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: another insert select question
Egor Egorov wrote: Jason Joines [EMAIL PROTECTED] wrote: Table employees: | idnumber | email | phone | address | Table webusers: - | idnumber | userid | website | - Table employees is completely populated. Table webusers has the idnumber and website fields completely populated. What query can I use to insert the email address from each persones record in the employees table into the userid field of their corresponding record in the webusers table? If I've got you right you need UPDATE, not INSERT .. SELECT. UPDATE webuser, employees SET webuser.userid=employees.email WHERE webuser.idnumber=employees.idnumber; The above UPDATE statement will work from v4.0.4. You've got it right, I was way off trying to use an INSERT instead of an UPDATE. These tables were an example where I was trying to get the syntax right before using it on the real thing. I thought I had to use two tables to make it work. The real thing is one table called on a 3.23.48 server. One of it's fields is an email address (email) and one is the userid. The primary key is idnumber. I need to populate the userid field from the email address field. I can get the userid using: SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) from employees; but I haven't been able to get the results into the userid field. Any ideas? Thanks, Jason === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
an update select question
I have a table called employees on a 3.23.48 server. One of it's fields is an email address (email) and one is the userid. The primary key is idnumber. I need to populate the userid field from the email address field. I can get the userid using: SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) from employees; but I haven't been able to get the results into the userid field. Any ideas? Thanks, Jason === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: an update select question
update employees set userid=substring(. Jason Joines wrote: I have a table called employees on a 3.23.48 server. One of it's fields is an email address (email) and one is the userid. The primary key is idnumber. I need to populate the userid field from the email address field. I can get the userid using: SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) from employees; but I haven't been able to get the results into the userid field. Any ideas? Thanks, Jason === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: an update select question
IF I understand you correctly, you should be able to do this: UPDATE employees SET userid=substring(per_email_address,1,instr(per_email_address,'@')-1); Chris -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Jason Joines Sent: Wednesday, November 05, 2003 9:14 AM To: [EMAIL PROTECTED] Subject: an update select question I have a table called employees on a 3.23.48 server. One of it's fields is an email address (email) and one is the userid. The primary key is idnumber. I need to populate the userid field from the email address field. I can get the userid using: SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) from employees; but I haven't been able to get the results into the userid field. Any ideas? Thanks, Jason === -- 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: an update select question
gerald_clark wrote: update employees set userid=substring(. Jason Joines wrote: I have a table called employees on a 3.23.48 server. One of it's fields is an email address (email) and one is the userid. The primary key is idnumber. I need to populate the userid field from the email address field. I can get the userid using: SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) from employees; but I haven't been able to get the results into the userid field. Any ideas? Thanks, Jason === Thank you. UPDATE employees SET userid=substring(email,1,instr(email,'@')-1); is exactly what I needed. Jason === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: another insert select question
Jason Joines wrote: Egor Egorov wrote: Jason Joines [EMAIL PROTECTED] wrote: Table employees: | idnumber | email | phone | address | Table webusers: - | idnumber | userid | website | - Table employees is completely populated. Table webusers has the idnumber and website fields completely populated. What query can I use to insert the email address from each persones record in the employees table into the userid field of their corresponding record in the webusers table? If I've got you right you need UPDATE, not INSERT .. SELECT. UPDATE webuser, employees SET webuser.userid=employees.email WHERE webuser.idnumber=employees.idnumber; The above UPDATE statement will work from v4.0.4. You've got it right, I was way off trying to use an INSERT instead of an UPDATE. These tables were an example where I was trying to get the syntax right before using it on the real thing. I thought I had to use two tables to make it work. The real thing is one table called on a 3.23.48 server. One of it's fields is an email address (email) and one is the userid. The primary key is idnumber. I need to populate the userid field from the email address field. I can get the userid using: SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) from employees; but I haven't been able to get the results into the userid field. Any ideas? Thanks, Jason === Turns out I need UPDATE SET UPDATE employees SET userid=substring(email,1,instr(email,'@')-1); Solution provided to me in the an update select question thread. Jason === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
another insert select question
Table employees: | idnumber | email | phone | address | Table webusers: - | idnumber | userid | website | - Table employees is completely populated. Table webusers has the idnumber and website fields completely populated. What query can I use to insert the email address from each persones record in the employees table into the userid field of their corresponding record in the webusers table? Thanks, Jason Joines Open Source = Open Mind -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert ... Select question
I use this syntax but I have privilege problem. Thenk you anyway - Original Message - From: Nitin [EMAIL PROTECTED] To: IEEIO AAOOCO [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 27, 2003 5:20 PM Subject: Re: Insert ... Select question of course, the syntax is: insert into db3.table2 (column list) select column list from db1.table1 for more information have a look at http://www.mysql.com/doc/en/INSERT_SELECT.html Enjoy Nitin - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:28 PM Subject: Insert ... Select question Hello list I want to insert ... select data from table1 of db1 to table2 of db3. Is that possible? Thank in advance -- 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]
Insert ... Select question
Hello list I want to insert ... select data from table1 of db1 to table2 of db3. Is that possible? Thank in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert ... Select question
of course, the syntax is: insert into db3.table2 (column list) select column list from db1.table1 for more information have a look at http://www.mysql.com/doc/en/INSERT_SELECT.html Enjoy Nitin - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:28 PM Subject: Insert ... Select question Hello list I want to insert ... select data from table1 of db1 to table2 of db3. Is that possible? Thank in advance -- 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: Insert ... Select question
Nikos, Yes. Use the following syntax: INSERT INTO db_name.tbl_name (col, col, col) SELECT col, col, col FROM db_name.tbl_name; Regards, Adam -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 9:58 AM To: [EMAIL PROTECTED] Subject: Insert ... Select question Hello list I want to insert ... select data from table1 of db1 to table2 of db3. Is that possible? Thank in advance -- 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: SELECT Question
-Original Message- From: Jean-Pierre Schwickerath [mailto:[EMAIL PROTECTED] Hello Mumba, Hello Barry, How do I select out and filter only rows that match both 16 and 62 in the KEYW_ID col? IE. The query would return only 119 and 108? I'm sure this could be done more effeciently other ways, possibly with a sub select if available, but something like this would probably work: SELECT temp1.* FROM table_name AS temp1 LEFT JOIN table_name AS temp2 ON temp1.tbl_idx=temp2.tbl_idx WHERE (temp1.keyw_id=16 AND temp2.keyw_id=62) OR (temp1.keyw_id=62 AND temp2.keyw_id=16); I'm not sure which one is more efficient but I'd do it this way: SELECT * FROM table WHERE KEYW_ID = 16 OR KEYW_ID = 62 GROUP BY TBL_IDX HAVING COUNT(TBL_IDX) = 2; Neat - but I think this would give a false positive if there were two rows with keyw_id = 16 or with keyw_id=62. If you know for certain that the tbl_idx and keyw_id pair are unique in the table, then all would be fine. - Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT Question
Hi, I've been given a table to work with, and I'm not meant to change it: - | TABLE_NAME| TBL_IDX | KEYW_ID | - | PROPERTIES| 108 | 16 | - | PROPERTIES| 119 | 16 | - | PROPERTIES| 108 | 62 | - | PROPERTIES| 119 | 16 | - | PROPERTIES| 135 | 16 | - | PROPERTIES| 135 | 17 | - How do I select out and filter only rows that match both 16 and 62 in the KEYW_ID col? IE. The query would return only 119 and 108? Any help much appreciated Mumba. Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT Question
-Original Message- From: Mumba Chucks [mailto:[EMAIL PROTECTED] I've been given a table to work with, and I'm not meant to change it: - | TABLE_NAME | TBL_IDX | KEYW_ID | - | PROPERTIES | 108 | 16 | - | PROPERTIES| 119 | 16 | - | PROPERTIES| 108 | 62 | - | PROPERTIES| 119 | 16 | - | PROPERTIES| 135 | 16 | - | PROPERTIES| 135 | 17 | - How do I select out and filter only rows that match both 16 and 62 in the KEYW_ID col? IE. The query would return only 119 and 108? I'm sure this could be done more effeciently other ways, possibly with a sub select if available, but something like this would probably work: SELECT temp1.* FROM table_name AS temp1 LEFT JOIN table_name AS temp2 ON temp1.tbl_idx=temp2.tbl_idx WHERE (temp1.keyw_id=16 AND temp2.keyw_id=62) OR (temp1.keyw_id=62 AND temp2.keyw_id=16); - Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Question
Hello Mumba, Hello Barry, How do I select out and filter only rows that match both 16 and 62 in the KEYW_ID col? IE. The query would return only 119 and 108? I'm sure this could be done more effeciently other ways, possibly with a sub select if available, but something like this would probably work: SELECT temp1.* FROM table_name AS temp1 LEFT JOIN table_name AS temp2 ON temp1.tbl_idx=temp2.tbl_idx WHERE (temp1.keyw_id=16 AND temp2.keyw_id=62) OR (temp1.keyw_id=62 AND temp2.keyw_id=16); I'm not sure which one is more efficient but I'd do it this way: SELECT * FROM table WHERE KEYW_ID = 16 OR KEYW_ID = 62 GROUP BY TBL_IDX HAVING COUNT(TBL_IDX) = 2; Jean-Pierre -- Powered by Linux From Scratch - http://schwicky.net/ PGP Key ID: 0xEE6F49B4 - AIM/Jabber: Schwicky - ICQ: 4690141 Nothing is impossible... Everything is relative! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie SELECT question
Hi, I have a table containing page referral URLs gleaned from users browsing my website. Is there a way for me to use SELECT based on a portion record's contents? eg table contents: http://www.yahoo.com/adirectory/apage.htm http://google.net/adirectory/anotherpage.php I wish to return only the portion between the // and /. Sort of a //*/ thing so I can use COUNT with this to allow me to build a table in PHP of the most popular root domains which go to my site. I hope this all makes sense ;-) kind regards, Graham Nichols.
RE: newbie SELECT question
Could look at instr? -Original Message- From: Graham Nichols [mailto:[EMAIL PROTECTED] Sent: 01 October 2003 14:54 To: [EMAIL PROTECTED] Subject: newbie SELECT question Hi, I have a table containing page referral URLs gleaned from users browsing my website. Is there a way for me to use SELECT based on a portion record's contents? eg table contents: http://www.yahoo.com/adirectory/apage.htm http://google.net/adirectory/anotherpage.php I wish to return only the portion between the // and /. Sort of a //*/ thing so I can use COUNT with this to allow me to build a table in PHP of the most popular root domains which go to my site. I hope this all makes sense ;-) kind regards, Graham Nichols. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie SELECT question
How about this? SELECT SUBSTRING_INDEX(SUBSTRING(url, LOCATE(//, url) + 2), '/', 1) AS domain FROM referals Michael On Wed, 1 Oct 2003 14:54:24 +0100, Graham Nichols [EMAIL PROTECTED] wrote: Hi, I have a table containing page referral URLs gleaned from users browsing my website. Is there a way for me to use SELECT based on a portion record's contents? eg table contents: http://www.yahoo.com/adirectory/apage.htm http://google.net/adirectory/anotherpage.php I wish to return only the portion between the // and /. Sort of a //*/ thing so I can use COUNT with this to allow me to build a table in PHP of the most popular root domains which go to my site. I hope this all makes sense ;-) kind regards, Graham Nichols. -- Michael Johnson [EMAIL PROTECTED] Internet Application Programmer, Pitsco, Inc. 620-231-2424x516 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie SELECT Question
We are having problems with what we think is a simple select statement: select ENTITY from ATTRIBUTE where (NAME='FavoriteSport' and VALUE='Soccer') and (NAME='FavoriteFood' and VALUE='CornDogs'); First, we are running an older version of MySQL: mysql select version(); +--+ | version()| +--+ | 3.23.22-beta-log | +--+ 1 row in set (0.00 sec) First, we create a table: CREATE TABLE ATTRIBUTE ( ENTITY int(10) unsigned NOT NULL, NAME varchar(64) NOT NULL, TYPE varchar(8), VALUE text, UNIT varchar(12), READONLY char(1), ENTERED timestamp(14), UPDATED timestamp(14), PRIMARY KEY (ENTITY,NAME) ); Next we populate it so it contains the following data: mysql select ENTITY, NAME, VALUE from ATTRIBUTE; ++---+-+ | ENTITY | NAME | VALUE | ++---+-+ |128 | Age | 7 | |128 | FavoriteFood | Sandwich| |128 | FavoriteSport | Tennis | |127 | Age | 5 | |127 | FavoriteFood | Peanuts | |127 | FavoriteSport | Hockey | |125 | FavoriteFood | Tacos | |125 | Age | 7 | |125 | FavoriteSport | Lacrosse| |124 | FavoriteFood | Hamburgers | |124 | Age | 8 | |124 | FavoriteSport | Soccer | |122 | FavoriteSport | Tennis | |122 | Age | 7 | |122 | FavoriteFood | Sandwich| |118 | FavoriteSport | Soccer | |118 | Age | 6 | |118 | FavoriteFood | CornDogs| |119 | FavoriteSport | Swimming| |119 | Age | 8 | |119 | FavoriteFood | Salad | |121 | FavoriteSport | Hockey | |121 | Age | 5 | |121 | FavoriteFood | Ice Cream | ++---+-+ 24 rows in set (0.01 sec) Now we want a list of entities that have an attribute named 'FavoriteSport' and a value of 'Soccer': mysql select ENTITY from ATTRIBUTE where NAME='FavoriteSport' and VALUE='Soccer'; ++ | ENTITY | ++ |124 | |118 | ++ 2 rows in set (0.01 sec) Now get a list of entities that have an attribute named 'FavoriteFood' and a value of 'CornDogs': mysql select ENTITY from ATTRIBUTE where (NAME='FavoriteFood' and VALUE='CornDogs'); ++ | ENTITY | ++ |118 | ++ 1 row in set (0.01 sec) Apparently ENTITY 118 has both a favorite sport of Soccer and a favorite food of CornDogs because it appears on the result list for each of the queries. Finally, let's try to get a list of entities that have both a favorite sport of Soccer and a favorite food of CornDogs with just one query: mysql select ENTITY from ATTRIBUTE where (NAME='FavoriteSport' and VALUE='Soccer') and (NAME='FavoriteFood' and VALUE='CornDogs'); Empty set (0.00 sec) This isn't what we expect. We should see ENTITY 118 appear in the result list since the previous two queries returned ENTITY 118. What single query will return just the records that both sets (NAME VALUE) of tests? -Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie SELECT Question
Steve Cote wrote: We are having problems with what we think is a simple select statement: select ENTITY from ATTRIBUTE where (NAME='FavoriteSport' and VALUE='Soccer') and (NAME='FavoriteFood' and VALUE='CornDogs'); You are trying to find a row with a NAME value of 'FavoriteSport' and 'FavoriteFood' at the same time, also with a VALUE value of 'Soccer' and 'CornDogs' at the same time. This can't work. Here's a query that does what you want: SELECT a1.ENTITY FROM ATTRIBUTE a1, ATTRIBUTE a2 WHERE a1.ENTITY = a2.ENTITY AND a1.NAME='FavoriteSport' and a1.VALUE='Soccer' AND a2.NAME='FavoriteFood' and a2.VALUE='CornDogs' [...] Finally, let's try to get a list of entities that have both a favorite sport of Soccer and a favorite food of CornDogs with just one query: mysql select ENTITY from ATTRIBUTE where (NAME='FavoriteSport' and VALUE='Soccer') and (NAME='FavoriteFood' and VALUE='CornDogs'); Empty set (0.00 sec) This isn't what we expect. We should see ENTITY 118 appear in the result list since the previous two queries returned ENTITY 118. What single query will return just the records that both sets (NAME VALUE) of tests? [...] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie SELECT Question
Steve select ENTITY from ATTRIBUTE where (NAME='FavoriteSport' and Steve VALUE='Soccer') and (NAME='FavoriteFood' and VALUE='CornDogs'); Steve Empty set (0.00 sec) Just analyze the query. You asked for a record in which name = 'FavoriteSport' AND name = 'FavoriteFood' which is always false because name can not be both 'FavoriteSport' and 'FavoriteFood' at the same time. One of the possible solutions (not so good) is this: select a.entity from attribute a, attribute b where a.entity = b.entity and a.NAME='FavoriteSport' and a.VALUE='Soccer' and b.NAME='FavoriteFood' and b.VALUE='CornDogs'; or you can upgrade to 4.1 and do this select entity from attribute where NAME='FavoriteSport' and VALUE='Soccer' and entity in (select entity from attribute where NAME='FavoriteFood' and VALUE='CornDogs'); in the near future (i think in 5.x tree) you will be able to intersect two queries select ... INTERSECT select ...; select ENTITY from ATTRIBUTE where NAME='FavoriteSport' and VALUE='Soccer' INTERSECT select ENTITY from ATTRIBUTE where NAME='FavoriteFood' and VALUE='CornDogs'; Or you can do it through script or something like that Ivan __ One World, one Web, one Program -- Microsoft promotional ad Ein Volk, ein Reich, ein Fuhrer -- Adolf Hitler __ http://alas.matf.bg.ac.yu/~mr02014 ___ _ _ _ __ ___ _ / __/___ __ | __| _ _ ___ \ / _/ / . / _\/\ | _| \ \/ / ._\ Ivan Cukic, Form Eye 2003. \ /_/ /___/_/ /_/_/_/ |___|_\ /\___ web development and design / __ / _ _ __ ___ / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT question
I have a question on how to substitute a subselect in mysql. For example lets say i have 3 tables Cars, Options and CarOptions Cars consists of: uid, make Options consists of: uid, option CarOption consists of: uid, Caruid, Optionsuid I want to select all Cars that have ALL of requested options: if there were only few options (say 2) it would be easy: (lets ignore the options table and assume we know the needed uids from Options) SELECT Cars.* FROM Cars JOIN CarOptions as CO1 ON Cars.uid = CO1.Caruid JOIN CarOptions as CO2 ON Cars.uid = CO2.Caruid WHERE CO1.Optionuid = 1 AND CO2.Optionuid = 2 Now the problem is what if there are dozens of Options (so each car can have none or 20 options each) You see the problem? I can easily keep on building JOIN statements for each option they requested but that could end up with a JOIN per Option that is requested Having no limit on Options the SELECT statement can be HUGE and adding additional tables into equation gets crazy (like tables carengines, carweel, cardealer)... So the queston is: Is there a better way of doing this? I am sure there is a limit on how many joins can be in the SELECT... Also, I tested it with the following data: 3 entries in the Cars table, 20 entries in Option table and 10 entries in the CarOption table Wrote the SELECT asking for Every possible option (all 20) and the time it took to execute it was devistating (about 2 seconds) (longer than if i manualy did select from CarOption table for every needed Option for each car). Please help, thank you
Simple SELECT question
I need a little help querying 2 tables in MySQL; Table User User_Id // Id = 2 User_Name // John Table History User_Id // 2 User_History // John has born in 58 SELECT User_History FROM History WHERE User_Id = 2; // return John has born in 58 But what my SELECT should look if i don't know the user id and want search the User_History by the User_Name? SELECT User_History FROM History WHERE User_Id = ( SOMEWAY TO DATABASE FIND JOHN ID BY JOHN NAME ) -- Use o melhor sistema de busca da Internet Radar UOL - http://www.radaruol.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Simple SELECT question
Ok, lessee... SELECT user.user_id, user_name, user_history FROM user, history WHERE user_history LIKE %58% AND user.user_id = history.user_id; That should do what you need, lemme know. Mike Hillyer http://www.dynamergy.com/mike -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: Simple SELECT question I need a little help querying 2 tables in MySQL; Table User User_Id // Id = 2 User_Name // John Table History User_Id // 2 User_History // John has born in 58 SELECT User_History FROM History WHERE User_Id = 2; // return John has born in 58 But what my SELECT should look if i don't know the user id and want search the User_History by the User_Name? SELECT User_History FROM History WHERE User_Id = ( SOMEWAY TO DATABASE FIND JOHN ID BY JOHN NAME ) -- Use o melhor sistema de busca da Internet Radar UOL - http://www.radaruol.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Simple SELECT question
[EMAIL PROTECTED] wrote: I need a little help querying 2 tables in MySQL; Table User User_Id // Id = 2 User_Name // John Table History User_Id // 2 User_History // John has born in 58 SELECT User_History FROM History WHERE User_Id = 2; // return John has born in 58 But what my SELECT should look if i don't know the user id and want search the User_History by the User_Name? SELECT User_History FROM History WHERE User_Id = ( SOMEWAY TO DATABASE FIND JOHN ID BY JOHN NAME ) SELECT User_History FROM User u INNER JOIN Table_History h ON u.User_ID = h.User_ID WHERE u.User_Name = 'John' ought to do the trick for you. Bruce Feist, also born in 58 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Simple SELECT question
--- [EMAIL PROTECTED] wrote: I need a little help querying 2 tables in MySQL; Table User User_Id // Id = 2 User_Name // John Table History User_Id // 2 User_History // John has born in 58 SELECT User_History FROM History WHERE User_Id = 2; // return John has born in 58 But what my SELECT should look if i don't know the user id and want search the User_History by the User_Name? SELECT User_History FROM History WHERE User_Id = ( SOMEWAY TO DATABASE FIND JOHN ID BY JOHN NAME ) I am no MySQL expert by any means, but I believe that a sub select would handle it. However, last I knew MySQL didn't support them. So I believe the other solution would be to do a JOIN, I thought I remembered people here saying that JOINs are the sub-select work around. Seeing as how I do not know them at all I can't offer an example... Sorry If this is incorrect someone please correct me. Thanx, Ryan __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: Select question
C, After some struggling, I have managed to get the problem below 99% working, the problem now is that I can't get them in descending order. Here is my select statement. $query = select manager.name, position, MAX(goals) from roster join reference join manager where manager.idn=reference.idn and reference.idp=roster.idp and position like 'F' GROUP BY manager.name order by goals desc; Using the example below, this is what I get: Bill 70 John 48 Fred 87 This is what I want: Fred 87 Bill 70 John 48 As an extension to ANSI-SQL, MySQL has added ASC/DESC to GROUP BY. So might want to try GROUP BY manager.name DESC. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select question
Note that you're ordering by goals, not MAX(goals). That's why you're not getting the results expected. Try: SELECT manager.name, position, MAX(goals) as goals FROM roster JOIN reference JOIN manager WHERE manager.idn=reference.idn AND reference.idp=roster.idp AND position like 'F' GROUP BY manager.name ORDER BY goals desc; On Fri, 2003-02-07 at 19:14, C. Reeve wrote: Hi again, After some struggling, I have managed to get the problem below 99% working, the problem now is that I can't get them in descending order. Here is my select statement. $query = select manager.name, position, MAX(goals) from roster join reference join manager where manager.idn=reference.idn and reference.idp=roster.idp and position like 'F' GROUP BY manager.name order by goals desc; Using the example below, this is what I get: Bill 70 John 48 Fred 87 This is what I want: Fred 87 Bill 70 John 48 TIA - Original Message - From: C. Reeve [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Friday, February 07, 2003 1:57 PM Subject: Select question Hi, I have a database with 3 names in it. In each of these names is 5 categories that have numbers in them. I want to be able to do a select and get the top number from each category for each name and display them from most to least. I have checked all the docs on the select statement, but this is escaping me at the moment. TIA i.e This is what is in the database. Bill 3620504670 John2630324846 Fred8740196242 This is what I want to be able to show: Fred 87 Bill 70 John 48 -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select question
Hi, I have a database with 3 names in it. In each of these names is 5 categories that have numbers in them. I want to be able to do a select and get the top number from each category for each name and display them from most to least. I have checked all the docs on the select statement, but this is escaping me at the moment. TIA i.e This is what is in the database. Bill 3620504670 John2630324846 Fred8740196242 This is what I want to be able to show: Fred 87 Bill 70 John 48 SPAM bypass: sql, query, queries, smallint - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select question
Hi again, After some struggling, I have managed to get the problem below 99% working, the problem now is that I can't get them in descending order. Here is my select statement. $query = select manager.name, position, MAX(goals) from roster join reference join manager where manager.idn=reference.idn and reference.idp=roster.idp and position like 'F' GROUP BY manager.name order by goals desc; Using the example below, this is what I get: Bill 70 John 48 Fred 87 This is what I want: Fred 87 Bill 70 John 48 TIA - Original Message - From: C. Reeve [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Friday, February 07, 2003 1:57 PM Subject: Select question Hi, I have a database with 3 names in it. In each of these names is 5 categories that have numbers in them. I want to be able to do a select and get the top number from each category for each name and display them from most to least. I have checked all the docs on the select statement, but this is escaping me at the moment. TIA i.e This is what is in the database. Bill 3620504670 John2630324846 Fred8740196242 This is what I want to be able to show: Fred 87 Bill 70 John 48 SPAM bypass: sql, query, queries, smallint - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select question
Use the CONCAT function Adolfo -Original Message- From: tag [mailto:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 3:57 AM To: [EMAIL PROTECTED] Subject: select question HI, I need to do a select query that can do the following: select * from table where col1 like hex(somestring); My problem is HOW do I get the % in there??? The Mysql Server is 4.0.4 and the table has a blob field with hex stored in it Thanks Tonino - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select question
HI, I need to do a select query that can do the following: select * from table where col1 like hex(somestring); My problem is HOW do I get the % in there??? The Mysql Server is 4.0.4 and the table has a blob field with hex stored in it Thanks Tonino - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select question
* tag
I need to do a select query that can do the following:
select * from table where col1 like hex(somestring);
This was a bit confusing... :)
hex(somestring) will always return 0, unless the string is a numerical
value:
mysql select hex('65'),hex(65),hex(A);
+---+-+--+
| hex('65') | hex(65) | hex(A) |
+---+-+--+
| 41| 41 | 0|
+---+-+--+
1 row in set (0.00 sec)
My problem is HOW do I get the % in there???
I think you should not use the hex() function in this case.
The Mysql Server is 4.0.4 and the table has a blob field with hex
stored in it
I doubt if this is the case... hex is a representation, not a format. If you
really have hex strings stored in the table, you wouldn't need a blob,
because all characters are ascii (0-9 + a-f).
You probably have binary data stored in your blob, and you can view it using
hex representation, but you must search on the binary values, not on the hex
representation. Something like this:
SELECT * FROM table WHERE col1 LIKE x#2\\1\_@!\æøå\0%;
You must escape the characters , ', \ and ascii 0 with a preceeding \.
(This also aplies to % and _ when you want to search for them using LIKE).
In other words, the above string is really: x#2\1_@!æøå + ascii 0 + %.
Languages supporting mysql have a special funtion for this, called
mysql_escape_string() in the C API, quote() in perl DBI.
HTH,
--
Roger
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select question
* Tonino Greco Thanks - but I got it working : select * from table where col1 like concat(%, hex(somestring), %); the hex(somestring) - returns :736F6D65737472696E67 * me hex(somestring) will always return 0, unless the string is a numerical value Sorry for that, this was changed in 4.0.1, hex(string) now returns a hex representation of the string, like you said. In 3.23 it would only convert numbers. If col1 really contains hex strings, there is no need to use BLOB, because hex strings are not binary, though they may _represent_ binary data. And, of course, they take twice as much space to store, compared to the binary data. Beware that you can get unpredictable results in some cases with the method you describe above. For instance, if you search for the letter B, it will be executed as ... LIKE %42%. If the actual data for a row is D A, it will be stored as 442041, and you will get a match for %42%, even if it does not contain the letter B. -- Roger sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql SELECT question (LEFT JOIN?)
by now, i hope, you've gotten answers from the sql gurus on the list, so i won't clutter with my humble attempts. your comment about problems with joins indicates that corereader might be of some help to you if you have a windows box for a front end. it will let you do quick point-and-click queries, so you can experiment with fairly complex joins. download it from http://corereader.com it's intended to be a production system, but it's a great teaching tool. it connects to anything, but it especially likes mysql. Assume two tables: CREATE TABLE block_ip ( datestamp int(11) NOT NULL default '0', remote_addr char(15) NOT NULL default '', PRIMARY KEY (remote_addr), KEY datestamp (datestamp) ) TYPE=MyISAM; CREATE TABLE brute_force ( datestamp int(11) NOT NULL default '0', remote_addr char(15) NOT NULL default '', remote_user char(35) NOT NULL default '', KEY remote_addr (remote_addr), KEY datestamp (datestamp), KEY remote_user (remote_user) ) TYPE=MyISAM; Contents of the 'brute_force' table (remote_addr): 1.2.3.4 2.3.4.5 3.4.5.6 4.5.6.7 5.6.7.8 6.7.8.9 Contents of the 'block_ip' table (remote_addr): 2.3.4.5 4.5.6.7 Can someone help me with the query that will select all the 'remote_addr' rows from 'brute_force' that are NOT in the 'block_ip' table? Something like: select brute_force.* from brute_force, block_ip where brute_force.remote_addr != block_ip.remote_addr maybe? I have a feeling it's some sort of left join, and I was never very good at those. :-/ -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Mysql SELECT question (LEFT JOIN?)
Eric, Friday, November 15, 2002, 1:36:54 AM, you wrote: EA Assume two tables: EA CREATE TABLE block_ip ( EA datestamp int(11) NOT NULL default '0', EA remote_addr char(15) NOT NULL default '', EA PRIMARY KEY (remote_addr), EA KEY datestamp (datestamp) EA ) TYPE=MyISAM; EA CREATE TABLE brute_force ( EA datestamp int(11) NOT NULL default '0', EA remote_addr char(15) NOT NULL default '', EA remote_user char(35) NOT NULL default '', EA KEY remote_addr (remote_addr), EA KEY datestamp (datestamp), EA KEY remote_user (remote_user) EA ) TYPE=MyISAM; EA Contents of the 'brute_force' table (remote_addr): EA 1.2.3.4 EA 2.3.4.5 EA 3.4.5.6 EA 4.5.6.7 EA 5.6.7.8 EA 6.7.8.9 EA Contents of the 'block_ip' table (remote_addr): EA 2.3.4.5 EA 4.5.6.7 EA Can someone help me with the query that will select all the EA 'remote_addr' rows from 'brute_force' that are NOT in the 'block_ip' EA table? EA Something like: EA select brute_force.* from brute_force, block_ip where EA brute_force.remote_addr != block_ip.remote_addr EA maybe? I have a feeling it's some sort of left join, and I was never EA very good at those. :-/ Yes, you need LEFT JOIN :) SELECT brute_force.* FROM brute_force LEFT JOIN block_ip ON brute_force.remote_addr=block_ip.remote_addr WHERE block_ip.remote_addr IS NULL; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INSERT INTO ... SELECT question
Hello, I recently stumbled upon the INSERT INTO..SELECT abilities. Basically what I'm doing is archiving records into another table before deleting them (inventory information). However, I'd like to have the archive table to have one more field than the original table: a date_archived function. So, for example if the tables looked like this: (quite simplified) Original: carton_id item_id qty status date_recd Archive carton_id item_id qty status date_recd date_archived *(new field) Can I have the SQL query have a NOW() in there to insert today's date when running this ? INSERT INTO archive (carton_id,item_id,qty,status,date_recd,date_archived) SELECT carton_id,item_id,qty,status,date_recd, NOW() FROM original Would this work? I'd like to know upfront before basing my code around this or whether or not I'll have to track how many records are going into the new table and manually updating the field. Thanks. - Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT INTO ... SELECT question
Well, amazingly enough, it works great! I found a test box to try it on first before implementing this on the production box. This will definitely make life easier... On Thu, 2002-11-14 at 10:14, gerald_clark wrote: Did you try it? Did it work? Greg Macek wrote: Hello, I recently stumbled upon the INSERT INTO..SELECT abilities. Basically what I'm doing is archiving records into another table before deleting them (inventory information). However, I'd like to have the archive table to have one more field than the original table: a date_archived function. So, for example if the tables looked like this: (quite simplified) Original: carton_id item_id qty status date_recd Archive carton_id item_id qty status date_recd date_archived *(new field) Can I have the SQL query have a NOW() in there to insert today's date when running this ? INSERT INTO archive (carton_id,item_id,qty,status,date_recd,date_archived) SELECT carton_id,item_id,qty,status,date_recd, NOW() FROM original Would this work? I'd like to know upfront before basing my code around this or whether or not I'll have to track how many records are going into the new table and manually updating the field. Thanks. - Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT INTO ... SELECT question
Hey Greg: A slightly easier way to do this is to use a timestamp field. Timestamp is just a standard mysql data type. When a record is added, it records the current time. When a record is updated, the timestamp field will be set to the time of the update. http://www.mysql.com/doc/en/DATETIME.html The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Thanks, Matt Matthew P Baranowski Data Manager, Office of Educational Assessment University of Washington - Original Message - From: Greg Macek [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 14, 2002 8:06 AM Subject: INSERT INTO ... SELECT question Hello, I recently stumbled upon the INSERT INTO..SELECT abilities. Basically what I'm doing is archiving records into another table before deleting them (inventory information). However, I'd like to have the archive table to have one more field than the original table: a date_archived function. So, for example if the tables looked like this: (quite simplified) Original: carton_id item_id qty status date_recd Archive carton_id item_id qty status date_recd date_archived *(new field) Can I have the SQL query have a NOW() in there to insert today's date when running this ? INSERT INTO archive (carton_id,item_id,qty,status,date_recd,date_archived) SELECT carton_id,item_id,qty,status,date_recd, NOW() FROM original Would this work? I'd like to know upfront before basing my code around this or whether or not I'll have to track how many records are going into the new table and manually updating the field. Thanks. - Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT INTO ... SELECT question
Thanks for the tip! Looks like I can change my date_archived field to timestamp(8), since all I care about for this is the date information (actual time is useless to me). My sql query all of a sudden got a lot simpler. Thanks again for the help! On Thu, 2002-11-14 at 11:07, Matthew Baranowski wrote: Hey Greg: A slightly easier way to do this is to use a timestamp field. Timestamp is just a standard mysql data type. When a record is added, it records the current time. When a record is updated, the timestamp field will be set to the time of the update. http://www.mysql.com/doc/en/DATETIME.html The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Thanks, Matt Matthew P Baranowski Data Manager, Office of Educational Assessment University of Washington - Original Message - From: Greg Macek [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 14, 2002 8:06 AM Subject: INSERT INTO ... SELECT question Hello, I recently stumbled upon the INSERT INTO..SELECT abilities. Basically what I'm doing is archiving records into another table before deleting them (inventory information). However, I'd like to have the archive table to have one more field than the original table: a date_archived function. So, for example if the tables looked like this: (quite simplified) Original: carton_id item_id qty status date_recd Archive carton_id item_id qty status date_recd date_archived *(new field) Can I have the SQL query have a NOW() in there to insert today's date when running this ? INSERT INTO archive (carton_id,item_id,qty,status,date_recd,date_archived) SELECT carton_id,item_id,qty,status,date_recd, NOW() FROM original Would this work? I'd like to know upfront before basing my code around this or whether or not I'll have to track how many records are going into the new table and manually updating the field. Thanks. - Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT INTO ... SELECT question
At 11:15 -0600 11/14/02, Greg Macek wrote: Thanks for the tip! Looks like I can change my date_archived field to timestamp(8), since all I care about for this is the date information (actual time is useless to me). My sql query all of a sudden got a lot simpler. Thanks again for the help! TIMESTAMP values are always stored to the full 14 digits. The 8 affects display only, though that appears to be exactly what you want. On Thu, 2002-11-14 at 11:07, Matthew Baranowski wrote: Hey Greg: A slightly easier way to do this is to use a timestamp field. Timestamp is just a standard mysql data type. When a record is added, it records the current time. When a record is updated, the timestamp field will be set to the time of the update. http://www.mysql.com/doc/en/DATETIME.html The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Thanks, Matt Matthew P Baranowski Data Manager, Office of Educational Assessment University of Washington - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql SELECT question (LEFT JOIN?)
Assume two tables: CREATE TABLE block_ip ( datestamp int(11) NOT NULL default '0', remote_addr char(15) NOT NULL default '', PRIMARY KEY (remote_addr), KEY datestamp (datestamp) ) TYPE=MyISAM; CREATE TABLE brute_force ( datestamp int(11) NOT NULL default '0', remote_addr char(15) NOT NULL default '', remote_user char(35) NOT NULL default '', KEY remote_addr (remote_addr), KEY datestamp (datestamp), KEY remote_user (remote_user) ) TYPE=MyISAM; Contents of the 'brute_force' table (remote_addr): 1.2.3.4 2.3.4.5 3.4.5.6 4.5.6.7 5.6.7.8 6.7.8.9 Contents of the 'block_ip' table (remote_addr): 2.3.4.5 4.5.6.7 Can someone help me with the query that will select all the 'remote_addr' rows from 'brute_force' that are NOT in the 'block_ip' table? Something like: select brute_force.* from brute_force, block_ip where brute_force.remote_addr != block_ip.remote_addr maybe? I have a feeling it's some sort of left join, and I was never very good at those. :-/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select question
Hi, I have a table called users with the columns firstname and lastname. I would like to do a search on the fullname and have tried: select * from users where (firstname + ' ' + lastname) = John Smith which returns all rows for some reason and not only the rows with users named John Smith (which SQL Server does). Any ideas? Kind regards, Elin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Select question
Elin, Tuesday, September 10, 2002, 12:03:21 PM, you wrote: ER I have a table called users with the columns firstname and lastname. I would ER like to do a search on the fullname and have tried: ER select * from users where (firstname + ' ' + lastname) = John Smith ER which returns all rows for some reason and not only the rows with users ER named John Smith (which SQL Server does). Any ideas? Take a look at CONCAT()/CONCAT_WS() functions: http://www.mysql.com/doc/en/String_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select question
You would have to do something like: SELECT * FROM users WHERE CONCAT(firstname, , lastname) = John Smith That should get you what you want. If your taking your DB from MS SQL to MySQL only a few queries will port directly over. You have to be careful that you follow the MySQL syntax and functions as they are slightly different. -Nick Hi, I have a table called users with the columns firstname and lastname. I would like to do a search on the fullname and have tried: select * from users where (firstname + ' ' + lastname) = John Smith which returns all rows for some reason and not only the rows with users named John Smith (which SQL Server does). Any ideas? Kind regards, Elin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A Date and Select question.
My SQL query problem: I met a strange problem: For example, I have the following table: Mytable: No Name Date Project 1 Bob 2002-05-27Bob's project 2 John -00-00 John's project When I use select * from Mytable WHERE Date = 'WWW'; it gives me the result: 2 John -00-00 John's project I think it should not return any values. Why? Thanks. I. TS. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT Question.
At 01:22 AM 3/3/2002 , you wrote: I need to pull a variable number of fields from a table from the last inputted fields. For example, instead of doing something like SELECT * FROM table I am looking for a way to do something like this(hypothetical, I don't really know what I should do). SELECT LAST_TEN_FIELDS FROM table which would then pull the most recent ten rows from the table. The reason why I am asking this mailling list is because I am writing a small program using PHP and want to get the ten most recent fields. However, since it is PHP and the script is going to have high traffic, I need to know the most efficient way of pulling the last ten fields. I know I can do it through PHP by sorting out the results, but I want the whole thing to be as streamlined as possible and don't want to have the PHP script in 4 months chugging away for hours trying to work with the data of 1000 fields pulled from mySQL. This is the structure I have in mind for the table, and this is not 100% official, but was the way I had initially planned it to go. id INT NOT NULL AUTO_INCREMENT, data VARCHAR(200), PRIMARY KEY(id) The script is going to take a little bit of data, and is only going to need to display the most recent(top ten probably) results entered, which is why I need to know if there is an efficient way to do this in mySQL without causing thousands of chunks of data to have to be handled by PHP. All help is appreciated, -Eric Eric, You could try something like: select * from table order by id desc limit 10; The reverse sort will get you there. Just make sure the column is indexed to make it fast, which in your case it is. :) Brent _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT SELECT QUESTION
At 8:44 PM -0500 9/9/01, Marcus Young wrote: Hi, I'm fairly new to MySQL. I'm trying to insert data into a table where one field is derived from a SELECT on another table (a key) and the other fields are defined directly (eg field_01=abcd) . The formats I have been trying don't appear to be correct. Is the commbined insert possible or what is the appropriate technique? thanks in anticipation, Marcus INSERT INTO tbl2 (col1, col2, col3) SELECT key, 'abcd', 'efgh' FROM tbl2 -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT SELECT QUESTION
Try to look at page 442 to 446 of the mySQL manual, it may give some help on your field creation using insert select. Regards, Calvin Chin [EMAIL PROTECTED] Ipmuda Berhad - Building materials for tomorrow's world ! -Original Message- From: Marcus Young [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Sun, 09 Sep 2001 20:44:23 -0500 Subject: INSERT SELECT QUESTION Hi, I'm fairly new to MySQL. I'm trying to insert data into a table where one field is derived from a SELECT on another table (a key) and the other fields are defined directly (eg field_01=abcd) . The formats I have been trying don't appear to be correct. Is the commbined insert possible or what is the appropriate technique? thanks in anticipation, Marcus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT question.
On 30 May 2001, at 10:14, Paul DuBois wrote: At 9:41 AM -0700 5/30/01, Richard Reina wrote: I am stuck on a select query and was wondering if someone could help. I have I've written a database app. that helps me run my business (trucking). I need however to write a query that shows me all of the loads that are delivered but not billed (invoiced). Which means that I have to select the loads that are delivered but do not have an entry in the INVOICED table -- since an entry is made in the INVOICED table whenever a load is billed. I know the query below won't work. Can someone please help me fix it? SELECT l.load_no l.date FROM loads l, invoiced i WHERE l.dlvr_date 0 AND l.load_no != i.load_no This is a job for LEFT JOIN. SELECT l.load_no, l.date FROM loads l LEFT JOIN invoiced i ON l.load_no = i.load_no WHERE i.load_no IS NULL Can you in sql also have FROM table_a, table_b LEFT JOIN table_c ON WHERE For example my query is my $query = SELECT message.message_id, message.user, message.for_user, message.sent_date_time, message.subject, message.id FROM subject_project, message LEFT JOIN read_message ON message.message_id = read_message.message_id WHERE read_message.user = '$user' AND message.sent_date_time $new_messages_from_date AND message.id = subject_project.id AND subject_project.location_id = 1 AND read_message.user IS NULL ORDER BY message.sent_date_time ; Until now I have just selected the messages that have been sent for the past 30 days. Then checking for each message with another query have they read the message or not. If not display it. The solution suggested by Paul worked fine for another part of my message system where I don't need to the subject_project table. An alternative would be to keep the location_id field also in the message table. Any ideas are welcome. Scott _ scott alexander tietoverkkosuunnittelija humak amk - finland +358(0)407505640 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
