Re: OT: SQL Question
Jeff, I would use a join table, teacher_flights. create table teacher_flights( teacher_id int(11) not null, flight_id int(11) not null, primary key(teacher_id, flight_id)); Dave From: Mark Phillips m...@phillipsmarketing.biz To: Mysql List mysql@lists.mysql.com Sent: Friday, March 23, 2012 7:28 PM Subject: OT: SQL Question My question is not specific to MySQL, even though I am using a MySQL db for this project. I have a servlet/jsp/MySQL web site in production, and there are about 2,000 records in the flights table. One of the foreign keys is teacher_id. Up to this point, there is a one to many relationship between teacher_id and the data in the flights table. I need to change the data model to allow for a many to many relationship between teacher_id and the data in the flight table. What is the best way to do this? Thanks, Mark
OT: SQL Question
My question is not specific to MySQL, even though I am using a MySQL db for this project. I have a servlet/jsp/MySQL web site in production, and there are about 2,000 records in the flights table. One of the foreign keys is teacher_id. Up to this point, there is a one to many relationship between teacher_id and the data in the flights table. I need to change the data model to allow for a many to many relationship between teacher_id and the data in the flight table. What is the best way to do this? Thanks, Mark
Re: OT: SQL Question
A many-to-many is generally best accomplished with a third linking table which contains the ids of the 2 records being linked ie. create table tflink ( flightid int; teacherid int; ); On Fri, Mar 23, 2012 at 10:28 PM, Mark Phillips m...@phillipsmarketing.biz wrote: My question is not specific to MySQL, even though I am using a MySQL db for this project. I have a servlet/jsp/MySQL web site in production, and there are about 2,000 records in the flights table. One of the foreign keys is teacher_id. Up to this point, there is a one to many relationship between teacher_id and the data in the flights table. I need to change the data model to allow for a many to many relationship between teacher_id and the data in the flight table. What is the best way to do this? Thanks, Mark -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
SQL Question
Hi i request a small help for know if it's possible. Anyone know if they have a Sql request for search the best value at one information: Sample: i have a table with: ID int 5 Chaine int 16 and this entry: 1 12345 2 123 3 12 i am search a request in WHERE : Chaine LIKE '123456789' and if he don't have into the database, he test Chaine LIKE '12345678' etc .. and when he arrive at 12345, he sent the ID information thanks for your help (PS: i use Perl for extract data) Jerome Sorry for my english -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: SQL Question
you'll need to write a recursive routine to call the same sql statement
and iterate from the largest value and attenuate that value by 1 each time
# file: test.pl been a few years but this should work
#initialize your variable that you will attenuate
my $global = 9876543210;
#number of rows actually returned
my $affectedrows=0;
#the offset for constructing the next global variable
my $offset=10;
#stay in this loop until we have successful result
#OR there are no rows found
while($affectedrows==0)
{
do_it();
}
if($affectedrows==99) print(no rows found);
sub do_it
{
# DEFINE A MySQL QUERY
$myquery = SELECT * FROM $tablename where column1=$global;
$execute = $connect-query($myquery);
#attenuate 1 character off
$offset = $offset -1;
$global = substr($global, 0, $offset);
#test for global having no value
if($offset==0) $affectedrows=99;
else $affectedrows = $execute-affectedrows($myquery);
}
http://www.tizag.com/perlT/perlmysqlquery.php
(notice the site is written in PHP)
Martin Gainty
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
Date: Tue, 3 Nov 2009 18:17:52 +0100
From: n...@phibee.net
To: mysql@lists.mysql.com
Subject: SQL Question
Hi
i request a small help for know if it's possible.
Anyone know if they have a Sql request for
search the best value at one information:
Sample:
i have a table with:
ID int 5
Chaine int 16
and this entry:
1 12345
2 123
3 12
i am search a request in WHERE :
Chaine LIKE '123456789' and if he don't have into the database,
he test Chaine LIKE '12345678' etc ..
and when he arrive at 12345, he sent the ID information
thanks for your help
(PS: i use Perl for extract data)
Jerome
Sorry for my english
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
_
Bing brings you maps, menus, and reviews organized in one place.
http://www.bing.com/search?q=restaurantsform=MFESRPpubl=WLHMTAGcrea=TEXT_MFESRP_Local_MapsMenu_Resturants_1x1
Re: SQL question: find items tagged with specific tags
Thanks, Sebastian!
I have tried this one before. The problem is that it finds all items
the tags of which include EITHER 'blue' OR 'red', not 'blue' AND 'red':
mysql SELECT DISTINCT items.title from items inner join taggings on
(items.id = taggings.item_id) inner join tags on (tags.id =
taggings.tag_id) WHERE tags.name IN ('red', 'blue');
+---+
| title |
+---+
| tagged_red|
| tagged_red_and_blue |
| tagged_red_and_green |
+---+
Do you have an idea how to create an AND query?
Ingo
On Apr 22, 2008, at 5:42 PM, Sebastian Mendel wrote:
Ingo Weiss schrieb:
Hi all,
I have an application where items can be tagged. There are three
tables
'items', 'taggings' and 'tags' joined together like this:
items inner join taggings on (items.id = taggings.item_id) inner join
tags on (tags.id = taggings.tag_id)
Now I have been struggling for some time now with coming up with
the SQL
to find the items the tags of which include a specified list of tag
names. Example:
I am looking for items tagged with 'blue' and 'red'. This should
find me:
- items tagged with 'blue' and 'red'
- items tagged with 'blue', 'red' and 'green'
SELECT DISTINCT items.*
FROM [your join above]
WHERE tags.name IN ('blue', 'red');
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question: find items tagged with specific tags
Ingo Weiss schrieb:
Thanks, Sebastian!
I have tried this one before. The problem is that it finds all items the
tags of which include EITHER 'blue' OR 'red', not 'blue' AND 'red':
oh ... and ..., i missred
SELECT DISTINCT items.*
FROM items
INNER JOIN taggings
ON items.id = taggings.item_id
INNER JOIN tags
ON tags.id = taggings.tag_id
AND tags.name = 'blue'
AND tags.name = 'red';
or
SELECT DISTINCT items.*
COUNT(items.id)
FROM [your join above]
WHERE tags.name IN ('blue', 'red')
HAVING COUNT(items.id) = 2;
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL question: find items tagged with specific tags
Hi all, I have an application where items can be tagged. There are three tables 'items', 'taggings' and 'tags' joined together like this: items inner join taggings on (items.id = taggings.item_id) inner join tags on (tags.id = taggings.tag_id) Now I have been struggling for some time now with coming up with the SQL to find the items the tags of which include a specified list of tag names. Example: I am looking for items tagged with 'blue' and 'red'. This should find me: - items tagged with 'blue' and 'red' - items tagged with 'blue', 'red' and 'green' Any help is very much appreciated! Ingo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question: find items tagged with specific tags
Ingo Weiss schrieb:
Hi all,
I have an application where items can be tagged. There are three tables
'items', 'taggings' and 'tags' joined together like this:
items inner join taggings on (items.id = taggings.item_id) inner join
tags on (tags.id = taggings.tag_id)
Now I have been struggling for some time now with coming up with the SQL
to find the items the tags of which include a specified list of tag
names. Example:
I am looking for items tagged with 'blue' and 'red'. This should find me:
- items tagged with 'blue' and 'red'
- items tagged with 'blue', 'red' and 'green'
SELECT DISTINCT items.*
FROM [your join above]
WHERE tags.name IN ('blue', 'red');
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question
Is it just this line I need to change? INNER JOIN url_categories uc ON uc.ID=bt.category_ID; Would it change to something like: INNER JOIN url_categories uc ON CAST(uc.ID as CHAR)=delimit(bt.category_ID) Just guessing! Thanks - that's what I thought. I really don't have much experience with mySQL. If it's not too much trouble, could someone give me a bit more help on how to do that please? Ed. no, those won't match based on just the datatype change.. you will have to define a user defined function to do those comparisons. On 6/14/07, Edward Quick [EMAIL PROTECTED] wrote: Thanks, that's interesting. Actually the uc.ID column is still type tinyint as it holds only one number, but are you saying if I change this to varchar my query will work e.g. 15 = 15:17 would work? What is the type of the 'uc.ID' column? If it's varchar, your match will work fine. If it's an integer type, you are going to have a problem because you have bt.category_ID holding things which can't be represented as integers and will therefore never match. IF both column type are being changed here, your query will work fine as is. - michael dykman On 6/14/07, Edward Quick [EMAIL PROTECTED] wrote: Hi, I have the following mySQL query in my script which has been working fine but due to a recent change, I had to modify one of the columns, bt.category_ID. This used to be defined as tinyint(3) but I've changed that now to varchar(20) as it needs to hold values such as 15, or 74:79 or 43:56:113 In light of that, could anyone tell me what I need to change in my SQL to get it working please? Presuambly uc.ID=bt.category_ID won't work anymore. INSERT IGNORE $visitstable (url_scheme_ID, url_server_ID, url_path_ID, url_query_ID, url_category_ID) SELECT DISTINCT usc.ID, us.ID, up.ID, uq.ID, uc.ID FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN $pathstable up ON up.path=bt.path INNER JOIN url_schemes usc ON usc.ID=bt.scheme_ID INNER JOIN $queriestable uq ON uq.query=bt.query INNER JOIN url_categories uc ON uc.ID=bt.category_ID; Many thanks, Ed. _ Win tickets to the sold out Live Earth concert! http://liveearth.uk.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. _ Win tickets to the sold out Live Earth concert! http://liveearth.uk.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. _ Play your part in making history - Email Britain! http://www.emailbritain.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Play your part in making history - Email Britain! http://www.emailbritain.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL question
Hi, I have the following mySQL query in my script which has been working fine but due to a recent change, I had to modify one of the columns, bt.category_ID. This used to be defined as tinyint(3) but I've changed that now to varchar(20) as it needs to hold values such as 15, or 74:79 or 43:56:113 In light of that, could anyone tell me what I need to change in my SQL to get it working please? Presuambly uc.ID=bt.category_ID won't work anymore. INSERT IGNORE $visitstable (url_scheme_ID, url_server_ID, url_path_ID, url_query_ID, url_category_ID) SELECT DISTINCT usc.ID, us.ID, up.ID, uq.ID, uc.ID FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN $pathstable up ON up.path=bt.path INNER JOIN url_schemes usc ON usc.ID=bt.scheme_ID INNER JOIN $queriestable uq ON uq.query=bt.query INNER JOIN url_categories uc ON uc.ID=bt.category_ID; Many thanks, Ed. _ Win tickets to the sold out Live Earth concert! http://liveearth.uk.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question
Thanks, that's interesting. Actually the uc.ID column is still type tinyint as it holds only one number, but are you saying if I change this to varchar my query will work e.g. 15 = 15:17 would work? What is the type of the 'uc.ID' column? If it's varchar, your match will work fine. If it's an integer type, you are going to have a problem because you have bt.category_ID holding things which can't be represented as integers and will therefore never match. IF both column type are being changed here, your query will work fine as is. - michael dykman On 6/14/07, Edward Quick [EMAIL PROTECTED] wrote: Hi, I have the following mySQL query in my script which has been working fine but due to a recent change, I had to modify one of the columns, bt.category_ID. This used to be defined as tinyint(3) but I've changed that now to varchar(20) as it needs to hold values such as 15, or 74:79 or 43:56:113 In light of that, could anyone tell me what I need to change in my SQL to get it working please? Presuambly uc.ID=bt.category_ID won't work anymore. INSERT IGNORE $visitstable (url_scheme_ID, url_server_ID, url_path_ID, url_query_ID, url_category_ID) SELECT DISTINCT usc.ID, us.ID, up.ID, uq.ID, uc.ID FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN $pathstable up ON up.path=bt.path INNER JOIN url_schemes usc ON usc.ID=bt.scheme_ID INNER JOIN $queriestable uq ON uq.query=bt.query INNER JOIN url_categories uc ON uc.ID=bt.category_ID; Many thanks, Ed. _ Win tickets to the sold out Live Earth concert! http://liveearth.uk.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. _ Win tickets to the sold out Live Earth concert! http://liveearth.uk.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question
Thanks - that's what I thought. I really don't have much experience with mySQL. If it's not too much trouble, could someone give me a bit more help on how to do that please? Ed. no, those won't match based on just the datatype change.. you will have to define a user defined function to do those comparisons. On 6/14/07, Edward Quick [EMAIL PROTECTED] wrote: Thanks, that's interesting. Actually the uc.ID column is still type tinyint as it holds only one number, but are you saying if I change this to varchar my query will work e.g. 15 = 15:17 would work? What is the type of the 'uc.ID' column? If it's varchar, your match will work fine. If it's an integer type, you are going to have a problem because you have bt.category_ID holding things which can't be represented as integers and will therefore never match. IF both column type are being changed here, your query will work fine as is. - michael dykman On 6/14/07, Edward Quick [EMAIL PROTECTED] wrote: Hi, I have the following mySQL query in my script which has been working fine but due to a recent change, I had to modify one of the columns, bt.category_ID. This used to be defined as tinyint(3) but I've changed that now to varchar(20) as it needs to hold values such as 15, or 74:79 or 43:56:113 In light of that, could anyone tell me what I need to change in my SQL to get it working please? Presuambly uc.ID=bt.category_ID won't work anymore. INSERT IGNORE $visitstable (url_scheme_ID, url_server_ID, url_path_ID, url_query_ID, url_category_ID) SELECT DISTINCT usc.ID, us.ID, up.ID, uq.ID, uc.ID FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN $pathstable up ON up.path=bt.path INNER JOIN url_schemes usc ON usc.ID=bt.scheme_ID INNER JOIN $queriestable uq ON uq.query=bt.query INNER JOIN url_categories uc ON uc.ID=bt.category_ID; Many thanks, Ed. _ Win tickets to the sold out Live Earth concert! http://liveearth.uk.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. _ Win tickets to the sold out Live Earth concert! http://liveearth.uk.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. _ Play your part in making history - Email Britain! http://www.emailbritain.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Question: alternative to crazy left joins?
Thanks Shawn, Believe you me, I share your reaction to this architecture...I had to spend 2 hours coding a ruby script to get the data into the kludgy form needed for the data import (though I do find that thing kind of fun...but it's not the best use of my time on the job). Fortunately the data limit doesn't seem to be imposed by the apps underlying db schema, only by the importing process, as contacts can be associated with an unlimited number of donations in the program but only 10 donations can be imported at a time. Very few database apps I've used seem to be free from at least a few architectural or interface flaws such as this one, and so often it seems one needs to reach a compromise in order to settle on a reasonably priced piece of software with an intuitive, powerful interface and decent engineering. Still, though, I wonder if there's way to solve this problem using straight SQL. Ethan -- View this message in context: http://www.nabble.com/SQL-Question%3A-alternative-to-crazy-left-joins--t1357877.html#a3654455 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Question: alternative to crazy left joins?
Hi All,
I'm migrating to a contact relationship management system (CRM) for one of
my clients from a proprietary Access database. The CRM system can import
our donor's contact history, but only in a non-normalized format with up to
10 donations per contact record in a single row of an Excel spreadsheet.
Needless to say Access SQL doesn't seem up to the task of converting our
Donations table (individual donation indexed by ID and ContactID) to this
funky format, so I've fired up MySQL in hopes of getting this done. But I'm
not sure how to proceed. I tried using a nutty left join on ContactID but
making sure the DonatioIDs for each joined row were different, but that
didn't work (the SQL was:
select a.ContactID,a.DonationID as aID,b.DonationID as bID,c.DonationID as
cID from (fcdcTomcatsDonations as a LEFT JOIN fcdcTomcatsDonations as b ON
a.ContactID=b.ContactID) LEFT JOIN fcdcTomcatsDonations as c ON
a.ContactID=c.ContactID WHERE a.DonationID != b.DonationID AND b.DonationID
!= c.DonationID and a.DonationID != c.DonationID;
It has numerous faults:
* It's a pain to write (and I need to allow for up to 10 donations per
user!)
* It doesn't accound for different variations of the same selection (e.g.
notated as {a.DonationID,b.DonationID,c.DonationID}, {1,2,3}, {2,1,3} and
{3,2,1} are all included in the result)
I'm sure I'm betraying some fundamental SQL ignorance here, but maybe
someone out their can point me in the right direction. What I want is
something like this:
| ContactID| Donation1.* | Donation2.* | Donation3.* | etc...
Thanks in advance...off to Ruby to see if I can code some hack quick to get
this task done with...;)
-Ethan
--
View this message in context:
http://www.nabble.com/SQL-Question%3A-alternative-to-crazy-left-joins--t1357877.html#a3636912
Sent from the MySQL - General forum at Nabble.com.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Question: alternative to crazy left joins?
eth1 [EMAIL PROTECTED] wrote on 03/28/2006 03:04:13 PM:
Hi All,
I'm migrating to a contact relationship management system (CRM) for
one of
my clients from a proprietary Access database. The CRM system can
import
our donor's contact history, but only in a non-normalized format with up
to
10 donations per contact record in a single row of an Excel spreadsheet.
Needless to say Access SQL doesn't seem up to the task of converting our
Donations table (individual donation indexed by ID and ContactID) to
this
funky format, so I've fired up MySQL in hopes of getting this done. But
I'm
not sure how to proceed. I tried using a nutty left join on ContactID
but
making sure the DonatioIDs for each joined row were different, but that
didn't work (the SQL was:
select a.ContactID,a.DonationID as aID,b.DonationID as bID,c.DonationID
as
cID from (fcdcTomcatsDonations as a LEFT JOIN fcdcTomcatsDonations as b
ON
a.ContactID=b.ContactID) LEFT JOIN fcdcTomcatsDonations as c ON
a.ContactID=c.ContactID WHERE a.DonationID != b.DonationID AND
b.DonationID
!= c.DonationID and a.DonationID != c.DonationID;
It has numerous faults:
* It's a pain to write (and I need to allow for up to 10 donations per
user!)
* It doesn't accound for different variations of the same selection
(e.g.
notated as {a.DonationID,b.DonationID,c.DonationID}, {1,2,3}, {2,1,3}
and
{3,2,1} are all included in the result)
I'm sure I'm betraying some fundamental SQL ignorance here, but maybe
someone out their can point me in the right direction. What I want is
something like this:
| ContactID| Donation1.* | Donation2.* | Donation3.* | etc...
Thanks in advance...off to Ruby to see if I can code some hack quick to
get
this task done with...;)
-Ethan
--
View this message in context: http://www.nabble.com/SQL-Question%3A-
alternative-to-crazy-left-joins--t1357877.html#a3636912
Sent from the MySQL - General forum at Nabble.com.
The correct way to model your information is to use the method you
describe as being used in the Access database. That data is normalized.
You even admit that you are denormalizing the data. The new CRM system is
imposing an artificial limit of 10 donations to any single contact. What
real-world rule says that after 10 donations, the contact is done? Or,
what real-world rule says to ignore the 11th or older contribution? These
artificial limit of only 10 donations in the donation history would be a
deal breakers for me.
Basically, the new design breaks several of the fundamental rules of
efficient database design. I would seriously doubt the capabilities of the
new system if this is how the backend is organized. I worry for your
client.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
SQL Question
Hy I want to sum quantites but there is some data that value is negative but users didn't write the - sign before. I can decide which datas are negative. I would like something like that select sum(moved_quantities) from db if moving like 'Move-' then sum seem the move is negative else move value is positive Is it possible? Joe - Yahoo! Cars NEW - sell your car and browse thousands of new and used cars online search now -
Fw: SQL Question
Oops, I meant to send this to the list. Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: Mester József [EMAIL PROTECTED] Sent: Friday, January 06, 2006 8:44 AM Subject: Re: SQL Question - Original Message - From: Mester József [EMAIL PROTECTED] To: Mysql list mysql@lists.mysql.com Sent: Friday, January 06, 2006 3:42 AM Subject: SQL Question Hy I want to sum quantites but there is some data that value is negative but users didn't write the - sign before. I can decide which datas are negative. I would like something like that select sum(moved_quantities) from db if moving like 'Move-' then sum seem the move is negative else move value is positive Is it possible? If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.14/222 - Release Date: 05/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Question
Hello. Do you want something similare to this: SELECT SUM(IF(moving like 'Move-',-moved_quantities,moved_quantites)) FROM DB; Have a look here: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Mester József wrote: Hy I want to sum quantites but there is some data that value is negative but users didn't write the - sign before. I can decide which datas are negative. I would like something like that select sum(moved_quantities) from db if moving like 'Move-' then sum seem the move is negative else move value is positive Is it possible? Joe - Yahoo! Cars NEW - sell your car and browse thousands of new and used cars online search now - -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: SQL Question
Hy If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Thank you. Actually my first thing was update but my SQL knowledge is weak and I don't want to mess the database. I started a script which is update bad records on a copy of that database. However I didn't solve the update problem. My script is in (PL/SQL): integer a; integer b; varchar tr; varchar ra; varchar ke; varchar moti; begin select tm.tr,tm.ra,tm.ke,tm.moti,tm.mome into tr,ke,moti,a from ev98nv_tm tm where MOTI like 'Rakt.kozi-' or MOTI like 'Kiadas -' and tm.EV like '2005' if (a 0) then a=b; b = 0- b; update ev98nv_tm set mome=b where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; end; But it is not working. The Primary index is tr+ra+ke+moti Rhino ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Question
- Original Message - From: Mester József [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql mysql@lists.mysql.com Sent: Friday, January 06, 2006 12:07 PM Subject: Re: SQL Question Hy If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Thank you. Actually my first thing was update but my SQL knowledge is weak and I don't want to mess the database. I started a script which is update bad records on a copy of that database. However I didn't solve the update problem. My script is in (PL/SQL): integer a; integer b; varchar tr; varchar ra; varchar ke; varchar moti; begin select tm.tr,tm.ra,tm.ke,tm.moti,tm.mome into tr,ke,moti,a from ev98nv_tm tm where MOTI like 'Rakt.kozi-' or MOTI like 'Kiadas -' and tm.EV like '2005' if (a 0) then a=b; b = 0- b; update ev98nv_tm set mome=b where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; end; But it is not working. The Primary index is tr+ra+ke+moti Rhino I'm sorry but I've just got too much to do to help you today. Perhaps Gleb's suggestion can help you do the summing as you originally wanted or perhaps someone else can jump in with suggestions. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.14/222 - Release Date: 05/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Re: SQL Question - Bayesian Filter detected spam
Why not just use the ABS Function update ev98nv_tm set mome=ABS(b) where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; 12.4.2. Mathematical Functions All mathematical functions return NULL in the event of an error. ABS(X) Returns the absolute value of X. mysql SELECT ABS(2); - 2 mysql SELECT ABS(-32); - 32 This function is safe to use with BIGINT values. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, January 06, 2006 12:19 PM To: Mester József; mysql Subject: [SPAM] - Re: SQL Question - Bayesian Filter detected spam - Original Message - From: Mester József [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql mysql@lists.mysql.com Sent: Friday, January 06, 2006 12:07 PM Subject: Re: SQL Question Hy If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Thank you. Actually my first thing was update but my SQL knowledge is weak and I don't want to mess the database. I started a script which is update bad records on a copy of that database. However I didn't solve the update problem. My script is in (PL/SQL): integer a; integer b; varchar tr; varchar ra; varchar ke; varchar moti; begin select tm.tr,tm.ra,tm.ke,tm.moti,tm.mome into tr,ke,moti,a from ev98nv_tm tm where MOTI like 'Rakt.kozi-' or MOTI like 'Kiadas -' and tm.EV like '2005' if (a 0) then a=b; b = 0- b; update ev98nv_tm set mome=b where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; end; But it is not working. The Primary index is tr+ra+ke+moti Rhino I'm sorry but I've just got too much to do to help you today. Perhaps Gleb's suggestion can help you do the summing as you originally wanted or perhaps someone else can jump in with suggestions. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.14/222 - Release Date: 05/01/2006 -- 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]
FW: Re: SQL Question
Why not just use the ABS Function update ev98nv_tm set mome=ABS(b) where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; 12.4.2. Mathematical Functions All mathematical functions return NULL in the event of an error. ABS(X) Returns the absolute value of X. mysql SELECT ABS(2); - 2 mysql SELECT ABS(-32); - 32 This function is safe to use with BIGINT values. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, January 06, 2006 12:19 PM To: Mester József; mysql Subject: [SPAM] - Re: SQL Question - Bayesian Filter detected spam - Original Message - From: Mester József [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql mysql@lists.mysql.com Sent: Friday, January 06, 2006 12:07 PM Subject: Re: SQL Question Hy If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Thank you. Actually my first thing was update but my SQL knowledge is weak and I don't want to mess the database. I started a script which is update bad records on a copy of that database. However I didn't solve the update problem. My script is in (PL/SQL): integer a; integer b; varchar tr; varchar ra; varchar ke; varchar moti; begin select tm.tr,tm.ra,tm.ke,tm.moti,tm.mome into tr,ke,moti,a from ev98nv_tm tm where MOTI like 'Rakt.kozi-' or MOTI like 'Kiadas -' and tm.EV like '2005' if (a 0) then a=b; b = 0- b; update ev98nv_tm set mome=b where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; end; But it is not working. The Primary index is tr+ra+ke+moti Rhino I'm sorry but I've just got too much to do to help you today. Perhaps Gleb's suggestion can help you do the summing as you originally wanted or perhaps someone else can jump in with suggestions. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.14/222 - Release Date: 05/01/2006 -- 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: SQL question
Hi All! I have a MySQL database (I have them using MySql at work for more stuff now!), and the definition is as follows: uid mediumint(6) NOT NULL auto_increment, ym varchar(6) default NULL, fileid varchar(8) default NULL, off char(3) default NULL, PRIMARY KEY (`uid`) TYPE=MyISAM uid is not really of importance for what I need - it is just a unique identifier of records in the database. The variables of importance are: I have YM (which is year/month and looks like this: 200503 for example) and fileid (an 8 digit unique identifier of a person) and off (a three digit identifier which is really just an office number) The ym actually is fiscal and goes from 200404 to 200503. I would like to make a little report showing how many people have changed the office with which they deal. It would be a) transfers into an office or b) transfers out of an office (of course leaving an office implies joining another one). It is possible for people to stop dealing with these offices completely. We just want to know who has transferred to or from offices during this fiscal period. Is it possible to do this with plain vanilla sql? All I can come up with is a loop using php, but I would love to know how to do it with just sql. . . I appreciate any time anyone has to consider this problem! Thanks heaps! -Warren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I ... SQL question
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: SELECT DISTINCT place FROM a ; place --- south west east Note that the place north does not appear in the last result because north was only visited by bob in 2005 and kim in 2004, records which are not included in the limited result. Using derived tables, this would be something like SELECT DISTINCT a.place FROM ( SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1) AS d JOIN a ON a.name = d.name AND a.year = d.year; I would like to be compatible with 3.23.xx. The pre-4.1 workaround for derived tables are temporary tables: CREATE TEMPORARY TABLE tmp AS SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; SELECT DISTINCT a.place FROM tmp d JOIN a ON a.name = d.name AND a.year = d.year; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I ... SQL question
[EMAIL PROTECTED] wrote on 01/17/2005 06:45:22 PM: Hi there: I have a How do I... SQL question regarding selecting distinct values from a field not included in an aggregated query when LIMIT is in effect, illustrated by the following example: Table a contains the names of individuals, the places they have visited and the year in which they were visited. Let's see who has visited where and when: SELECT * FROM a; name place year -- --- -- kimnorth 2004 kimsouth 2003 kimsouth 2003 bobwest2004 bobwest2004 bobwest2003 joesouth 2004 joesouth 2005 suewest2004 bobeast2003 joeeast2004 joeeast2004 suesouth 2004 bobnorth 2004 bobnorth 2005 Summarize data by number of places visited by year: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC; count name year --- -- -- 3 bob2004 3 joe2004 2 bob2003 2 kim2003 2 sue2004 1 bob2005 1 kim2004 1 joe2005 Return only four rows beginning at second row: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; count name year --- -- -- 3 joe2004 s,e,e 2 bob2003 w,e 2 kim2003 s,s 2 sue2004 s,w Select only places visited included in LIMITed query: SELECT DISTINCT place FROM a ; Put the results of the LIMITed query into a temporary table and re-query. CREATE TEMPORARY TABLE tmpStep1 ( freq int , name varchar(25) , year int ); INSERT tmpStep1 (freq, name, year) SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; select distinct a.place from tmpStep1 ts1 INNER JOIN a on a.name = ts1.name; Then you should get the list: place --- south west east Note that the place north does not appear in the last result because north was only visited by bob in 2005 and kim in 2004, records which are not included in the limited result. Any help appreciated. I would like to be compatible with 3.23.xx. -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Basically, if you need to treat a set of results as source data, your best option is to make a table (temporary or permanent) out of your results. Then when you are through, cleanup for the next time. DROP TEMPORARY TABLE tmpStep1; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: How do I ... SQL question
Return only four rows beginning at second row: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; count name year --- -- -- 3 joe2004 s,e,e 2 bob2003 w,e 2 kim2003 s,s 2 sue2004 s,w Select only places visited included in LIMITed query: SELECT DISTINCT place FROM a ; Put the results of the LIMITed query into a temporary table and re-query. CREATE TEMPORARY TABLE tmpStep1 ( freq int , name varchar(25) , year int ); INSERT tmpStep1 (freq, name, year) SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; select distinct a.place from tmpStep1 ts1 INNER JOIN a on a.name = ts1.name; Then you should get the list: place --- south west east Thanks to all who responded. The inner join does what I wanted. -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do I ... SQL question
Hi there: I have a How do I... SQL question regarding selecting distinct values from a field not included in an aggregated query when LIMIT is in effect, illustrated by the following example: Table a contains the names of individuals, the places they have visited and the year in which they were visited. Let's see who has visited where and when: SELECT * FROM a; name place year -- --- -- kimnorth 2004 kimsouth 2003 kimsouth 2003 bobwest2004 bobwest2004 bobwest2003 joesouth 2004 joesouth 2005 suewest2004 bobeast2003 joeeast2004 joeeast2004 suesouth 2004 bobnorth 2004 bobnorth 2005 Summarize data by number of places visited by year: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC; count name year --- -- -- 3 bob2004 3 joe2004 2 bob2003 2 kim2003 2 sue2004 1 bob2005 1 kim2004 1 joe2005 Return only four rows beginning at second row: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; count name year --- -- -- 3 joe2004 s,e,e 2 bob2003 w,e 2 kim2003 s,s 2 sue2004 s,w Select only places visited included in LIMITed query: SELECT DISTINCT place FROM a ; place --- south west east Note that the place north does not appear in the last result because north was only visited by bob in 2005 and kim in 2004, records which are not included in the limited result. Any help appreciated. I would like to be compatible with 3.23.xx. -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I ... SQL question
Can't you do:
SELECT count(*) AS count, name, year FROM a
WHERE place IN ('south','west','east')
GROUP BY name, year
ORDER BY count DESC, name ASC
LIMIT 4 OFFSET 1;
[EMAIL PROTECTED] wrote:
Hi there:
I have a How do I... SQL question regarding selecting
distinct values from a field not included in an aggregated
query when LIMIT is in effect, illustrated by the
following example:
Table a contains the names of individuals, the places
they have visited and the year in which they were visited.
Let's see who has visited where and when:
SELECT * FROM a;
name place year
-- --- --
kimnorth 2004
kimsouth 2003
kimsouth 2003
bobwest2004
bobwest2004
bobwest2003
joesouth 2004
joesouth 2005
suewest2004
bobeast2003
joeeast2004
joeeast2004
suesouth 2004
bobnorth 2004
bobnorth 2005
Summarize data by number of places visited by year:
SELECT count(*) AS count, name, year FROM a
GROUP BY name, year
ORDER BY count DESC, name ASC;
count name year
--- -- --
3 bob2004
3 joe2004
2 bob2003
2 kim2003
2 sue2004
1 bob2005
1 kim2004
1 joe2005
Return only four rows beginning at second row:
SELECT count(*) AS count, name, year FROM a
GROUP BY name, year
ORDER BY count DESC, name ASC
LIMIT 4 OFFSET 1;
count name year
--- -- --
3 joe2004 s,e,e
2 bob2003 w,e
2 kim2003 s,s
2 sue2004 s,w
Select only places visited included in LIMITed query:
SELECT DISTINCT place FROM a ;
place
---
south
west
east
Note that the place north does not appear in the last result
because north was only visited by bob in 2005 and kim in 2004,
records which are not included in the limited result.
Any help appreciated.
I would like to be compatible with 3.23.xx.
-Bob
--
Scott Baker
Canby Telephone - Network Administrator - RHCE
Ph: 503.266.8253
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General Sql question
On Fri, 03 Dec 2004 10:58:30 -0700, Steve Grosz wrote: I wrote my query as select Cust_ID, Cust_Name from mailings where ucase(Name) = ucase(Cust_Name) When it runs, I get a error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Grosz = Cust_Name' at line 3 Use cfqueryparam for all your parameters. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General Sql question
I am kinda new to SQL, and am having a problem trying to get something done. I'm trying to search for usernames in one of my tables. The name is stored as firstname lastname. I wrote my query as select Cust_ID, Cust_Name from mailings where ucase(Name) = ucase(Cust_Name) When it runs, I get a error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Grosz = Cust_Name' at line 3 It seems that its only searching for the name after the space between the first and last name. How can I correct this in the query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General Sql question
Your sample query is not valid SQL. What tool/language are you using to run this query? There must be something interpreting what you entered and mis-representing your query to the MySQL server. Without that piece of the puzzle I am completely in the dark. It would also help to know what version MySQL server you are using and if you are connecting to your MySQL server through any kind of library (connector/J, ODBC, etc.) which one and what version is it? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Steve Grosz [EMAIL PROTECTED] wrote on 12/03/2004 12:58:30 PM: I am kinda new to SQL, and am having a problem trying to get something done. I'm trying to search for usernames in one of my tables. The name is stored as firstname lastname. I wrote my query as select Cust_ID, Cust_Name from mailings where ucase(Name) = ucase(Cust_Name) When it runs, I get a error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Grosz = Cust_Name' at line 3 It seems that its only searching for the name after the space between the first and last name. How can I correct this in the query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General Sql question
I am writing this by hand, and is being used within Coldfusion. MySql is v 4.1.7 and I am connecting via ODBC. Steve [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Your sample query is not valid SQL. What tool/language are you using to run this query? There must be something interpreting what you entered and mis-representing your query to the MySQL server. Without that piece of the puzzle I am completely in the dark. It would also help to know what version MySQL server you are using and if you are connecting to your MySQL server through any kind of library (connector/J, ODBC, etc.) which one and what version is it? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Steve Grosz [EMAIL PROTECTED] wrote on 12/03/2004 12:58:30 PM: I am kinda new to SQL, and am having a problem trying to get something done. I'm trying to search for usernames in one of my tables. The name is stored as firstname lastname. I wrote my query as select Cust_ID, Cust_Name from mailings where ucase(Name) = ucase(Cust_Name) When it runs, I get a error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Grosz = Cust_Name' at line 3 It seems that its only searching for the name after the space between the first and last name. How can I correct this in the query? -- 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: General Sql question
Does Name exist as a column in your table, or is it a ColdFusion variable? I know very little about how ColdFusion works, but it does parse the query, and alter it, before it gets sent to ODBC. Just looks like it's using ucase(Name) as a coldfusion function, then replacing it in the query. Just something to look into. You would probably have much better luck with a ColdFusion list Chris Steve Grosz wrote: I am writing this by hand, and is being used within Coldfusion. MySql is v 4.1.7 and I am connecting via ODBC. Steve [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Your sample query is not valid SQL. What tool/language are you using to run this query? There must be something interpreting what you entered and mis-representing your query to the MySQL server. Without that piece of the puzzle I am completely in the dark. It would also help to know what version MySQL server you are using and if you are connecting to your MySQL server through any kind of library (connector/J, ODBC, etc.) which one and what version is it? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Steve Grosz [EMAIL PROTECTED] wrote on 12/03/2004 12:58:30 PM: I am kinda new to SQL, and am having a problem trying to get something done. I'm trying to search for usernames in one of my tables. The name is stored as firstname lastname. I wrote my query as select Cust_ID, Cust_Name from mailings where ucase(Name) = ucase(Cust_Name) When it runs, I get a error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Grosz = Cust_Name' at line 3 It seems that its only searching for the name after the space between the first and last name. How can I correct this in the query? -- 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]
SQL question.... Trying to improve upon my PHP solution.
I have a table of members, about 13,000 rows.
Each night I need to shuffle the table. I have a small int column called
random_position. Currently I am creating a position list (based on the count
of the members), shuffle it, then while iterating through the members
assigning them a a position.
$time_start = microtime_float();
$member = new DataObjects_Member();
$number_of_rows = $member-find();
$positions = array();
for ($i = 1; $i = $number_of_rows; $i++) {
$positions[] = $i;
}
shuffle($positions);
while ($member-fetch()) {
// choose a position from the bottom
$position = array_pop($positions);
// set this member with that position
$member-setRandom_position($position);
$member-update();
}
Of course by doing it this way I'm running 13,000 SELECT calls and 13,000
UPDATE calls which can be processor intensive. As of now I'm timing it at
225 seconds but this machine is faster than server.
Is there a simpler / faster SQL query I could use?
Thanks!
~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question.... Trying to improve upon my PHP solution.
At 11:08 AM 11/29/2004, you wrote:
I have a table of members, about 13,000 rows.
Each night I need to shuffle the table. I have a small int column called
random_position. Currently I am creating a position list (based on the count
of the members), shuffle it, then while iterating through the members
assigning them a a position.
$time_start = microtime_float();
$member = new DataObjects_Member();
$number_of_rows = $member-find();
$positions = array();
for ($i = 1; $i = $number_of_rows; $i++) {
$positions[] = $i;
}
shuffle($positions);
while ($member-fetch()) {
// choose a position from the bottom
$position = array_pop($positions);
// set this member with that position
$member-setRandom_position($position);
$member-update();
}
Of course by doing it this way I'm running 13,000 SELECT calls and 13,000
UPDATE calls which can be processor intensive. As of now I'm timing it at
225 seconds but this machine is faster than server.
Is there a simpler / faster SQL query I could use?
Thanks!
~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com
Mike,
Your solution is way too complicated (it makes my head hurt).g
Try this:
set @n=0;
update tmp set rnd = @n := @n + 1 order by RAND()
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question.... Trying to improve upon my PHP solution.
On 11/29/04 12:27 PM, mos [EMAIL PROTECTED] wrote: Mike, Your solution is way too complicated (it makes my head hurt).g Try this: set @n=0; update tmp set rnd = @n := @n + 1 order by RAND() Mike I'll give this a shot. Follow-up question: I've had a lot of trouble with RAND() on my MySQL 3 box not being all that random. This time I'm using 4.0.17-log. Is it going to be any better? ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question.... Trying to improve upon my PHP solution.
- Original Message -
From: Mike Zornek [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, November 29, 2004 12:08 PM
Subject: SQL question Trying to improve upon my PHP solution.
I have a table of members, about 13,000 rows.
Each night I need to shuffle the table. I have a small int column called
random_position. Currently I am creating a position list (based on the
count
of the members), shuffle it, then while iterating through the members
assigning them a a position.
$time_start = microtime_float();
$member = new DataObjects_Member();
$number_of_rows = $member-find();
$positions = array();
for ($i = 1; $i = $number_of_rows; $i++) {
$positions[] = $i;
}
shuffle($positions);
while ($member-fetch()) {
// choose a position from the bottom
$position = array_pop($positions);
// set this member with that position
$member-setRandom_position($position);
$member-update();
}
Of course by doing it this way I'm running 13,000 SELECT calls and 13,000
UPDATE calls which can be processor intensive. As of now I'm timing it at
225 seconds but this machine is faster than server.
Is there a simpler / faster SQL query I could use?
I don't think this is a question about SQL at all; I think you already know
how to write the SQL to select, insert, update or delete rows.
I think that what you really want to know is if there is a more efficient
way to shuffle your rows and that is more a question of choosing a good
programming algorithm and plugging in the SQL you already know.
I have to admit I'm curious about why you need to do this. In many years of
database work, I've never seen the need to shuffle a table before. By the
way, could you clarify what you mean by shuffle? Do you mean that you want
to completely randomize every row and put it in some new, randomly-chosen
slot within the table? Or are you only moving the bottom row to the top
and pushing each of the others down one slot?
I suspect that you might be able to avoid this shuffle entirely and simply
choose rows at random based on their primary key without inserting,
updating, or deleting anything. You probably only have to show the rows in a
random sequence without actually physically moving them within the table. In
that case, simply get a list of the primary key values for every row of the
table and assign them to an array; then use a random number generator to
select keys from the array at random. Of course, this still gives you the
likelihood of choosing some records twice or multiple times and some not at
all; you'd need to make the algorithm recognize when it has grabbed a given
key already so that it ignores it if it is chosen a second time.
If PHP has a data structure analagous to Java's vector, it would be a lot
better choice: you simply put each primary key in its own slot of the
vector, select each key at random via the random number generator, and after
you've selected the row that corresponds to that primary key DELETE THE KEY
FROM THE VECTOR. That ensures that no row is selected twice and every row
gets selected at some point.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question.... Trying to improve upon my PHP solution.
On 11/29/04 1:26 PM, Rhino [EMAIL PROTECTED] wrote: I don't think this is a question about SQL at all; I think you already know how to write the SQL to select, insert, update or delete rows. I think that what you really want to know is if there is a more efficient way to shuffle your rows and that is more a question of choosing a good programming algorithm and plugging in the SQL you already know. I have to admit I'm curious about why you need to do this. In many years of database work, I've never seen the need to shuffle a table before. By the way, could you clarify what you mean by shuffle? Do you mean that you want to completely randomize every row and put it in some new, randomly-chosen slot within the table? Or are you only moving the bottom row to the top and pushing each of the others down one slot? The basic problem is this. We have a search engine that lets photo buyers search our db for photographers. Rather than sort on name the results have always by default been randomized to help level the playing field. And yes this works but it causing frustration to users as when you bookmark the results, or refresh it for various reasons the order is always new. Our idea is to nightly order the members at random and when showing results. I call this shuffling only to make the metaphor. I really mean setting randomized values in a extra column which will be sorted when the user asks for random results. Thanks for you help. ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question.... Trying to improve upon my PHP solution.
At 11:53 AM 11/29/2004, you wrote: On 11/29/04 12:27 PM, mos [EMAIL PROTECTED] wrote: Mike, Your solution is way too complicated (it makes my head hurt).g Try this: set @n=0; update tmp set rnd = @n := @n + 1 order by RAND() Mike I'll give this a shot. Follow-up question: I've had a lot of trouble with RAND() on my MySQL 3 box not being all that random. This time I'm using 4.0.17-log. Is it going to be any better? ~ Mike Mike, According to http://dev.mysql.com/doc/mysql/en/News-4.0.1.html they've improved Rand in version 4.0.1 Changed RAND() initialization so that RAND(N) and RAND(N+1) are more distinct. and in 4.0.10 they Fixed initialization of the random seed for newly created threads to give a better rand() distribution from the first call. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
Anybody? Jeff Burgoon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Sorry, I forgot to mention I am using version 4.0.20a (no subqueries supported) Jeff Burgoon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles Here is what I'd like to do (but can't because the current stable build of MySQL doesn't support subqueries) SELECT MyTable.* FROM (SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2) as Duplicates, MyTable WHERE Duplicates.Region = MyTable.Region Here is what I'm actually doing: CREATE TEMPORARY TABLE Duplicates SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2; SELECT MyTable.* FROM MyTable, Duplicates WHERE MyTable.Region = Duplicates.Region; Can anybody tell me if there is a more efficient way of doing this query? Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
[snip] Anybody? I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles There is no good way to get this in a single query (w/o subqueries). Having applied all sorts of query mangling you would have to be able to carry forward some sort of count or variable in order to draw out the ones where the foreign key was 1. Grouping by the city does not work either as that reduces any count to a one for that record. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
What about select distinct a.region, a.city from mytable a , mytable b where a.region=b.region and a.city b.city Jay Blanchard wrote: [snip] Anybody? I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles There is no good way to get this in a single query (w/o subqueries). Having applied all sorts of query mangling you would have to be able to carry forward some sort of count or variable in order to draw out the ones where the foreign key was 1. Grouping by the city does not work either as that reduces any count to a one for that record. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
[snip] What about select distinct a.region, a.city from mytable a , mytable b where a.region=b.region and a.city b.city [/snip] Crud! Standing too close to the forest and forgot about a self join... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
Good one. I don't know how I missed this either! Thanks! gerald_clark [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] What about select distinct a.region, a.city from mytable a , mytable b where a.region=b.region and a.city b.city Jay Blanchard wrote: [snip] Anybody? I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles There is no good way to get this in a single query (w/o subqueries). Having applied all sorts of query mangling you would have to be able to carry forward some sort of count or variable in order to draw out the ones where the foreign key was 1. Grouping by the city does not work either as that reduces any count to a one for that record. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple SQL Question
I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles Here is what I'd like to do (but can't because the current stable build of MySQL doesn't support subqueries) SELECT MyTable.* FROM (SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2) as Duplicates, MyTable WHERE Duplicates.Region = MyTable.Region Here is what I'm actually doing: CREATE TEMPORARY TABLE Duplicates SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2; SELECT MyTable.* FROM MyTable, Duplicates WHERE MyTable.Region = Duplicates.Region; Can anybody tell me if there is a more efficient way of doing this query? Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
Sorry, I forgot to mention I am using version 4.0.20a (no subqueries supported) Jeff Burgoon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles Here is what I'd like to do (but can't because the current stable build of MySQL doesn't support subqueries) SELECT MyTable.* FROM (SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2) as Duplicates, MyTable WHERE Duplicates.Region = MyTable.Region Here is what I'm actually doing: CREATE TEMPORARY TABLE Duplicates SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2; SELECT MyTable.* FROM MyTable, Duplicates WHERE MyTable.Region = Duplicates.Region; Can anybody tell me if there is a more efficient way of doing this query? Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: An SQL question about using multiple tables
I don't know the source of the INTERSECT command that keeps popping up on the list but this is a straight-forward JOIN situation if I have ever seen one. Please read for more details: http://dev.mysql.com/doc/mysql/en/JOIN.html SELECT A.*, E.* FROM A INNER JOIN B ON A.ID = B.parentid INNER JOIN C ON A.ID = C.parentid INNER JOIN D ON A.ID = D.parentid LEFT JOIN E ON A.ID = E.parentid WHERE B.name = 'xxx' AND C.name = 'YYY' AND D.name = 'ZZZ'; Since E has optional information, it's LEFT JOINed to the group. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Sandip Bhattacharya [EMAIL PROTECTED] wrote on 09/09/2004 12:11:22 AM: Background: I have one master table A, and other supplementary tables B,C and D such that for every row of A there can be one or more corresponding rows in B,C,D. There is another supplementary table E with which A has a one-to-one relationship. Problem: Given three search criteria resulting in AB, AC, and AD respectively, I need to display results so that I get ( AB intersection AC intersection AD) and I need to display unique rows of A on teh screen joined with corresponding row of E. A typical multiple parameter search operation in any database with normalized tables. Constraints: Am using (sigh) mysql 3.23. No subqueries, no INTERSECT. What I have tried till now: Creating three temporary tables for AB, AC and AD respectively. Now how do I find out the intersection of these? Stuck there. The SQL with subqueries will probably be something like: select A.*, E.* from A inner join E on A.id=E.parentid where A.id in (select distinct A.id from A inner join B on A.id=B.parentid where B.name='XXX') and A.id in (select distinct A.id from A inner join C on A.id=C.parentid where C.name='YYY') and A.id in (select distinct A.id from A inner join D on A.id=D.parentid where D.name='ZZZ'); === This is most probably impossible to do in one statement in mysql. But how do I do it at all? Any pointers willl be nice. Excuse me if I am doing something terribly wrong. This is the first time I am getting my hands really dirty with SQL. - Sandip -- Sandip Bhattacharya*Puroga Technologies * [EMAIL PROTECTED] Work: http://www.puroga.com* Home: http://www.sandipb.net PGP/GPG Signature: 51A4 6C57 4BC6 8C82 6A65 AE78 B1A1 2280 A129 0FF3 Woolsey-Swanson Rule: People would rather live with a problem they cannot solve rather than accept a solution they cannot understand. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
An SQL question about using multiple tables
Background: I have one master table A, and other supplementary tables B,C and D such that for every row of A there can be one or more corresponding rows in B,C,D. There is another supplementary table E with which A has a one-to-one relationship. Problem: Given three search criteria resulting in AB, AC, and AD respectively, I need to display results so that I get ( AB intersection AC intersection AD) and I need to display unique rows of A on teh screen joined with corresponding row of E. A typical multiple parameter search operation in any database with normalized tables. Constraints: Am using (sigh) mysql 3.23. No subqueries, no INTERSECT. What I have tried till now: Creating three temporary tables for AB, AC and AD respectively. Now how do I find out the intersection of these? Stuck there. The SQL with subqueries will probably be something like: select A.*, E.* from A inner join E on A.id=E.parentid where A.id in (select distinct A.id from A inner join B on A.id=B.parentid where B.name='XXX') and A.id in (select distinct A.id from A inner join C on A.id=C.parentid where C.name='YYY') and A.id in (select distinct A.id from A inner join D on A.id=D.parentid where D.name='ZZZ'); === This is most probably impossible to do in one statement in mysql. But how do I do it at all? Any pointers willl be nice. Excuse me if I am doing something terribly wrong. This is the first time I am getting my hands really dirty with SQL. - Sandip -- Sandip Bhattacharya*Puroga Technologies * [EMAIL PROTECTED] Work: http://www.puroga.com* Home: http://www.sandipb.net PGP/GPG Signature: 51A4 6C57 4BC6 8C82 6A65 AE78 B1A1 2280 A129 0FF3 Woolsey-Swanson Rule: People would rather live with a problem they cannot solve rather than accept a solution they cannot understand. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
I had a similar problem, but my criteria for selecting the value of f1 was different; it's a date field and I wanted only the rows with the most recent date value in that field, so only the latest of otherwise identical entries got inserted. I ended up doing something like this: create temporary table temp_table ( t_f1 date, t_f2 varchar(100) unique, t_f3 varchar(100) unique ); insert ignore into temp_table select f1,f2,f3 from new_table order by f1 desc; insert into original_table select * from temp_table; If there is a better way to do this, I would like to know about it. - seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
How about INSERT INTO original_table SELECT MAX(f1), f2, f3 FROM new_table GROUP BY f2, f3; Michael Stephen E. Bacher wrote: I had a similar problem, but my criteria for selecting the value of f1 was different; it's a date field and I wanted only the rows with the most recent date value in that field, so only the latest of otherwise identical entries got inserted. I ended up doing something like this: create temporary table temp_table ( t_f1 date, t_f2 varchar(100) unique, t_f3 varchar(100) unique ); insert ignore into temp_table select f1,f2,f3 from new_table order by f1 desc; insert into original_table select * from temp_table; If there is a better way to do this, I would like to know about it. - seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL question, SELECT DISTINCT
say I'm selecting distinct (non-duplicate) rows for insertion, insert into original_table select distinct * from new_table these tables have 3 fields/row. Per the above code all 3 fields are evaluated by distict * . But my question is: I want to ignore field1, therefore I only want to test if any rows have field2 *and* field3 as dups - that would be my distinct test and then do the insert based on that. Test f2 and f3, not f1. What the sql? Help is greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
It all depends on which values of f1 you want to ignore. f1 f2 f3 - - -- val1-1 val2 val3 val1-2 val2 val3 val1-3 val2 val3 Which value of f1 would you want in your new table? Which ones to ignore? Are there other columns (beyond these 3) to move as well? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 08/16/2004 11:29:33 AM: say I'm selecting distinct (non-duplicate) rows for insertion, insert into original_table select distinct * from new_table these tables have 3 fields/row. Per the above code all 3 fields are evaluated by distict * . But my question is: I want to ignore field1, therefore I only want to test if any rows have field2 *and* field3 as dups - that would be my distinct test and then do the insert based on that. Test f2 and f3, not f1. What the sql? Help is greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
On Mon, 16 Aug 2004 11:36:32 -0400, [EMAIL PROTECTED] said: It all depends on which values of f1 you want to ignore. f1 f2 f3 - - -- val1-1 val2 val3 val1-2 val2 val3 val1-3 val2 val3 Which value of f1 would you want in your new table? Which ones to ignore? I want the DISTINCT to ignore the f1 column completely. But I want all 3 cols. ie. the entire row selected though. Pretend that f1 is a unique PK it'll always be different, I want to DISTINCT to ignore it, so if 2 or mores row have dup f2 *and* f3 Distinct will let only one through. Are there other columns (beyond these 3) to move as well? Nope. Just 3 cols. Thanks Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 08/16/2004 11:29:33 AM: say I'm selecting distinct (non-duplicate) rows for insertion, insert into original_table select distinct * from new_table these tables have 3 fields/row. Per the above code all 3 fields are evaluated by distict * . But my question is: I want to ignore field1, therefore I only want to test if any rows have field2 *and* field3 as dups - that would be my distinct test and then do the insert based on that. Test f2 and f3, not f1. What the sql? Help is greatly appreciated. -- 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: SQL question, SELECT DISTINCT
Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Or, is there some other criteria you need to consider in order to populate the f1 column of the new table with a value from your old table? Or, will the new table provide its own value for the f1 column? If I look at the sample data I set up, I see 3 rows with a unique f2/f3 combination but you only want to move 1 of them to a new table... Which value from f1 do you want to keep and which 2 do you want to throw away during the move? You are eliminating f1 values by reducing how many times the f2/f3 combination appears in the new table. All I need from you is a method to decide which f1 to keep and the SQL writes itself (almost) ;-D Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 08/16/2004 12:13:06 PM: On Mon, 16 Aug 2004 11:36:32 -0400, [EMAIL PROTECTED] said: It all depends on which values of f1 you want to ignore. f1 f2 f3 - - -- val1-1 val2 val3 val1-2 val2 val3 val1-3 val2 val3 Which value of f1 would you want in your new table? Which ones to ignore? I want the DISTINCT to ignore the f1 column completely. But I want all 3 cols. ie. the entire row selected though. Pretend that f1 is a unique PK it'll always be different, I want to DISTINCT to ignore it, so if 2 or mores row have dup f2 *and* f3 Distinct will let only one through. Are there other columns (beyond these 3) to move as well? Nope. Just 3 cols. Thanks Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 08/16/2004 11:29:33 AM: say I'm selecting distinct (non-duplicate) rows for insertion, insert into original_table select distinct * from new_table these tables have 3 fields/row. Per the above code all 3 fields are evaluated by distict * . But my question is: I want to ignore field1, therefore I only want to test if any rows have field2 *and* field3 as dups - that would be my distinct test and then do the insert based on that. Test f2 and f3, not f1. What the sql? Help is greatly appreciated. -- 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: SQL question, SELECT DISTINCT
On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to text only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen [EMAIL PROTECTED] said: You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? Oh, I understand now, sorry. If I said it makes no difference then you'd ask what the heck I have f1 for in the first place...It actually doesn't make a difference. Maybe I should drop f1. f1 is an auto-increment int. so I imagine I'd want f1 re-incremented in numerical order to take the gaps out. Not exactly normalized (or normal:^), thanks. That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target table, leave it out of the SELECT, and let it auto-generate IDs. Something like this: INSERT INTO original_table (f2, f3) SELECT DISTINCT f2, f3 FROM new_table; I did that in the same order as your original message, though I'd have expected original_table and new_table to be swapped, based on their names. See the manual http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html for the details on INSERT...SELECT. Michael leegold wrote: On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen [EMAIL PROTECTED] said: You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? Oh, I understand now, sorry. If I said it makes no difference then you'd ask what the heck I have f1 for in the first place...It actually doesn't make a difference. Maybe I should drop f1. f1 is an auto-increment int. so I imagine I'd want f1 re-incremented in numerical order to take the gaps out. Not exactly normalized (or normal:^), thanks. That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen [EMAIL PROTECTED] said: You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? Oh, I understand now, sorry. If I said it makes no difference then you'd ask what the heck I have f1 for in the first place...It actually doesn't make a difference. Maybe I should drop f1. f1 is an auto-increment int. so I imagine I'd want f1 re-incremented in numerical order to take the gaps out. Not exactly normalized (or normal:^), thanks. That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
Disregard by last message it's a repeat. THANKS for the help! On Mon, 16 Aug 2004 14:32:27 -0400, Michael Stassen [EMAIL PROTECTED] said: Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target table, leave it out of the SELECT, and let it auto-generate IDs. Something like this: INSERT INTO original_table (f2, f3) SELECT DISTINCT f2, f3 FROM new_table; I did that in the same order as your original message, though I'd have expected original_table and new_table to be swapped, based on their names. See the manual http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html for the details on INSERT...SELECT. Michael leegold wrote: On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen [EMAIL PROTECTED] said: You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? Oh, I understand now, sorry. If I said it makes no difference then you'd ask what the heck I have f1 for in the first place...It actually doesn't make a difference. Maybe I should drop f1. f1 is an auto-increment int. so I imagine I'd want f1 re-incremented in numerical order to take the gaps out. Not exactly normalized (or normal:^), thanks. That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql sql question
Bruce, It also depends on where the data is coming FROM as to what your options are. Is the source data coming from another table (like, maybe from a bulk import?) or from some kind of user input? You CAN write it in one statement but I need to know your data source. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+-- | | Peter Brawley| | | [EMAIL PROTECTED]| | | ftware.com| | | | | | 07/04/2004 06:03 PM| | | | |-+-- | | | | To: [EMAIL PROTECTED], [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: Re: mysql sql question | | Bruce, i have two hypothetical tables create table owner ( - name char(20) , - ownerid int(10) auto_increment primary key); create table dog ( - name char(20) , - ownerid int(10), - dogid int(10) auto_increment primary key); i'm curious as to how i'd go about inserting a name and the id of the owner, in table dog, in a single sql statement. something like this psuedo sql.. insert table (name, ownerid) values ($name, $ownerid) where owner.owner = owner INSERT INTO takes a single table arg, so to insert int o2 tables,you need editable Views (not yet in MySQL) or at least Stored Procedures (MySQL version 5) on the server side, or you can, as you suggest, do it in the app. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql sql question
Bruce, i have two hypothetical tables create table owner ( - name char(20) , - ownerid int(10) auto_increment primary key); create table dog ( - name char(20) , - ownerid int(10), - dogid int(10) auto_increment primary key); i'm curious as to how i'd go about inserting a name and the id of the owner, in table dog, in a single sql statement. something like this psuedo sql.. insert table (name, ownerid) values ($name, $ownerid) where owner.owner = owner INSERT INTO takes a single table arg, so to insert int o2 tables,you need editable Views (not yet in MySQL) or at least Stored Procedures (MySQL version 5) on the server side, or you can, as you suggest, do it in the app. PB
mysql sql question
hi... i have two hypothetical tables create table owner ( - name char(20) , - ownerid int(10) auto_increment primary key); create table dog ( - name char(20) , - ownerid int(10), - dogid int(10) auto_increment primary key); i'm curious as to how i'd go about inserting a name and the id of the owner, in table dog, in a single sql statement. something like this psuedo sql.. insert table (name, ownerid) values ($name, $ownerid) where owner.owner = owner in other words, an app would supply the values for the dog name, and the owner. the sql would be able to derive the ownerid for the owner from the owner table, and then be able to insert the ownerid, and dogname into the dog table... searching through google gets me to being able to write a php/perl script where i can do this using multiple sql statements.. but i'm trying to see how to do it in a single statement... any comments/criticisms would be helpful... thanks... -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql sql question
At 12:54 -0700 7/4/04, bruce wrote: hi... i have two hypothetical tables create table owner ( - name char(20) , - ownerid int(10) auto_increment primary key); create table dog ( - name char(20) , - ownerid int(10), - dogid int(10) auto_increment primary key); i'm curious as to how i'd go about inserting a name and the id of the owner, in table dog, in a single sql statement. something like this psuedo sql.. insert table (name, ownerid) values ($name, $ownerid) where owner.owner = owner This won't work, because there is no guarantee that the name of your owner is unique. Some techniques that might be useful in this situation can be found here: http://www.onlamp.com/pub/a/onlamp/excerpt/mysqlckbk/index2.html in other words, an app would supply the values for the dog name, and the owner. the sql would be able to derive the ownerid for the owner from the owner table, and then be able to insert the ownerid, and dogname into the dog table... searching through google gets me to being able to write a php/perl script where i can do this using multiple sql statements.. but i'm trying to see how to do it in a single statement... You can't, for the reason noted above. If you happen to have a unique index on the owner.name column, then what you might want to try is the INSERT INTO ... SELECT FROM form of INSERT. http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html any comments/criticisms would be helpful... thanks... -bruce -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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: mysql sql question
my bad... should have mentioned that both the owner and dog name are unique, but one owner can have multiple dogs... so just how does the insert ... select work.. it looks like it could actually do what i need, but i can't seem to get it working correctly.. -bruce -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Sunday, July 04, 2004 12:59 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: mysql sql question At 12:54 -0700 7/4/04, bruce wrote: hi... i have two hypothetical tables create table owner ( - name char(20) , - ownerid int(10) auto_increment primary key); create table dog ( - name char(20) , - ownerid int(10), - dogid int(10) auto_increment primary key); i'm curious as to how i'd go about inserting a name and the id of the owner, in table dog, in a single sql statement. something like this psuedo sql.. insert table (name, ownerid) values ($name, $ownerid) where owner.owner = owner This won't work, because there is no guarantee that the name of your owner is unique. Some techniques that might be useful in this situation can be found here: http://www.onlamp.com/pub/a/onlamp/excerpt/mysqlckbk/index2.html in other words, an app would supply the values for the dog name, and the owner. the sql would be able to derive the ownerid for the owner from the owner table, and then be able to insert the ownerid, and dogname into the dog table... searching through google gets me to being able to write a php/perl script where i can do this using multiple sql statements.. but i'm trying to see how to do it in a single statement... You can't, for the reason noted above. If you happen to have a unique index on the owner.name column, then what you might want to try is the INSERT INTO ... SELECT FROM form of INSERT. http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html any comments/criticisms would be helpful... thanks... -bruce -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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: mysql sql question
what you provided would almost do it... but i want to insert into the dog table the name that i submit... i'd like to do something like if i submit dogname, ownername insert into dog (name, ownerid) values ($dogname, owner.id) select ownerid from owner where owner.name = $ownername; -bruce -Original Message- From: Emmett Bishop [mailto:[EMAIL PROTECTED] Sent: Sunday, July 04, 2004 12:59 PM To: [EMAIL PROTECTED] Subject: Re: mysql sql question Bruce, what you want it the insert into ... select statement. It's like this: insert into dog (name, ownerid) select name, ownerid from owner where ownerid = 8; Does that sound like what you need? -- Tripp --- bruce [EMAIL PROTECTED] wrote: hi... i have two hypothetical tables create table owner ( - name char(20) , - ownerid int(10) auto_increment primary key); create table dog ( - name char(20) , - ownerid int(10), - dogid int(10) auto_increment primary key); i'm curious as to how i'd go about inserting a name and the id of the owner, in table dog, in a single sql statement. something like this psuedo sql.. insert table (name, ownerid) values ($name, $ownerid) where owner.owner = owner in other words, an app would supply the values for the dog name, and the owner. the sql would be able to derive the ownerid for the owner from the owner table, and then be able to insert the ownerid, and dogname into the dog table... searching through google gets me to being able to write a php/perl script where i can do this using multiple sql statements.. but i'm trying to see how to do it in a single statement... any comments/criticisms would be helpful... thanks... -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql sql question
i created the following as a simple test...
mysql describe test;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| name | char(20) | YES | MUL | NULL||
| id| int(10) | | PRI | NULL| auto_increment |
+---+--+--+-+-++
2 rows in set (0.00 sec)
mysql describe dept;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| name | char(20) | YES | MUL | NULL||
| collegeid | int(10) | YES | | NULL||
| nameid| int(10) | | PRI | NULL| auto_increment |
+---+--+--+-+-++
3 rows in set (0.00 sec)
mysql select * from test;
+++
| name | id |
+++
| sa | 1 |
| be | 2 |
| sam1ss | 3 |
| sammy | 4 |
+++
4 rows in set (0.00 sec)
mysql select * from dept;
Empty set (0.00 sec)
i tried...
mysql insert into dept (name,collegeid) values ('tom',test.id)
- select id from test where name=sammy;
and got the following error...
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'select id from test where name=sammy' at line 2
i've tried a number of different iterations.. any ideas as to why this
doesn't seem to work...
thanks
-bruce
ps. also, if i do/can manage to get this to work, shouldn't it be possible
to extend the basic approach to multiple tables, using FROM tbl1, tbl2,
tbl3..
-
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 3:26 PM
To: 'Emmett Bishop'; [EMAIL PROTECTED]
Subject: RE: mysql sql question
what you provided would almost do it... but i want to insert into the dog
table the name that i submit...
i'd like to do something like
if i submit dogname, ownername
insert into dog (name, ownerid) values ($dogname, owner.id)
select ownerid from owner where owner.name = $ownername;
-bruce
-Original Message-
From: Emmett Bishop [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 12:59 PM
To: [EMAIL PROTECTED]
Subject: Re: mysql sql question
Bruce,
what you want it the insert into ... select statement.
It's like this:
insert into dog (name, ownerid)
select name, ownerid from owner where ownerid = 8;
Does that sound like what you need?
-- Tripp
--- bruce [EMAIL PROTECTED] wrote:
hi...
i have two hypothetical tables
create table owner (
- name char(20) ,
- ownerid int(10) auto_increment primary key);
create table dog (
- name char(20) ,
- ownerid int(10),
- dogid int(10) auto_increment primary key);
i'm curious as to how i'd go about inserting a name
and the id of the owner,
in table dog, in a single sql statement.
something like this psuedo sql..
insert table (name, ownerid) values ($name,
$ownerid)
where owner.owner = owner
in other words, an app would supply the values for
the dog name, and the
owner. the sql would be able to derive the
ownerid for the owner from
the owner table, and then be able to insert the
ownerid, and dogname
into the dog table...
searching through google gets me to being able to
write a php/perl script
where i can do this using multiple sql statements..
but i'm trying to see
how to do it in a single statement...
any comments/criticisms would be helpful...
thanks...
-bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql sql question
Hi,
You've already specified some values, so you can't then add a 'select' clause as well.
Try
mysql insert into dept (name,collegeid)
- select 'tom', id from test where name=sammy;
HTH
Quentin
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Monday, 5 July 2004 10:41 a.m.
To: 'Emmett Bishop'; [EMAIL PROTECTED]
Subject: RE: mysql sql question
i created the following as a simple test...
mysql describe test;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| name | char(20) | YES | MUL | NULL||
| id| int(10) | | PRI | NULL| auto_increment |
+---+--+--+-+-++
2 rows in set (0.00 sec)
mysql describe dept;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| name | char(20) | YES | MUL | NULL||
| collegeid | int(10) | YES | | NULL||
| nameid| int(10) | | PRI | NULL| auto_increment |
+---+--+--+-+-++
3 rows in set (0.00 sec)
mysql select * from test;
+++
| name | id |
+++
| sa | 1 |
| be | 2 |
| sam1ss | 3 |
| sammy | 4 |
+++
4 rows in set (0.00 sec)
mysql select * from dept;
Empty set (0.00 sec)
i tried...
mysql insert into dept (name,collegeid) values ('tom',test.id)
- select id from test where name=sammy;
and got the following error...
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'select id from test where name=sammy' at line 2
i've tried a number of different iterations.. any ideas as to why this
doesn't seem to work...
thanks
-bruce
ps. also, if i do/can manage to get this to work, shouldn't it be possible
to extend the basic approach to multiple tables, using FROM tbl1, tbl2,
tbl3..
-
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 3:26 PM
To: 'Emmett Bishop'; [EMAIL PROTECTED]
Subject: RE: mysql sql question
what you provided would almost do it... but i want to insert into the dog
table the name that i submit...
i'd like to do something like
if i submit dogname, ownername
insert into dog (name, ownerid) values ($dogname, owner.id)
select ownerid from owner where owner.name = $ownername;
-bruce
-Original Message-
From: Emmett Bishop [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 12:59 PM
To: [EMAIL PROTECTED]
Subject: Re: mysql sql question
Bruce,
what you want it the insert into ... select statement.
It's like this:
insert into dog (name, ownerid)
select name, ownerid from owner where ownerid = 8;
Does that sound like what you need?
-- Tripp
--- bruce [EMAIL PROTECTED] wrote:
hi...
i have two hypothetical tables
create table owner (
- name char(20) ,
- ownerid int(10) auto_increment primary key);
create table dog (
- name char(20) ,
- ownerid int(10),
- dogid int(10) auto_increment primary key);
i'm curious as to how i'd go about inserting a name
and the id of the owner,
in table dog, in a single sql statement.
something like this psuedo sql..
insert table (name, ownerid) values ($name,
$ownerid)
where owner.owner = owner
in other words, an app would supply the values for
the dog name, and the
owner. the sql would be able to derive the
ownerid for the owner from
the owner table, and then be able to insert the
ownerid, and dogname
into the dog table...
searching through google gets me to being able to
write a php/perl script
where i can do this using multiple sql statements..
but i'm trying to see
how to do it in a single statement...
any comments/criticisms would be helpful...
thanks...
-bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail
--
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]
The information contained in this email is privileged and confidential and
intended for the addressee only. If you are not the intended recipient, you
are asked to respect that confidentiality and not disclose, copy or make use
of its contents. If received in error you are asked to destroy this email
and contact
RE: mysql sql question
Hi,
The insert says 'insert data in to two columns, name and collegeid'.
The select says get two columns, 'tom' and id - 'tom' is a fixed value, the same for
each row, and id is taken from the test table.
If you want the name from test, then use
mysql insert into dept (name,collegeid)
- select name, id from test where name=sammy;
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Monday, 5 July 2004 10:50 a.m.
To: Quentin Bennett
Subject: RE: mysql sql question
i can't see how this would work at all...
unless you're saying the select will return tom and stuff that into the dept table
as the name value.
and where/how would the collegeid of the insert be derived from..
-bruce
-Original Message-
From: Quentin Bennett [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 3:41 PM
To: [EMAIL PROTECTED]; Emmett Bishop; [EMAIL PROTECTED]
Subject: RE: mysql sql question
Hi,
You've already specified some values, so you can't then add a 'select' clause as well.
Try
mysql insert into dept (name,collegeid)
- select 'tom', id from test where name=sammy;
HTH
Quentin
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Monday, 5 July 2004 10:41 a.m.
To: 'Emmett Bishop'; [EMAIL PROTECTED]
Subject: RE: mysql sql question
i created the following as a simple test...
mysql describe test;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| name | char(20) | YES | MUL | NULL||
| id| int(10) | | PRI | NULL| auto_increment |
+---+--+--+-+-++
2 rows in set (0.00 sec)
mysql describe dept;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| name | char(20) | YES | MUL | NULL||
| collegeid | int(10) | YES | | NULL||
| nameid| int(10) | | PRI | NULL| auto_increment |
+---+--+--+-+-++
3 rows in set (0.00 sec)
mysql select * from test;
+++
| name | id |
+++
| sa | 1 |
| be | 2 |
| sam1ss | 3 |
| sammy | 4 |
+++
4 rows in set (0.00 sec)
mysql select * from dept;
Empty set (0.00 sec)
i tried...
mysql insert into dept (name,collegeid) values ('tom',test.id)
- select id from test where name=sammy;
and got the following error...
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'select id from test where name=sammy' at line 2
i've tried a number of different iterations.. any ideas as to why this
doesn't seem to work...
thanks
-bruce
ps. also, if i do/can manage to get this to work, shouldn't it be possible
to extend the basic approach to multiple tables, using FROM tbl1, tbl2,
tbl3..
-
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 3:26 PM
To: 'Emmett Bishop'; [EMAIL PROTECTED]
Subject: RE: mysql sql question
what you provided would almost do it... but i want to insert into the dog
table the name that i submit...
i'd like to do something like
if i submit dogname, ownername
insert into dog (name, ownerid) values ($dogname, owner.id)
select ownerid from owner where owner.name = $ownername;
-bruce
-Original Message-
From: Emmett Bishop [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 12:59 PM
To: [EMAIL PROTECTED]
Subject: Re: mysql sql question
Bruce,
what you want it the insert into ... select statement.
It's like this:
insert into dog (name, ownerid)
select name, ownerid from owner where ownerid = 8;
Does that sound like what you need?
-- Tripp
--- bruce [EMAIL PROTECTED] wrote:
hi...
i have two hypothetical tables
create table owner (
- name char(20) ,
- ownerid int(10) auto_increment primary key);
create table dog (
- name char(20) ,
- ownerid int(10),
- dogid int(10) auto_increment primary key);
i'm curious as to how i'd go about inserting a name
and the id of the owner,
in table dog, in a single sql statement.
something like this psuedo sql..
insert table (name, ownerid) values ($name,
$ownerid)
where owner.owner = owner
in other words, an app would supply the values for
the dog name, and the
owner. the sql would be able to derive the
ownerid for the owner from
the owner table, and then be able to insert the
ownerid, and dogname
into the dog table...
searching through google gets me to being able to
write a php/perl script
where i can do this using multiple sql statements..
but i'm trying to see
how to do it in a single statement...
any comments
RE: mysql sql question
hey
i tried your suggestion.. and it worked... could you walk me through why it
worked...
also, could i do the same basic thing if i wanted to get values from
different levels of parent tables
ie, i want to insert a name/id in table1, however, i need to get the id from
a parent tbl, which in turn is based on a parent tbl...
thanks...
-bruce
i can't see how this would work at all...
unless you're saying the select will return tom and stuff that into the
dept table as the name value.
and where/how would the collegeid of the insert be derived from..
-bruce
-Original Message-
From: Quentin Bennett [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 3:41 PM
To: [EMAIL PROTECTED]; Emmett Bishop; [EMAIL PROTECTED]
Subject: RE: mysql sql question
Hi,
You've already specified some values, so you can't then add a 'select'
clause as well.
Try
mysql insert into dept (name,collegeid)
- select 'tom', id from test where name=sammy;
HTH
Quentin
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Monday, 5 July 2004 10:41 a.m.
To: 'Emmett Bishop'; [EMAIL PROTECTED]
Subject: RE: mysql sql question
i created the following as a simple test...
mysql describe test;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| name | char(20) | YES | MUL | NULL||
| id| int(10) | | PRI | NULL| auto_increment |
+---+--+--+-+-++
2 rows in set (0.00 sec)
mysql describe dept;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| name | char(20) | YES | MUL | NULL||
| collegeid | int(10) | YES | | NULL||
| nameid| int(10) | | PRI | NULL| auto_increment |
+---+--+--+-+-++
3 rows in set (0.00 sec)
mysql select * from test;
+++
| name | id |
+++
| sa | 1 |
| be | 2 |
| sam1ss | 3 |
| sammy | 4 |
+++
4 rows in set (0.00 sec)
mysql select * from dept;
Empty set (0.00 sec)
i tried...
mysql insert into dept (name,collegeid) values ('tom',test.id)
- select id from test where name=sammy;
and got the following error...
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'select id from test where name=sammy' at line 2
i've tried a number of different iterations.. any ideas as to why this
doesn't seem to work...
thanks
-bruce
ps. also, if i do/can manage to get this to work, shouldn't it be possible
to extend the basic approach to multiple tables, using FROM tbl1, tbl2,
tbl3..
-
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 3:26 PM
To: 'Emmett Bishop'; [EMAIL PROTECTED]
Subject: RE: mysql sql question
what you provided would almost do it... but i want to insert into the dog
table the name that i submit...
i'd like to do something like
if i submit dogname, ownername
insert into dog (name, ownerid) values ($dogname, owner.id)
select ownerid from owner where owner.name = $ownername;
-bruce
-Original Message-
From: Emmett Bishop [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 12:59 PM
To: [EMAIL PROTECTED]
Subject: Re: mysql sql question
Bruce,
what you want it the insert into ... select statement.
It's like this:
insert into dog (name, ownerid)
select name, ownerid from owner where ownerid = 8;
Does that sound like what you need?
-- Tripp
--- bruce [EMAIL PROTECTED] wrote:
hi...
i have two hypothetical tables
create table owner (
- name char(20) ,
- ownerid int(10) auto_increment primary key);
create table dog (
- name char(20) ,
- ownerid int(10),
- dogid int(10) auto_increment primary key);
i'm curious as to how i'd go about inserting a name
and the id of the owner,
in table dog, in a single sql statement.
something like this psuedo sql..
insert table (name, ownerid) values ($name,
$ownerid)
where owner.owner = owner
in other words, an app would supply the values for
the dog name, and the
owner. the sql would be able to derive the
ownerid for the owner from
the owner table, and then be able to insert the
ownerid, and dogname
into the dog table...
searching through google gets me to being able to
write a php/perl script
where i can do this using multiple sql statements..
but i'm trying to see
how to do it in a single statement...
any comments/criticisms would be helpful...
thanks...
-bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
RE: mysql sql question
quentin/emmett...
my question
it appears that mysql essentially take the two values returned from the
select, and inserts them into the (name,collegeid) that i specified...
if this is the case, then i'm cool
mind if i ask you antoher question from a design perspective...
i'm creating a system with college class schedule information..
it needs to present the user with the following information:
state
university name
school name (school of medicine/engineering/accounting/etc..)
dept name
course/class name
class section
class day/time
instructor
rather than put all this into one large table..
i'm considering having separate tbls for each item. each tabole would be
connected via the id of the parent.
tables...
stateTbl
stateName - unique
stateID
universityTbl
universityName - unique
universityID
stateID
schoolTbl
schoolName - (unique for schoolName and universityID)
schoolID
universityID
deptTbl
deptName - (unique for deptName and universityID)
deptID
universityID
courseTbl
courseName - (unique for courseName, classSection and deptID)
courseID
deptID
classSection - char
classday
classtime
instructorID
instructorTbl
name
dept
phone
email
universityID
instructorID
does this make sense... any thoughts/comments/critcisms
i'm not a db guy by any stretch.. but this should scale to handle 100s of
universities with no prob...
thanks
-bruce
-Original Message-
From: Quentin Bennett [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 3:49 PM
To: [EMAIL PROTECTED]
Cc: MySQL (E-mail)
Subject: RE: mysql sql question
Hi,
The insert says 'insert data in to two columns, name and collegeid'.
The select says get two columns, 'tom' and id - 'tom' is a fixed value,
the same for each row, and id is taken from the test table.
If you want the name from test, then use
mysql insert into dept (name,collegeid)
- select name, id from test where name=sammy;
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Monday, 5 July 2004 10:50 a.m.
To: Quentin Bennett
Subject: RE: mysql sql question
i can't see how this would work at all...
unless you're saying the select will return tom and stuff that into the
dept table as the name value.
and where/how would the collegeid of the insert be derived from..
-bruce
-Original Message-
From: Quentin Bennett [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 3:41 PM
To: [EMAIL PROTECTED]; Emmett Bishop; [EMAIL PROTECTED]
Subject: RE: mysql sql question
Hi,
You've already specified some values, so you can't then add a 'select'
clause as well.
Try
mysql insert into dept (name,collegeid)
- select 'tom', id from test where name=sammy;
HTH
Quentin
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Monday, 5 July 2004 10:41 a.m.
To: 'Emmett Bishop'; [EMAIL PROTECTED]
Subject: RE: mysql sql question
i created the following as a simple test...
mysql describe test;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| name | char(20) | YES | MUL | NULL||
| id| int(10) | | PRI | NULL| auto_increment |
+---+--+--+-+-++
2 rows in set (0.00 sec)
mysql describe dept;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| name | char(20) | YES | MUL | NULL||
| collegeid | int(10) | YES | | NULL||
| nameid| int(10) | | PRI | NULL| auto_increment |
+---+--+--+-+-++
3 rows in set (0.00 sec)
mysql select * from test;
+++
| name | id |
+++
| sa | 1 |
| be | 2 |
| sam1ss | 3 |
| sammy | 4 |
+++
4 rows in set (0.00 sec)
mysql select * from dept;
Empty set (0.00 sec)
i tried...
mysql insert into dept (name,collegeid) values ('tom',test.id)
- select id from test where name=sammy;
and got the following error...
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'select id from test where name=sammy' at line 2
i've tried a number of different iterations.. any ideas as to why this
doesn't seem to work...
thanks
-bruce
ps. also, if i do/can manage to get this to work, shouldn't it be possible
to extend the basic approach to multiple tables, using FROM tbl1, tbl2,
tbl3..
-
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 3:26 PM
To: 'Emmett Bishop'; [EMAIL PROTECTED]
Subject: RE: mysql sql question
what you provided would almost
Re: mysql sql question
On Sunday 04 July 2004 07:16 pm, bruce wrote:
quentin/emmett...
my question
it appears that mysql essentially take the two
values returned from the select, and inserts them
into the (name,collegeid) that i specified...
if this is the case, then i'm cool
Yes, this is the case.
This is all explained in the FM at:
http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html
mind if i ask you antoher question from a design
perspective...
i'm creating a system with college class schedule
information.. it needs to present the user with the
following information:
state
university name
school name (school of
medicine/engineering/accounting/etc..) dept name
course/class name
class section
class day/time
instructor
rather than put all this into one large table..
i'm considering having separate tbls for each item.
each tabole would be connected via the id of the
parent.
tables...
stateTbl
stateName - unique
stateID
universityTbl
universityName - unique
universityID
stateID
schoolTbl
schoolName - (unique for schoolName and
universityID) schoolID
universityID
deptTbl
deptName - (unique for deptName and universityID)
deptID
universityID
courseTbl
courseName - (unique for courseName, classSection
and deptID) courseID
deptID
classSection - char
classday
classtime
instructorID
instructorTbl
name
dept
phone
email
universityID
instructorID
does this make sense... any
thoughts/comments/critcisms
Almost.
You're on the verge of discovering (or reinventing)
normal forms.
But it looks like you have combined course, class, and
meeting info into a single table.
These should be in three different tables for it to be
properly normalized.
The general rule is each piece of information should be
stored only once in the database. Your plan stores
course name multiple times (sections * meeting times).
This is not normal :(
i'm not a db guy by any stretch.. but this should
scale to handle 100s of universities with no prob...
You are correct.
Good luck,
--John
thanks
-bruce
-Original Message-
From: Quentin Bennett
[mailto:[EMAIL PROTECTED] Sent:
Sunday, July 04, 2004 3:49 PM
To: [EMAIL PROTECTED]
Cc: MySQL (E-mail)
Subject: RE: mysql sql question
Hi,
The insert says 'insert data in to two columns, name
and collegeid'.
The select says get two columns, 'tom' and id -
'tom' is a fixed value, the same for each row, and
id is taken from the test table.
If you want the name from test, then use
mysql insert into dept (name,collegeid)
- select name, id from test where name=sammy;
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Monday, 5 July 2004 10:50 a.m.
To: Quentin Bennett
Subject: RE: mysql sql question
i can't see how this would work at all...
unless you're saying the select will return tom
and stuff that into the dept table as the name
value.
and where/how would the collegeid of the insert be
derived from..
-bruce
-Original Message-
From: Quentin Bennett
[mailto:[EMAIL PROTECTED] Sent:
Sunday, July 04, 2004 3:41 PM
To: [EMAIL PROTECTED]; Emmett Bishop;
[EMAIL PROTECTED] Subject: RE: mysql sql
question
Hi,
You've already specified some values, so you can't
then add a 'select' clause as well.
Try
mysql insert into dept (name,collegeid)
- select 'tom', id from test where
name=sammy;
HTH
Quentin
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Monday, 5 July 2004 10:41 a.m.
To: 'Emmett Bishop'; [EMAIL PROTECTED]
Subject: RE: mysql sql question
i created the following as a simple test...
mysql describe test;
+---+--+--+-+-+-
---+
| Field | Type | Null | Key | Default | Extra
| |
+---+--+--+-+-+-
---+
| name | char(20) | YES | MUL | NULL|
| | id| int(10) | | PRI | NULL|
| auto_increment |
+---+--+--+-+-+-
---+ 2 rows in set (0.00 sec)
mysql describe dept;
+---+--+--+-+-+-
---+
| Field | Type | Null | Key | Default |
| Extra |
+---+--+--+-+-+-
---+
| name | char(20) | YES | MUL | NULL|
| | collegeid | int(10) | YES | |
| NULL|| nameid| int(10) |
| | PRI | NULL| auto_increment |
+---+--+--+-+-+-
---+ 3 rows in set (0.00 sec)
mysql select * from test;
+++
| name | id |
+++
| sa | 1 |
| be | 2 |
| sam1ss | 3 |
| sammy | 4 |
+++
4 rows in set (0.00 sec)
mysql select * from dept;
Empty set (0.00 sec)
i tried...
mysql insert into dept (name,collegeid) values
('tom
sql question
Hello all, I've a table like this : site_1 pkg_name_1 version site_1 pkg_name_2 version site_1 pkg_name_3 version ... site_1 pkg_name_n version site_2 pkg_name_1 version site_2 pkg_name_2 version ... site_2 pkg_name_n version ... site_n pkg_name_1 version ... site_n pkg_name_n version And i would like to make a sql statement to obtain a result like this ; |pkg| site_1 | site_2 | site_3 ... site_n |-- |pkg_name_1 | version | version | version ... version |pkg_name_2 | version | version | version ... version |pkg_name_3 | version | version | version ... version |... |pkg_name_n | version | version | version ... version I really don't know how to write this sql request, even if there is one way to do it... I still hope someone will have an idea. Thank's for you help -- Vincent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql question
Maybe something like: Select LIKE pkg_name%, LIKE site_%, version from table group by LIKE pkg_name% , LIKE site_%; but I'm still a beginner. Respectfully, Ligaya Turmelle [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hello all, I've a table like this : site_1 pkg_name_1 version site_1 pkg_name_2 version site_1 pkg_name_3 version ... site_1 pkg_name_n version site_2 pkg_name_1 version site_2 pkg_name_2 version ... site_2 pkg_name_n version ... site_n pkg_name_1 version ... site_n pkg_name_n version And i would like to make a sql statement to obtain a result like this ; |pkg| site_1 | site_2 | site_3 ... site_n |-- |pkg_name_1 | version | version | version ... version |pkg_name_2 | version | version | version ... version |pkg_name_3 | version | version | version ... version |... |pkg_name_n | version | version | version ... version I really don't know how to write this sql request, even if there is one way to do it... I still hope someone will have an idea. Thank's for you help -- Vincent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL question: Finding duplicates
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Disclaimer: I'm not an SQL expert so please don't laugh. ;-) I'm trying to generate a way to find all rows from a table where the data in column 'foo' is a duplicate of the data in another row. I.E.: row | foo 1 | a 2 | c 3 | b 4 | c 5 | a 6 | d the statement would return me rows 1, 2, 4, and 5. I tried this: SELECT t1.* FROM mytable AS t1, mytable AS t2 WHERE t1.foo = t2.foo t1.rownum != t2.rownum ; It works on my test table with 10 records, but takes a really long time on my table with a large number (13000) of records. The numerical column is the primary key, and the other column is indexed. Is there a way to speed this up, or do it more efficiently? Thanks, Simon -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQFAH4LxyEdVKI+MVc8RAiYJAJsFdfGHbkk7RRRIXm8V+fIN28OB2QCfWbbE bWgRa2AkYlDB0mbQP3GHIMc= =AWOE -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL question: Finding duplicates
row | foo 1 | a 2 | c 3 | b 4 | c 5 | a 6 | d the statement would return me rows 1, 2, 4, and 5. CREATE TEMPORARY TABLE temptable SELECT * FROM test GROUP BY foo HAVING COUNT( * ) 1 ORDER BY foo ASC ; SELECT * FROM test, temptable WHERE test.foo = temptable.foo ORDER BY test.row ASC; -- Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql question
Hi Using mysql 3.23.54 I'm trying to join three tables. categories, topics, posts. I just want the categories to print out, with the number of topics in each category, and the number of posts in each topic. ex output: Category | Topics | Posts Cat One | 3 |15 I have this: SELECT forum_categories.id AS `id` , forum_categories.name AS `name` , forum_categories.createdby AS `createdby` , forum_categories.order AS `order` ,DATE_FORMAT( forum_categories.created, %m/%d/%y %l:%i %p ) AS `created` , COUNT( forum_topics.id ) AS `topics`, SUM(forum_posts.id) AS `posts` FROM forum_categories LEFT JOIN forum_topics ON forum_categories.id = forum_topics.category LEFT JOIN forum_posts ON forum_topics.id = forum_posts.topic WHERE forum_categories.domain = 01 GROUP BY forum_categories.id ORDER BY forum_categories.order ASC Pulling from: 2 Categories. There are 3 topics under Category 1, and there are two Posts for Topic 1, 1 for Topic 2, and 0 for Topic 3. 1 topic under Category 2, and one Post under that Topic. Gives me: ++-+---+---+---+ +---+ | id | name| createdby| order | created | topics| posts | ++-+---+---+---+ +---+ | 29 | Test Category One | Chris Edwards | 0 | 11/06/03 11:00 | 4 | 6| | 31 | Test Category Three | Chris Edwards | 1 | 11/06/03 11:00 | 1 | 0| ++-+---+---+---+ +---+ My problem is getting the grouping to work or something. The query ends up multiplying the topics and posts to create the post count. I cannot figure out how to prevent this. I need the posts to say 3 and 0, not 6 and 0. I hope this is enough information. Thanks. -- Chris Edwards Web Application Developer Outer Banks Internet, Inc. 252-441-6698 [EMAIL PROTECTED] http://www.OuterBanksInternet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sql question
Mysql 3.. I can't figure this one out I need to move data from one mysql table to another The hurdle for me is adding additional column values. Here is what I have. insert into mytable (column1, column 2, column3) (Select thiscolumn From anotherTable), '1', now(); It's the 1 and the now() I can't insert. Anyone have an idea how to do this? --- Keith Schuster Schuster Company LLC ph:704-799-2438 fx:704-799-0779 iChat/AIM:FSHSales WWW.FlagShipHosting.com WWW.Schusterandcompany.com WWW.Vsheet.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sql question
Keith Schuster [EMAIL PROTECTED] wrote: Mysql 3.. I can't figure this one out I need to move data from one mysql table to another The hurdle for me is adding additional column values. Here is what I have. insert into mytable (column1, column 2, column3) (Select thiscolumn From anotherTable), '1', now(); It's the 1 and the now() I can't insert. Anyone have an idea how to do this? INSERT INTO mytable(column1, column2, column3) SELECT thiscolumn, '1', NOW() FROM anothertable -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sql question
you want to do insert into mytable (column1, column2, column3) (select thiscolumn, '1', now() from anotherTable); Mysql 3.. I can't figure this one out I need to move data from one mysql table to another The hurdle for me is adding additional column values. Here is what I have. insert into mytable (column1, column 2, column3) (Select thiscolumn From anotherTable), '1', now(); It's the 1 and the now() I can't insert. Anyone have an idea how to do this? --- Keith Schuster Schuster Company LLC ph:704-799-2438 fx:704-799-0779 iChat/AIM:FSHSales WWW.FlagShipHosting.com WWW.Schusterandcompany.com WWW.Vsheet.net -- 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: Newbie SQL question
Keith, You're on the right track. But instead of 2 tables, pretend you have three. 1) Home Teams, 2) Schedule 3) Opponent Teams. In reality tables 1 and 3 are the same table, but you mus't get confused between using 'Teams' as Home Teams and Teams as Opponents. In the same way as you named columns using 'AS' you can 'name' tables using AS. E.G. select HomeTeams.Team_Name as 'Team Name' from Teams as HomeTeams; -- notice how you seem to have a new table called 'HomeTeams'. This naming of table allows you to use the same table 2 or more times without getting confused as to which table you are refering to. Now you can join 3 'tables' instead of only 2. select HomeTeams.Team_Name as 'Team Name',Schedules.Team_ID,Schedules.Opponent_ID, OpponentTeams.Team_Name as 'Opponents', Schedules.Game_Date from Schedules,Teams as HomeTeams, Teams as OpponentTeams where (HomeTeams.id=140 or OpponentTeams.id =140) and (HomeTeams.id=Schedules.Team_ID and OppenentTeams.id=Schedules.Opponent_ID) order by Game_Date; The 3 tables are joined in the last line. The previous line limits the results to where the home or opponents are '140'; Excuse any typos. Regards, Andy. Warren, Keith wrote: I'm coming from a Filemaker Pro background and have very little SQL experience. I'm trying to write an SQL statement to extract data from two tables. One table has the Team IDs, Team Names for all the high school football teams in the state. The other table has the schedules for all the games. The schedules table has Game_ID, Team_ID, Opponent_ID and Game_Date fields. I've got the SQL statement to return the data that I'm looking for, except, I only get team IDs. I want team names. This is the MySQL statement: mysql select Teams.Team_Name as 'Team Name',Schedules.Team_ID,Schedules.Opponent_ID,Schedules.Game_Date from Schedules,Teams where Teams.id=140 and (Teams.id=Schedules.Team_ID or Teams.id=Schedules.Opponent_ID) order by Game_Date; and here are the results. +-+-+-++ | Team Name | Team_ID | Opponent_ID | Game_Date | +-+-+-++ | Lawrence County | 140 | 88 | 2003-08-28 | | Lawrence County | 163 | 140 | 2003-09-05 | | Lawrence County | 140 | 237 | 2003-09-12 | | Lawrence County | 140 | 161 | 2003-09-19 | | Lawrence County | 263 | 140 | 2003-09-26 | | Lawrence County | 129 | 140 | 2003-10-03 | | Lawrence County | 153 | 140 | 2003-10-10 | | Lawrence County | 140 | 284 | 2003-10-17 | | Lawrence County | 323 | 140 | 2003-10-24 | | Lawrence County | 140 | 26 | 2003-10-31 | +-+-+-++ 10 rows in set (0.01 sec) This is exactly what I want, but, I want to be able to query the Teams table to give me the Team_Name for both the Team_ID (which is the Home team) and the Team_Name for the Opponent_ID (the visiting team). I'm assuming I'd have to have a nested search, but I really don't even know enough about SQL to ask an intellegent question here. Can someone point me in the right direction? Thanks, Keith Warren Systems Editor The Clarion-Ledger -- 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]
Newbie SQL question
I'm coming from a Filemaker Pro background and have very little SQL experience. I'm trying to write an SQL statement to extract data from two tables. One table has the Team IDs, Team Names for all the high school football teams in the state. The other table has the schedules for all the games. The schedules table has Game_ID, Team_ID, Opponent_ID and Game_Date fields. I've got the SQL statement to return the data that I'm looking for, except, I only get team IDs. I want team names. This is the MySQL statement: mysql select Teams.Team_Name as 'Team Name',Schedules.Team_ID,Schedules.Opponent_ID,Schedules.Game_Date from Schedules,Teams where Teams.id=140 and (Teams.id=Schedules.Team_ID or Teams.id=Schedules.Opponent_ID) order by Game_Date; and here are the results. +-+-+-++ | Team Name | Team_ID | Opponent_ID | Game_Date | +-+-+-++ | Lawrence County | 140 | 88 | 2003-08-28 | | Lawrence County | 163 | 140 | 2003-09-05 | | Lawrence County | 140 | 237 | 2003-09-12 | | Lawrence County | 140 | 161 | 2003-09-19 | | Lawrence County | 263 | 140 | 2003-09-26 | | Lawrence County | 129 | 140 | 2003-10-03 | | Lawrence County | 153 | 140 | 2003-10-10 | | Lawrence County | 140 | 284 | 2003-10-17 | | Lawrence County | 323 | 140 | 2003-10-24 | | Lawrence County | 140 | 26 | 2003-10-31 | +-+-+-++ 10 rows in set (0.01 sec) This is exactly what I want, but, I want to be able to query the Teams table to give me the Team_Name for both the Team_ID (which is the Home team) and the Team_Name for the Opponent_ID (the visiting team). I'm assuming I'd have to have a nested search, but I really don't even know enough about SQL to ask an intellegent question here. Can someone point me in the right direction? Thanks, Keith Warren Systems Editor The Clarion-Ledger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
basic SQL question
Dear all, I have the following problem, I am trying to select records from two tables. the tables are constructed like: block block_id sequence_id snp_required first_polymorphism_index last_polymorphism_index first_reference_positio last_reference_position start_pos end_pos tiled_bp polymorphism_block_map block_id polymorphism_index polymorphism_id When I try to do: mysql select first_polymorphism_index,last_polymorphism_index from block where first_polymorphism_index like 14163 OR first_polymorphism_index like 14750 OR first_polymorphism_index like 14932; +--+-+ | first_polymorphism_index | last_polymorphism_index | +--+-+ |14163 | 14235 | |14750 | 14802 | |14932 | 14980 | +--+-+ 3 rows in set (0.02 sec) everything works fine, easy ;) but if I try to link these two tables I got: +--++-+- ++-+ | first_polymorphism_index | polymorphism_index | polymorphism_id | last_polymorphism_index | polymorphism_index | polymorphism_id | +--++-+- ++-+ |14163 | 14163 | P0216196 | 14235 | 14235 | P0216288| |14750 | 14750 | P0217007 | 14235 | 14235 | P0216288| |14932 | 14932 | P0217251 | 14235 | 14235 | P0216288| +--++-+- ++-+ 3 rows in set (1 min 27.48 sec) ## # this does not return the right values on the las polymorphism index ## SELECT b1.first_polymorphism_index, p1.polymorphism_index, p1.polymorphism_id, b2.last_polymorphism_index, p2.polymorphism_index, p2.polymorphism_id FROM block as b1, polymorphism_block_map as p1, block as b2, polymorphism_block_map as p2 WHERE b1.first_polymorphism_index = p1.polymorphism_index AND b2.last_polymorphism_index = p2.polymorphism_index LIMIT 5; But the second polymorphism_index is always the same, How I can get the right one? Thanks in advance P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: basic SQL question
How I can get the right one? Depending on your goal, which was not given here. I recall in my earlier career, in the computing center there was a banner saying: I wish they could sell this computer: It never does what I want it to do, only what I tell it to do. The result you got was intrinsic to your data. I'd suggest you take a closer look at your data, do a hand calculation for each step in your where-clause. Then you'd see the reason of the returned results, and perhaps find a way to achieve what you want. Best regards, Lin -Original Message- From: Gomez Fabre, Pedro Manuel [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 5:19 AM To: [EMAIL PROTECTED] Subject: basic SQL question Dear all, I have the following problem, I am trying to select records from two tables. the tables are constructed like: block block_id sequence_id snp_required first_polymorphism_index last_polymorphism_index first_reference_positio last_reference_position start_pos end_pos tiled_bp polymorphism_block_map block_id polymorphism_index polymorphism_id When I try to do: mysql select first_polymorphism_index,last_polymorphism_index from block where first_polymorphism_index like 14163 OR first_polymorphism_index like 14750 OR first_polymorphism_index like 14932; +--+-+ | first_polymorphism_index | last_polymorphism_index | +--+-+ |14163 | 14235 | |14750 | 14802 | |14932 | 14980 | +--+-+ 3 rows in set (0.02 sec) everything works fine, easy ;) but if I try to link these two tables I got: +--++-+- ++-+ | first_polymorphism_index | polymorphism_index | polymorphism_id | last_polymorphism_index | polymorphism_index | polymorphism_id | +--++-+- ++-+ |14163 | 14163 | P0216196 | 14235 | 14235 | P0216288| |14750 | 14750 | P0217007 | 14235 | 14235 | P0216288| |14932 | 14932 | P0217251 | 14235 | 14235 | P0216288| +--++-+- ++-+ 3 rows in set (1 min 27.48 sec) ## # this does not return the right values on the las polymorphism index ## SELECT b1.first_polymorphism_index, p1.polymorphism_index, p1.polymorphism_id, b2.last_polymorphism_index, p2.polymorphism_index, p2.polymorphism_id FROM block as b1, polymorphism_block_map as p1, block as b2, polymorphism_block_map as p2 WHERE b1.first_polymorphism_index = p1.polymorphism_index AND b2.last_polymorphism_index = p2.polymorphism_index LIMIT 5; But the second polymorphism_index is always the same, How I can get the right one? Thanks in advance P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (SQL Question) WHERE NOT IN A LIST
At 12:29 -0500 7/18/03, Tom O'Neill (MySQL User) wrote:
Hi,
Is there a way I can run a query that will delete all items that are not in
a list? For example I have a bunch of records in a table and I want to
remove all of them that are not in a comma delimited list that I have
recieved from another application. I was thinking that I could create a
query that says:
WHERE id != 'item1' AND id != 'item2' AND id != 'item3'... etc but I was
wondering if there is an easier/more effecient way?
Thanks!
-Tom
... WHERE id NOT IN('item1','item2','item3',)
You cannot use NULL in the value list.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question
Jake Johnson wrote: This is one quick way to get the newest records of a group if you are grouping by the sku and stock. select stock, sku, qty from table where concat(dt_tm,stock,sku) IN ( select concat(max(dt_tm), stock, sku) from table group by stock, sku ) Another approach (also assuming a current version of MySQL which supports subselects) is: SELECT stock, sku, qty FROM table t1 WHERE dt_tim = ( SELECT max(dt_tm) FROM table t2 WHERE t1.stock = t2.stock AND t1.sku = t2.sku ) This should be more reliable than Jake's solution, which will have problems with some data values. To illustrate the possible problem, run the above query on the following data: dt_tm stocksku qty dt1 A BB 1 dt1 ABB 2 Jake's query will return a single row instead of two rows. (Sorry, Jake, I don't mean to put you on the spot!) Bruce Feist I have a table with stock-status transactions like ... 2003-06-17 06:00 stockA SKU1 QTY 98 2003-06-16 06:10 stockA SKU1 QTY 101 2003-06-15 04:59 stockA SKU1 QTY 111 - the time for updating the transaction - each specific stock - each specific SKU / partnumber - quantity in stock at time of transaction The SQL issue - are there some way in SQL I can SELECT only latest transaction for each stock/SKU no matter date of update, or do I have to read thrue all transactions and select in the program ??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question
Nice approach Bruce, but I too won't have any problems with your case because I am grouping by sku and stock in the sub-query. Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com Shop Plutoid for the best prices on Rims and Car Audio Products On Thu, 19 Jun 2003, Bruce Feist wrote: Jake Johnson wrote: This is one quick way to get the newest records of a group if you are grouping by the sku and stock. select stock, sku, qty from table where concat(dt_tm,stock,sku) IN ( select concat(max(dt_tm), stock, sku) from table group by stock, sku ) Another approach (also assuming a current version of MySQL which supports subselects) is: SELECT stock, sku, qty FROM table t1 WHERE dt_tim = ( SELECT max(dt_tm) FROM table t2 WHERE t1.stock = t2.stock AND t1.sku = t2.sku ) This should be more reliable than Jake's solution, which will have problems with some data values. To illustrate the possible problem, run the above query on the following data: dt_tm stocksku qty dt1 A BB 1 dt1 ABB 2 Jake's query will return a single row instead of two rows. (Sorry, Jake, I don't mean to put you on the spot!) Bruce Feist I have a table with stock-status transactions like ... 2003-06-17 06:00 stockA SKU1 QTY 98 2003-06-16 06:10 stockA SKU1 QTY 101 2003-06-15 04:59 stockA SKU1 QTY 111 - the time for updating the transaction - each specific stock - each specific SKU / partnumber - quantity in stock at time of transaction The SQL issue - are there some way in SQL I can SELECT only latest transaction for each stock/SKU no matter date of update, or do I have to read thrue all transactions and select in the program ??? -- 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: SQL question
Jake Johnson wrote: Nice approach Bruce, but I too won't have any problems with your case because I am grouping by sku and stock in the sub-query. You're right; you do avoid the problem with the specific sample data I gave you. Sorry about that! But, there are still potential problems because of the comparison of a single concatenated value instead of the separate SKU and STOCK values. In practice, they might not arise because of the formatting of the date/time field. Your subquery does yield, in effect, a good table of maximum date/times for each SKU/STOCK combination. But, you can get a false hit in it with the comparison if a dt_tm value isn't maximum for some stock, but there are other values dt_tm1 and stock1 such that concat(dt_tm,stock) = concat(dt_tm1, stock1) where dt_tm1 *is* maximum for stock1. That was very abstract; I'll supply some more data, with bogus date/time values to illustrate my point. dt_tm, stock, sku, qty A, BB, C, 1 AB, BB, C, 2 -- note that AB is the max(dt_tm) for stock=BB,SKU=C; A is not the max AB, B, C, 3-- note that AB is the max(dt_tm) for stock=B,SKU=C Subselect yields AB, BB, C and AB, B, C; concatenated they are ABBBC and ABBC Select compares ABBC to the above and it matches, so BB, C, 1 is returned (incorrectly) Select compares ABBBC to the above and it matches, so BB, C, 2 is returned Select compares ABBC to the above and it matches, so B, C, 3 is returned Bruce Feist On Thu, 19 Jun 2003, Bruce Feist wrote: Jake Johnson wrote: This is one quick way to get the newest records of a group if you are grouping by the sku and stock. select stock, sku, qty from table where concat(dt_tm,stock,sku) IN ( select concat(max(dt_tm), stock, sku) from table group by stock, sku ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL question
Hi, I have a SQL issue I cannot figure out, perhaps somebody can help / solve / input on this ... I have a table with stock-status transactions like ... 2003-06-17 06:00 stockA SKU1 QTY 98 2003-06-16 06:10 stockA SKU1 QTY 101 2003-06-15 04:59 stockA SKU1 QTY 111 - the time for updating the transaction - each specific stock - each specific SKU / partnumber - quantity in stock at time of transaction The SQL issue - are there some way in SQL I can SELECT only latest transaction for each stock/SKU no matter date of update, or do I have to read thrue all transactions and select in the program ??? Thanks and best regards Peter Rasmussen Copenhagen Denmark
Re: SQL question
This is one quick way to get the newest records of a group if you are grouping by the sku and stock. select stock, sku, qty from table where concat(dt_tm,stock,sku) IN ( select concat(max(dt_tm), stock, sku) from table group by stock, sku ) Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com Shop Plutoid for the best prices on Rims and Car Audio Products ) Hi, I have a SQL issue I cannot figure out, perhaps somebody can help / solve / input on this ... I have a table with stock-status transactions like ... 2003-06-17 06:00 stockA SKU1 QTY 98 2003-06-16 06:10 stockA SKU1 QTY 101 2003-06-15 04:59 stockA SKU1 QTY 111 - the time for updating the transaction - each specific stock - each specific SKU / partnumber - quantity in stock at time of transaction The SQL issue - are there some way in SQL I can SELECT only latest transaction for each stock/SKU no matter date of update, or do I have to read thrue all transactions and select in the program ??? Thanks and best regards Peter Rasmussen Copenhagen Denmark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL question - no CREATE VIEW in mysql
Hi People. SQL problem: given a table where each column is a number, let's say that I wish to create a second table where columns are the sum of specific columns of the first, i.e.: col_1 + col_2 + col_3 + col_4 col_1 of second table col_5 + col_6 + col_7 + col_8 col_2 of second table col_9 + col_10 + col_11 + col_12 col_3 of second table . In Oracle I could create a view from the initial table, what about MySQL? Do I really have to loop through each row to build the second table? Thanks in advance - 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: SQL question - no CREATE VIEW in mysql
Luca, In Oracle I could create a view from the initial table, what about MySQL? MySQL will support views as of version 5.1. I cannot find it in the todo (http://www.mysql.com/doc/en/TODO.html) but I saw it elsewhere. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - 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: SQL Question
I have 2 tables in our MySQL database like this: TABLE: customers +--+---+--+-+-+- ---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+- ---+ | custid | mediumint(8) unsigned | | PRI | NULL| auto_increment | | name | tinytext | YES | | NULL| | | email| tinytext | YES | | NULL| | | company | tinytext | YES | | NULL| | +--+---+--+-+-+- ---+ TABLE: shipment +-+---+--+-+-+-- --+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- --+ | shipmentid | mediumint(10) | | PRI | NULL| auto_increment | | origin | mediumint(10) | YES | | NULL| | | destination | mediumint(10) | YES | | NULL| | | quoteid | mediumint(10) | YES | | NULL| | | customerid | mediumint(10) | YES | | NULL| | | carrierid | mediumint(10) | YES | | NULL| | +-+---+--+-+-+-- --+ I need to construct a query to find out what customers in the database have not booked shipments with us. That means there would be no records in the shipment table for a given customer id. I can easily get a count of how many shipments are there for customers that have booked (via count() and group), but this one is eluding me.. Any help or suggestions on where to get more info would be appreciated. Thanks, Darren Young The Freight Depot - 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: SQL Question
Darren, I need to construct a query to find out what customers in the database have not booked shipments with us. That means there would be no records in the shipment table for a given customer id. If I get this right, it should be: SELECT custid, name FROM customers LEFT JOIN shipment ON custid = customerid WHERE shipmentid IS NOT NULL or: SELECT custid, name FROM customers INNER JOIN shipment ON custid = customerid Regards, -- Stefan Hinz [EMAIL PROTECTED] CEO / Geschäftsleitung iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Darren Young [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 16, 2002 11:24 PM Subject: RE: SQL Question I have 2 tables in our MySQL database like this: TABLE: customers +--+---+--+-+-+- ---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+- ---+ | custid | mediumint(8) unsigned | | PRI | NULL| auto_increment | | name | tinytext | YES | | NULL| | | email| tinytext | YES | | NULL| | | company | tinytext | YES | | NULL| | +--+---+--+-+-+- ---+ TABLE: shipment +-+---+--+-+-+-- --+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- --+ | shipmentid | mediumint(10) | | PRI | NULL| auto_increment | | origin | mediumint(10) | YES | | NULL| | | destination | mediumint(10) | YES | | NULL| | | quoteid | mediumint(10) | YES | | NULL| | | customerid | mediumint(10) | YES | | NULL| | | carrierid | mediumint(10) | YES | | NULL| | +-+---+--+-+-+-- --+ I need to construct a query to find out what customers in the database have not booked shipments with us. That means there would be no records in the shipment table for a given customer id. I can easily get a count of how many shipments are there for customers that have booked (via count() and group), but this one is eluding me.. Any help or suggestions on where to get more info would be appreciated. Thanks, Darren Young The Freight Depot - 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: SQL Question
On 16 Dec 2002, at 23:50, Stefan Hinz, iConnect (Berlin wrote: I need to construct a query to find out what customers in the database have not booked shipments with us. That means there would be no records in the shipment table for a given customer id. If I get this right, it should be: SELECT custid, name FROM customers LEFT JOIN shipment ON custid = customerid WHERE shipmentid IS NOT NULL Darren wants the customers who do *not* have shipments, so it should be ... WHERE shipmentid IS NULL. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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: SQL question
John, Tuesday, November 05, 2002, 1:50:32 AM, you wrote: JJ I have a callers table and a citylist table. Both tables have a field JJ 'town' and both tables have a field called 'zipcode'. JJ The citylist is a list of cities and their zip codes. JJ citylist.city and citylist.zipcode JJ The callers table has the same 2 field names, but the zip code field is JJ empty. JJ How can I populate the callers.zipcode field by looking up the the zip JJ code in the cities table? JJ I know how to do simple selects and simple updates, but I do no know how JJ to match the callers.town with the cities.town, and then make JJ callers.zipcode equal the zipcode for the callers.town=cities.town match. John, multi-table updates is supported since 4.0.2. So, if you use MySQL server 4.0.2 or high you can just do: UPDATE callers, cities SET callers.zipcode=cities.zipcode WHERE callers.town=cities.town; Otherwise you can do it with programming language or like describen for DELETE here: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html -- 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
newbie: SQL question
Hi, I am just a starter. Anyone can suggest me good web resources for learning SQL command that I can use (compatible) with mySQL ? I read from www.mysql.com documentation, but it's not complete ... Well, if you have collection for beginner, please :) Thanks, kapot __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.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: newbie: SQL question
http://hotwired.lycos.com/webmonkey/backend/databases/ might be a good place, haven't read it, but I send people that want to learn HTML to webmonkey, and they liked it. -Original Message- From: Admin-Stress [mailto:meerkapot;yahoo.com] Sent: Monday, November 04, 2002 10:50 AM To: [EMAIL PROTECTED] Subject: newbie: SQL question Hi, I am just a starter. Anyone can suggest me good web resources for learning SQL command that I can use (compatible) with mySQL ? I read from www.mysql.com documentation, but it's not complete ... Well, if you have collection for beginner, please :) Thanks, kapot __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.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 - 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: newbie: SQL question
Try this: http://www.devshed.com/Server_Side/MySQL - Hi, I am just a starter. Anyone can suggest me good web resources for learning SQL command that I can use (compatible) with mySQL ? I read from www.mysql.com documentation, but it's not complete ... Well, if you have collection for beginner, please :) Thanks, kapot - 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
