Re: newbie join issue
What is the 'idx' for when you already have an 'id' column? Also, you need an index on the column that you are joining on; having a single indexed column on a table doesn't automatically improve all queries against that table. Put an index on the 'email_address' fields of both tables. You'll need: ALTER TABLE la_entire ADD INDEX idx_email_address (email_address); ALTER TABLE la_final ADD INDEX idx_email_address (email_address); See: http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html On Tue, 2004-07-20 at 09:22, Edward Ritter wrote: > Stefan: >I added an index column to each after I imported. Here's a listing of > the two tables. > > la_entire > ++-+--+-+-++ > | Field | Type| Null | Key | Default | Extra | > ++-+--+-+-++ > | id1| int(3) | | | 0 || > | id2| varchar(6) | | | || > | first_name | varchar(30) | | | || > | last_name | varchar(30) | | | || > | street_address | varchar(50) | | | || > | city | varchar(30) | | | || > | state | char(2) | | | || > | zip| varchar(9) | | | || > | email_address | varchar(50) | | | || > | idx| int(7) | | PRI | NULL| auto_increment | > ++-+--+-+-++ > 10 rows in set (0.00 sec) > > ++-+--+-+-++ > | Field | Type| Null | Key | Default | Extra | > ++-+--+-+-++ > | id | int(5) | | | 0 || > | county | int(5) | | | 0 || > | precinct | int(5) | | | 0 || > | last_name | varchar(30) | | | || > | first_name | varchar(30) | | | || > | src_address| varchar(30) | | | || > | src_city | varchar(30) | | | || > | src_state | varchar(20) | | | || > | src_zip| int(5) | | | 0 || > | email_address | varchar(30) | | | || > | new_city | varchar(30) | | | || > | new_state | varchar(20) | | | || > | new_zip| int(5) | | | 0 || > | new_zip4 | int(4) | | | 0 || > | new_address| varchar(30) | | | || > | dma_flag | varchar(4) | | | || > | deceased | varchar(4) | | | || > | phone | int(12) | | | 0 || > | time_zone | varchar(4) | | | || > | phone_sol | varchar(4) | | | || > | cluster| varchar(4) | | | || > | age| varchar(4) | | | || > | income | varchar(4) | | | || > | pres_child | varchar(4) | | | || > | own_rent | varchar(4) | | | || > | length_of_res | varchar(4) | | | || > | buyer | varchar(4) | | | || > | responder | varchar(4) | | | || > | gender | varchar(4) | | | || > | occupation | varchar(4) | | | || > | education | varchar(4) | | | || > | donor_prospect | varchar(4) | | | || > | scr1ast1 | varchar(4) | | | || > | scr1bst1 | varchar(4) | | | || > | scr2ast1 | varchar(4) | | | || > | scr2bst1 | varchar(4) | | | || > | decile1| varchar(4) | | | || > | decile2| varchar(4) | | | || > | decile3| varchar(4) | | | || > | decile4| varchar(4) | | | || > | scr1ast2
Re: MULTI-DELETE BUG when used WITH TABLE ALIASES (ver. mysql-4.1.3-beta-standard)
t; > > Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' > CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' L\ > > DFLAGS='' ASFLAGS='' > > LIBC: > > lrwxrwxrwx1 root root 14 Aug 7 2003 /lib/libc.so.6 -> > libc-2.2.93.so > > -rwxr-xr-x1 root root 1235468 Sep 5 2002 /lib/libc-2.2.93.so > > -rw-r--r--1 root root 2233342 Sep 5 2002 /usr/lib/libc.a > > -rw-r--r--1 root root 178 Sep 5 2002 /usr/lib/libc.so > > Configure command: ./configure '--prefix=/usr/local/mysql' > '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/m\ > > ysql/bin' '--with-comment=Official MySQL-standard binary' > '--with-extra-charsets=complex' '--with-server-suffix=-standard' '-\ > > -enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' > '--disable-shared' '--with-client-ldflags=-all-stati\ > > c' '--with-mysqld-ldflags=-all-static' '--with-readline' > '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpr\ > > o' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' > > > > > > > > If any more information needed, let me know at [EMAIL PROTECTED] > > Thanks, > > > > Asim Thakker > > [EMAIL PROTECTED] > > ext #4176 > > > > > > !DSPAM:40fbe134327511661126047! -- . Garth Webb . [EMAIL PROTECTED] . . shoes * éå * schoenen * ëí * chaussures * zapatos . Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why this query doesn't group the email addresses?
On Tue, 2004-07-13 at 13:51, Justin Swanhart wrote:
> > Where is the implicit group? The 'order by'
> > shouldn't effect how things
> > are grouped. On MySQL 4.0.17:
>
> Wow. I wasn't aware. Is that expected behavior?
Yup, check out:
http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html
> Other databases (Oracle) generate an error when you
> include columns in the select list that aren't in a
> group by, or they do an implicit group by (postgres)
> on the columns. I [wrongly] assumed an implicit group
> by was going on because the query hadn't generated an
> error.
>
> I guess mysql just takes the values from the first
> record that matches the group expression.
Sorta, but its probably better to assume that its random.
> That creates confusion when you do:
> select first,last,email, count(*)
> from foobar
> group by email
>
> Because the query will report a count of two (given
> your data) when there really is only one row with that
> first,last,email combination.
But there are 2 rows with that email which, considering 'email' is the
only thing group'ed on, makes sense. From the link above, one would
only select 'first' and 'last' if they knew they were unique as well and
wanted to save on typing. Probably the only way to trust that is to
have a unique constraint on those three columns.
> Oracle would require you to do:
> select first,last,email, count(*)
> from foobar
> group by first,last,email
> otherwise you would get an error that "first is not a
> GROUP BY expression".
Yah, and Oracle actually enforces foreign keys and won't insert guessed
default values for 'NOT NULL' fields when you try to insert a null value
into them. MySQL keeps you on your toes!
Garth
> That query would return four rows on your data, each
> with a count of 1.
>
> My apologies,
>
> Justin
>
> >
> > create table bar ( email varchar(64), first
> > varchar(64), last
> > varchar(64) );
> >
> > insert into bar values ('[EMAIL PROTECTED]', 'a', 'smith'),
> > ('[EMAIL PROTECTED]', 'a',
> > 'williams'), ('[EMAIL PROTECTED]', 'b', 'webb'), ('[EMAIL PROTECTED]',
> > 'c', 'oconner');
> >
> > mysql> select * from bar;
> > +-+---+--+
> > | email | first | last |
> > +-+---+--+
> > | [EMAIL PROTECTED] | a | smith|
> > | [EMAIL PROTECTED] | a | williams |
> > | [EMAIL PROTECTED] | b | webb |
> > | [EMAIL PROTECTED] | c | oconner |
> > +-+---+--+
> >
> > mysql> select first,last,email from bar b group by
> > b.email order by
> > b.first, b.last;
> > +---+---+-+
> > | first | last | email |
> > +---+---+-+
> > | a | smith | [EMAIL PROTECTED] |
> > | b | webb | [EMAIL PROTECTED] |
> > +---+---+-+
> >
> >
> > > Case differences between the records could also
> > cause
> > > dupes. If case differences are causing it then do
> > >
> > > select lower(first), lower(last), lower(email)
> > > ...
> > > group by lower(first), lower(last), lower(email)
> >
> > Case or extra whitespace is a definite possibility.
> > Aaron, try to find
> > at least one occurrence of duplicate email addresses
> > and then post a
> > small (5 row) dataset that exhibits the problem you
> > are having.
> >
> > Garth
> >
> > > --- Wesley Furgiuele <[EMAIL PROTECTED]> wrote:
> > > > What type of field is the email field?
> > > >
> > > > Wes
> > > >
> > > > On Jul 13, 2004, at 11:04 AM, Aaron Wolski
> > wrote:
> > > >
> > > > > Hey all,
> > > > >
> > > > > Got this query:
> > > > >
> > > > > SELECT first,last,email FROM CustomerTable AS
> > t1,
> > > > > OrderTable AS t2, CartTable AS t3 WHERE
> > > > t2.cart_id=t3.cart_id
> > > > > AND t1.id=t2.customer_index AND
> > t3.submitted='1'
> > > > AND
> > > > > t3.product_index='1' AND t3.quantity>0
> > > > > GROUP BY t1.email ORDER BY t1.first,t1.last
> > > > >
> > > > > For some strange reason it doesn't seem to
> > group
> > > > the email addresses.
> > > > > I
Re: Why this query doesn't group the email addresses?
On Tue, 2004-07-13 at 11:13, Justin Swanhart wrote:
> You are doing an implicit group by of first, last as
> well as your explicit group by of email.
>
> So you could have two records with the same e-mail
> address generate two records with your group by
>
>Justin Time [EMAIL PROTECTED]
>Justin Credible [EMAIL PROTECTED] --DUPE--
Where is the implicit group? The 'order by' shouldn't effect how things
are grouped. On MySQL 4.0.17:
create table bar ( email varchar(64), first varchar(64), last
varchar(64) );
insert into bar values ('[EMAIL PROTECTED]', 'a', 'smith'), ('[EMAIL PROTECTED]', 'a',
'williams'), ('[EMAIL PROTECTED]', 'b', 'webb'), ('[EMAIL PROTECTED]', 'c', 'oconner');
mysql> select * from bar;
+-+---+--+
| email | first | last |
+-+---+--+
| [EMAIL PROTECTED] | a | smith|
| [EMAIL PROTECTED] | a | williams |
| [EMAIL PROTECTED] | b | webb |
| [EMAIL PROTECTED] | c | oconner |
+-+---+--+
mysql> select first,last,email from bar b group by b.email order by
b.first, b.last;
+---+---+-+
| first | last | email |
+---+---+-+
| a | smith | [EMAIL PROTECTED] |
| b | webb | [EMAIL PROTECTED] |
+---+---+-+
> Case differences between the records could also cause
> dupes. If case differences are causing it then do
>
> select lower(first), lower(last), lower(email)
> ...
> group by lower(first), lower(last), lower(email)
Case or extra whitespace is a definite possibility. Aaron, try to find
at least one occurrence of duplicate email addresses and then post a
small (5 row) dataset that exhibits the problem you are having.
Garth
> --- Wesley Furgiuele <[EMAIL PROTECTED]> wrote:
> > What type of field is the email field?
> >
> > Wes
> >
> > On Jul 13, 2004, at 11:04 AM, Aaron Wolski wrote:
> >
> > > Hey all,
> > >
> > > Got this query:
> > >
> > > SELECT first,last,email FROM CustomerTable AS t1,
> > > OrderTable AS t2, CartTable AS t3 WHERE
> > t2.cart_id=t3.cart_id
> > > AND t1.id=t2.customer_index AND t3.submitted='1'
> > AND
> > > t3.product_index='1' AND t3.quantity>0
> > > GROUP BY t1.email ORDER BY t1.first,t1.last
> > >
> > > For some strange reason it doesn't seem to group
> > the email addresses.
> > > I'd be hard pressed to find every occurrence out
> > of 1000 records, but I
> > > DID quickly spot two exact same records which
> > means the email address
> > > was not grouped.
> > >
> > > What can I do or where did I go wrong?
> > >
> > > Thanks!
> > >
> > > Aaron
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> >
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
--
. Garth Webb
. [EMAIL PROTECTED]
.
. shoes * éå * schoenen * ëí * chaussures * zapatos
. Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑ
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
On Thu, 2004-07-01 at 10:03, rmck wrote:
> Hi,
>
> I have a table with ip,port and I want to see the top ten Ip's with the most
> entries?
> Ip's can be in db many times...
>
> Not the first distinct 10... Im stuck...
>
> I have tried:
> mysql> select DISTINCT ip from iptable limit 10;
>
> +---+
> | ip |
> +---+
> | 0.0.0.0 |
> | 10.0.1.42 |
> | 10.0.1.8 |
> | 10.1.1.1 |
> | 10.10.10.1|
> | 10.115.94.193 |
> | 10.115.94.195 |
> | 10.115.94.40 |
> | 10.122.1.1|
> | 10.20.7.184 |
> +---+
> 10 rows in set (0.04 sec)
>
> mysql>
>
> But doesn't that just give the first 10 DISTINCT ip's??
Yes. You need to count the number of times an IP appears and sort by
that count, then limit it:
SELECT ip, COUNT(ip) as num
FROM iptable
GROUP BY ip
ORDER BY num DESC
LIMIT 10
--
. Garth Webb
. [EMAIL PROTECTED]
.
. shoes * éå * schoenen * ëí * chaussures * zapatos
. Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑ
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql join statement that I do not understand
that optimization of joins depends upon what table you are > joining to what: > > > This is the new sql: > > mysql> select > -> cs_fld_cs_tbl_l.cs_type,field_name,name,type,type_sql,rl_table,cs_tbl > from > -> cs_fld_cs_tbl_l left join cs_fld > -> ON cs_fld_cs_tbl_l.cs_fld_id = cs_fld.cs_rid > -> WHERE cs_fld_cs_tbl_l.cs_tbl_id = '23' AND cs_fld_cs_tbl_l.cs_type > = 'basic'; > +-++--++-+-- > ++ > | cs_type | field_name | name | type | type_sql| > rl_table | cs_tbl | > +-++--++-+-- > ++ > | basic | status | Status | recordid | int | > status | [23] | > | basic | body | Main Body| textarea | text| > | [23] | > | basic | section| Section | recordid | int | > demsect | [23] | > | basic | title | Title| text | varchar(50) | > | [23] | > | basic | assignu| Assign to User: | recordid | int | u > | [23] | > | basic | subsect| Subsection | recordlist | list| > subsect | [23] | > | basic | assigngr | Assign to Group: | recordid | int | > cs_com | [23] | > | basic | sorder | Relative Order | numeric| int | > | [23] | > | basic | con_type | Content Type:| recordid | int | > con_type | [23] | > +-++--++-+-- > ++ > 9 rows in set (0.01 sec) > All I did was transpose the cs_fld table with the cs_fld_cs_tbl_l and bam! I > get the more efficient time. > > My question is: How do I determine which table should be on which side of > the join statement? > > Thanks to all who respond - I really am stumped on this one. -- . Garth Webb . [EMAIL PROTECTED] . . shoes * éå * schoenen * ëí * chaussures * zapatos . Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory on INSERT
On Tue, 2004-06-15 at 08:08, J S wrote: > Hi, > > I have a perl script which runs a bulk insert. When I run an insert with > about 100,000 lines it keels over with the following message: > > DBD::mysql::st execute failed: Out of memory (Needed 6100848 bytes) at > ./parse.pl line 227, <> line 15. > > There is 8GB of memory on the box so I'm sure there is enough memory there. > Is there a setting in my.cnf which I need to tweak? How large is the data? How much of that 8GB is used by other processes? Have you watched the output of 'top' while the script is running? MySQL has tweakable limits on how large a particular insert can be, but this error looks like perl is truely running out of memory, rather than being denied by MySQL. -- . Garth Webb . [EMAIL PROTECTED] . . shoes * éå * schoenen * ëí * chaussures * zapatos . Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Databases
4 or 5 tables is pretty small. We've got about 200 tables here, some containing over 100 million rows which still runs well on a simple PIII test server. You are definitely better off having one database serve this data. If you want a backup, the MySQL replication stuff works very well. On Thu, 2004-06-17 at 14:55, David Blomstrom wrote: > I'm working on a rather large database - four or five > tables - that will power eight different websites. > There will also be a few additional supplemental > tables on various sites, but I'd speculate that 90% of > the data will be exactly the same on all eight sites. > > With that in mind, would you recommend creating and > publishing eight separate databases or just publishing > one database and linking all the websites to it? > Actually, I'd probably publish a minimum of two > databases, for security; if one goes down, I'll have a > back up. > > Thanks. > > > > __ > Do you Yahoo!? > Yahoo! Mail is new and improved - Check it out! > http://promotions.yahoo.com/new_mail -- . Garth Webb . [EMAIL PROTECTED] . . shoes * éå * schoenen * ëí * chaussures * zapatos . Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBI and last_insert_id()
You might have better luck with this on the [EMAIL PROTECTED] list,
re: why this doesn't work. This works for me though:
$pk = $dbh->{mysql_insertid};
On Mon, 2004-06-14 at 21:42, Daniel Kasak wrote:
> Hi all.
>
> I'm writing an app in Perl / Gtk2, and I'm having trouble with DBI's
> last_insert_id() function.
> I have so far:
>
> > eval {
> > $sth->execute (@bind_values) || die $dbh->errstr;
> > };
> >
> > # If the above failed, there will be something in the special
> > variable $@
> > if ($@) {
> >
> > # Dialog explaining error...
> > my $dialog = &msgbox(
> > $prospects->get_widget("Prospects"),
> > "Error updating recordset!",
> > "Database Server says:\n" . $dbh->errstr,
> > 1
> >);
> >
> > $dialog->run;
> > $dialog->destroy;
> >
> > warn "Error updating recordset:[EMAIL PROTECTED]" . $@ .
> > "\n\n";
> >
> > return 0;
> >
> > }
> >
> >
> > $pk = $dbh->last_insert_id();
>
> The statement executes successfully, and the data is inserted. However
> the above line that fetches the last_insert_id value from MySQL always
> returns undef. The table has an auto_increment column. What's going on?
>
> Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to COUNT rows when they have a COUNT in them
You could also try: SELECT owner, COUNT(*) FROM pet GROUP BY owner; SELECT FOUND_ROWS(); On Mon, 2004-06-14 at 20:41, Dave Torr wrote: > Thanks - this did not work for me as I am on 4.0.17 - presumably this works > on 4.1 (seems to need the SubQuery feature)? If so I will upgrade > immediately! > > > >From: Yayati Kasralikar <[EMAIL PROTECTED]> > >To: Dave Torr <[EMAIL PROTECTED]> > >CC: [EMAIL PROTECTED] > >Subject: Re: How to COUNT rows when they have a COUNT in them > >Date: Mon, 14 Jun 2004 23:37:15 -0400 > > > >Following query does what you want: > > > >SELECT COUNT(*) from (SELECT COUNT(*) as c FROM pet GROUP BY owner HAVING > >c>1) as temp > > > >-Yayati > > > >Dave Torr wrote: > > > >>Probably simple but I can't figure it out! > >> > >>THe manual section 3.3.4.8 has the example > >> > >>SELECT owner, COUNT(*) FROM pet GROUP BY owner > >> > >>which is fine. Now what I want to do is count the number of rows this > >>returns. Actually of course this is trivial - I can just count how many > >>owners there are. > >> > >>What I actually have is something similar to > >> > >>SELECT owner, COUNT(*) as c FROM pet GROUP BY owner HAVING c>1 > >> > >>(ie I want to see the owners who have more than one pet). And I just want > >>to know how many there are - at the moment I am having to retreive the > >>full data set (which is large in my case). > >> > >>What I want is something like > >> > >>SELECT COUNT(SELECT owner, COUNT(*) FROM pet GROUP BY owner HAVING c>1) > >> > >>but that doesn't work > >> > >> > >> > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
On Mon, 2004-05-24 at 11:32, John Nichel wrote: > Rich Allen wrote: > > iH > > > > this should work > > > > test> select * from xt; > > ++---+ > > | id | field | > > ++---+ > > | 1 | 0 | > > | 2 | 0 | > > | 3 | 7 | > > | 4 | 8 | > > | 5 | 7 | > > | 6 | 0 | > > | 7 | 6 | > > | 8 | 7 | > > | 9 | 8 | > > ++---+ > > 9 rows in set (0.00 sec) > > > > test> select count(distinct(field)) + (select count(*) from xt where > > field=0) - 1 from xt; > > +--+ > > | count(distinct(field)) + (select count(*) from xt where field=0) - 1 | > > +--+ > > |6 | > > +--+ > > 1 row in set (0.01 sec) > > note that i subtract one since i counted a "0" value in the distinct > > part ... > > > > - hcir > > That's what I needed. Thanks! Note that this solution will be off by one if there aren't any zeros in your data. Try this: select count(IF(field>0,NULL,1)) + count(distinct IF(field>0,field,NULL)) from test; -- . Garth Webb . [EMAIL PROTECTED] . . shoes * éå * schoenen * ëí * chaussures * zapatos . Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Relation Design Question
On Thu, 2004-05-20 at 07:13, Lewick, Taylor wrote: > Hi all, I am having a little trouble deciding how to relate some of my > tables together. > > > > I was wondering about creating one big lookup table to relate 3 or 4 > tables together, but wasn't sure if that was a good id, or should I have > a look up table > > For each pair of tables. > > > > Here is a simple example of my tables. > > > > Orgs: org_id org_name (org_id is primary key) > > > > Contacts: con_id, con_name, org_id (con_id is primary, org_id is > foreign key) > > > > Events: ev_id, ev_name, org_id, con_id (ev_id is primary, con_id and > org_id are foreign keys) Its probably unnecessary to have a 'con_id' in the Event table since you can get that information from the 'org_id'. An exception might be if multiple contacts for one organization are allowed, and for any given event you want the ability to specify just one of those contacts. Removing the con_id would also help eliminate the problem where an org_id on an event does not agree with the org_id of the contact given by con_id (input or programming gremlin). Or, you have an existing event where the contact has the same org_id as the event's org_id, but there is a change of contacts at org_id's organization. Someone updates the contact information and now your event either contains an invalid contact, or an ID to a non-existent contact > This is centered around organizations, so every contact must belong to > an org, likewise for an event. I will create an org called None in case > they just want > > To track the occasional lone contact or internal event. > > > > But because an organization can have many contacts and many events, I > was thinking of using lookup tables. > > > > I.e., Contacts are assigned to Organizations, > > So have a table called assigned with org_id and con_id as a composite > primary key. And each is a foreign key back to the correct table... > > > > And should I have a table that links orgs and events and contacts and > events, or should I have one lookup table > > That relates them all together, i.e. orgs contacts, and events..? > > > > To simplify, is It better to have many smaller lookup tables or one big > one? > > > > Thanks, > > Taylor -- . Garth Webb . [EMAIL PROTECTED] . . shoes * éå * schoenen * ëí * chaussures * zapatos . Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT table1 SET col1 = table2.col1
On Mon, 2004-05-17 at 13:37, P. Hill wrote: > Victoria Reznichenko wrote: > > "P. Hill" <[EMAIL PROTECTED]> wrote: > > > >>Why when there is a figurae, figurae2 table does the follow give an error? > >>INSERT INTO figurae2 SET id = figurae.id; > >> > >>The error is: > >>ERROR 1109 at line 35: Unknown table 'figurae' in field list > > > > Look at INSERT .. SELECT statement: > > http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html > > I'm not asking for an alternative way to do it; I'm asking what > is wrong with what I did? Did you read: http://dev.mysql.com/doc/mysql/en/INSERT.html this page? Specifically, the second line of this page states: "The INSERT ... VALUES and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables." You can only use one table in an insert and with the SET syntax it must have a specific value. The name after 'INSERT INTO' tells MySQL what table you intend to insert into, so when you tried to specify a second table you got the "Unknown table..." error. It looks like you want to copy over several or all values from a second table. The INSERT .. SELECT syntax isn't an alternate way, its the only way. > I did rework it to an alternative syntax, but I wanted to > use the SET syntax so I could list all my old fields right > next to my new fields, so that I didn't have to > skip up and down 40 lines to see how I have them matched up. > > So does INSERT ... SET ... work? How? > > -Paul > > -- . Garth Webb . [EMAIL PROTECTED] . . shoes * éå * schoenen * ëí * chaussures * zapatos . Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Table Joins Example
On Sat, 2004-05-15 at 04:04, David Blomstrom wrote: > --- Jigal van Hemert <[EMAIL PROTECTED]> wrote: > > > If you need to know how to display the resulting > > record sets, example 1 on: > > http://www.php.net/manual/en/ref.mysql.php > > gives you a complete piece of code to print out the > > resulting records. > > OK, I think this example points out what I'm doing > wrong, even if I still don't get it. > > Here's the top portion of my code, which was primarily > inserted by Dreamweaver: I think your problem is dreamweaver. From these posts it seems like you're new to both PHP and MySQL and dreamweaver is just compounding your confusion over both these problems. Additionally, you can't fix everything at once. For now, dump dreamweaver. Forget about trying to display the data in PHP in the way you want. Concentrate on getting MySQL to do a join and return the data you want. If you have a command line interface to MySQL (or a GUI interface) use that and forget about PHP for the moment. If you don't have access to these tools, copy the code exactly that Jigal gave above to display the results from an SQL query. You need a way to narrow your problems and questions to just the SQL because that is all on which this list will really be able to help you. Once you get an SQL query that returns the data you want, then try to use PHP to format the results in the way you want. If you are successful at that, and you still think Dreamweaver will save you time, then move to using Dreamweaver to create your pages. Only after you've figured out all the intermediate steps will it be possible to fix the code dreamweaver outputs when it goes wrong. Garth -- . Garth Webb . [EMAIL PROTECTED] . . shoes * éå * schoenen * ëí * chaussures * zapatos . Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Querying rows between two strings
On Tue, 2004-05-11 at 10:45, Lionel Pitaru wrote: > Hello > I would like to make a question: I'm creating a list with a scripting lenguage with > MySql database. The list presents username, first name and last name of persons. It > has three pairs of filters for username, first name and last name. For each of them > the user of the list can type a text as limit for his field with a from and until > manner. I would like to create this query with regexp, but i can't make it work. > An example of working in this: > > UsernameFirst nameLast name > - > alberto alberto gomez > ariel ariel ramirez > leandro lean123 pass123 > > with this filters: > username from 'a' until 'le' > first name from 'al' until 'lean' > there should be the hole list > > but with this filters: > username from 'a' until 'la' > first name from 'al' until 'lean' > there should be this list: > > UsernameFirst nameLast name > - > alberto alberto gomez > ariel ariel ramirez > > I hope i was clear and you could help me with this. You can use 'between' to do this. The only catch is that your ranges don't completely cover the dataset you want returned in the traditional manner. As an example, if we were using numbers, what you are asking for is something like asking for numbers between 2 and 7 from the list: 1 2.3 4 6 7.8 9 and desiring the result to be 2.3 4 6 7.8 In this case asking for all numbers 2 <= X <= 7 doesn't work because you want ANY number starting with a 7. So, to get the affect that you want, you'd have to ask for 2 <= X <= (7+1). Likewise, for your first example you'd use: SELECT * FROM names WHERE username BETWEEN 'a' and 'ld' AND first_name BETWEEN 'al' and 'leao' Note that for the end condition of each comparison the last letter is increased by one letter so that it completely covers all words that begin with the target letters. The second example would be: SELECT * FROM names WHERE username BETWEEN 'a' and 'lb' AND first_name BETWEEN 'al' and 'leao' HTH -- . Garth Webb . [EMAIL PROTECTED] . . shoes * éå * schoenen * ëí * chaussures * zapatos . Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie data import question
On Thu, 2004-05-06 at 07:24, luther van dam wrote: > I am new to MySQL but I some basic database experience. > I have about 50,000 rows of data in a CSV file. > > Where I can find some examples of SQL scripts that show how to: > a. Create a database X. > b. Create a table Y. > c. Import the 50,000 rows of CSV data into table Y. > > I am running MySQL on Red Hat 9 A search at 'http://dev.mysql.com/doc/mysql/en/' yields: a. http://dev.mysql.com/doc/mysql/en/CREATE_DATABASE.html b. http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html c. http://dev.mysql.com/doc/mysql/en/mysqlimport.html > -- > ___ > Sign-up for Ads Free at Mail.com > http://promo.mail.com/adsfreejump.htm > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
On Mon, 2004-05-03 at 11:21, Bob Ramsey wrote: > Ah, but the ordering is not random. As your example has it, the results > are in the order that the entries were inserted into the table. There is > an explanation for the order of the returned data. I don't think the point of the original question was "Are returned results random and completely unpredictable", but "Can I rely on the order of unordered results to be the same every time". The test I provided proves that no, they cannot be relied upon to be same every time. Anyway, if result order isn't the same every time, but in an arbitrary database specific order, they might as well be random. > bob > At 12:55 PM 5/3/2004, Garth Webb wrote: > >On Mon, 2004-05-03 at 10:39, Boyd E. Hemphill wrote: > > > My boss says that if you do a select statement against a table the > > > result set always comes back in the same order. I say that this is a > > > myth and that the result is random, except when some ordering is > > > specified in the SQL statement. > > > > > > Who is right? Is this behavior specified by ANSI or ISO? > > > >You are correct. Ordering takes time. Why choose a random column on > >which to order the results and take additional time when the user didn't > >ask for it. Here's the proof: > > > >create temporary table foo (num int(10)); > >insert into foo values (1), (2), (3), (4), (5); > >select * from foo; > > > >+--+ > >| num | > >+--+ > >|1 | > >|2 | > >|3 | > >|4 | > >|5 | > >+--+ > >5 rows in set (0.00 sec) > > > >delete from foo where num = 3; > >insert into foo values (6); > >insert into foo values (3); > >delete from foo where num = 6; > >select * from foo; > >+--+ > >| num | > >+--+ > >|1 | > >|2 | > >|4 | > >|5 | > >|3 | > >+--+ > >5 rows in set (0.00 sec) > > > > > >Garth > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > == > Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III > ph: 1(319)335-9956 187 Boyd Law Building > fax: 1(319)335-9019 University of Iowa College of Law > mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 > For Hardware and Software questions, call 5-9124 > == > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
On Mon, 2004-05-03 at 10:39, Boyd E. Hemphill wrote: > My boss says that if you do a select statement against a table the > result set always comes back in the same order. I say that this is a > myth and that the result is random, except when some ordering is > specified in the SQL statement. > > Who is right? Is this behavior specified by ANSI or ISO? You are correct. Ordering takes time. Why choose a random column on which to order the results and take additional time when the user didn't ask for it. Here's the proof: create temporary table foo (num int(10)); insert into foo values (1), (2), (3), (4), (5); select * from foo; +--+ | num | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) delete from foo where num = 3; insert into foo values (6); insert into foo values (3); delete from foo where num = 6; select * from foo; +--+ | num | +--+ |1 | |2 | |4 | |5 | |3 | +--+ 5 rows in set (0.00 sec) Garth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create a table from SHOW TABLE STATUS ?
On Fri, 2004-04-30 at 06:03, Richard A. DeVenezia wrote: > I'm running 4.1.1a-alpha-max-nt using innodb tables with foreign keys . > I know how to use SHOW TABLE STATUS to see the referential linkages in the > COMMENT column. > > Supppose I am typing away in MySQL monitor: > Q: Is there a way to create a table from the SHOW TABLE STATUS command ? Not from SHOW TABLE STATUS, but you can duplicate a table. From the "create table" docs (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html): "In MySQL 4.1, you can also use LIKE to create a table based on the definition of another table, including any column attributes and indexes the original table has: CREATE TABLE new_tbl LIKE orig_tbl; CREATE TABLE ... LIKE does not copy any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table." > Q: Does v5 have system views that are equivalent to SHOW xyz commands ? > > Thanks, > Richard A. DeVenezia > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simplifying OR clauses
On Mon, 2004-04-26 at 14:59, Matthias Eireiner wrote: > hi there, > > I have a basic question: > how can I simplify multiple OR statements in a WHERE clause where I have > only one column to which I refer? > > e.g. > > SELECT word FROM word_table WHERE id = 1 OR id = 34 OR id = 78 OR id = 8787 > OR ... Try 'IN': SELECT word FROM word_table WHERE id IN (1, 34, 78, 8787); > I thought I once read over something like this but I can't find it right > now. Would be great if somebody could help me out! > Thanks a lot in advance! > > regards > > Matthias > > > _ > > Matthias Eireiner > > email: [EMAIL PROTECTED] > > www.bvcapital.com > _ > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
On Fri, 2004-04-16 at 14:09, Dirk Bremer (NISC) wrote: > - Original Message - > From: "Victor Pendleton" <[EMAIL PROTECTED]> > To: "'Dirk Bremer (NISC) '" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Friday, April 16, 2004 15:57 > Subject: RE: SQL Query Question > > > > If your data is stored in the following format > > 2004-04-16 00:00:00 > > you can do WHERE queue_time = CURRENT_DATE() + 0 > > You will also be able to take advantage of an index. > > > > Else, if you data is kept in the datetime format, > > 2004-04-16 15:53:27 > > one option is to do > > WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 > > ...no index usage though > > > Victor, > > The data defined as a timestamp, i.e. a number rather than a string, so it > has MMDDHHMMSS values. So it looks like I'll need to do some type of > substring on it. You could keep any index you have and do it this way: SELECT a,b FROM x WHERE queue_time BETWEEN date_format(curdate(), "%Y%m%e00") AND date_format(curdate(), "%Y%m%e235959"); -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
RE: Error ?
Your join syntax is incorrect. Check out: http://dev.mysql.com/doc/mysql/en/JOIN.html In particular, you join against an alias (t2), when you need to join on an actual table. On Thu, 2004-04-15 at 14:25, Justin Palmer wrote: > Hi, > > Can anyone give me a hint to what this error message is trying to say. > > Thanks, > > Justin Palmer > > > -Original Message- > From: Justin Palmer [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 15, 2004 1:38 PM > To: [EMAIL PROTECTED] > Subject: FW: Error ? > > > The query that generated this error was: > > SELECT t1. * > FROM `wat_student_profile` AS t1, `wat_student_job` AS t2 > LEFT JOIN t2 ON t1.student_id = t2.student_id > WHERE t1.cv != '' AND t1.sevis_id = '' AND t2.student_id = NULL > LIMIT 0 , 30 > > Regards, > > Justin Palmer > > > -Original Message- > From: Justin Palmer [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 15, 2004 1:28 PM > To: [EMAIL PROTECTED] > Subject: Error ? > > > Hi, > > I get this error from a query: > > #1066 - Not unique table/alias: 't2' > > What does it mean? Can anyone point me to a place where I can look up > what these codes actually mean? > > Any help would be great. > > Regards, > > Justin Palmer > > > > > -- > 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] > > -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: How can I detect the end of a replication cycle?
On Tue, 2004-04-13 at 13:13, [EMAIL PROTECTED] wrote:
[snip]
> >> I could frequently poll the slave servers using SHOW SLAVE STATUS or
> SHOW
> >> PROCESS LIST and watch for their status to change.
> >>
> >> I could start each slave with "--log-slave-updates" and watch the
> slaves'
> >> binlogs for changes.
> >>
> >> I could watch for the creation/deletion of the slave's relay logs.
>
> > This seems to indicate that you are afraid of selecting rows on the
> > slave that are in the middle of being updated from the master. A single
> > update statement is still atomic, so you don't need to poll log files to
> > determine if an update statement has finished.
>
> > On the other hand, if there is some set of multiple updates and inserts
> > that constitute a collection of data that you want to merge only when
> >this collection is complete, you're better off finding a way to signal
> > this through the database. You could have the master lock the tables in
> > question until its finished and then the program quering the slave knows
> > that when it gets a read lock, its will see the full set of data. You
> > could also have a status column or a status table that has a flag
> > letting the program on the slave side know when the data is ready.
>
> > If this is off the mark, maybe some example statements would help...
>
> [more snipping]
>
> Yes, I AM concerned about getting a partial update to the warehouse. I know
> that transactions aren't logged until after they commit. If I use
> transactional boundaries to post multitable reports (for instance: an
> invoice takes two tables, one for the base information and one for the line
> items) into the branch masters then they will arrive intact and I won't
> corrupt the central slaves. (That's NOT the issue I am worried about!)
>
> I am worried that if I start processing those new records from the slave
> database to the Warehouse before all of the records have been processed
> from the Relay Logs (lets say I started trying to merge records when I see
> the relay log being created), I could miss some data (like the last few
> items on the invoice). That's why I am so worried about not merging until
> the END of the cycle. I need to be sure that everything has been committed
> to my central slave BEFORE I can merge the latest updates with the
> warehouse database.
>
> I can lock a slave database so that it won't replicate in the middle of my
> merging so I know that if I can catch a slave when it goes back to "sleep"
> ("Waiting for master to send event"), I would have a complete set of data.
> I could use "--log-slave-updates" to copy the updates to the slave's binlog
> and check that to see if I need to merge records( if slave status is
> "waiting" and the binlog is not "empty" then merge). Each merge could flush
> the binlog. However, there was a post from someone using binlogs for
> similar purpose that said that for 4.1.x+ the binlogs vary in size after
> flushing so I don't know how reliable that would be as a check. How could I
> tell when a binlog is empty?
Using the binlog in this way will lead to a race condition. What if
another update comes in during the few milliseconds between your 'ready'
check (an empty binlog and a 'waiting' status) and when you select those
rows for processing? Plus if MySQL does any buffering of its output to
the binlog, you could be basing your check on seconds old data, further
aggravating the problem.
The only solution I can think of that won't cause a race condition is to
lock your tables, but your program running on the slave database would
have to be able to connect to the master. Your slave program would lock
the necessary tables on the master side, wait to receive the lock, wait
until the slave had caught up, do your merge, then release the lock.
Likewise, the code updating tables on the master would need to lock the
tables while they write. You make the process a little friendlier by
creating a separate control table that the slave and master alternately
locked, rather than locking all the table you'll use. That way
processes on the master that just want to read the data don't have to
wait for a lock.
--
|- Garth Webb -|
|- [EMAIL PROTECTED] -|
signature.asc
Description: This is a digitally signed message part
Re: How can I detect the end of a replication cycle?
On Tue, 2004-04-13 at 11:21, [EMAIL PROTECTED] wrote: > Hello List: [snip] > Here is where things get sticky. I would like to be able to merge the > replicated reports into the warehouse database with the smallest practical > delay (I have some very process-driven branch mangers and they feel they > "must have" this data sooner than later). I think that I will need to hold > off merging records from any particular branch until replication completes > with that branch (to maintain relational integrity, transaction boundaries, > etc. Nobody said the reports were simple.). Somehow, I need to detect the > end of my central slaves' replication cycle so that I can trigger the > "merge processing". Its not clear what you mean by 'replication cycle'. > I could frequently poll the slave servers using SHOW SLAVE STATUS or SHOW > PROCESS LIST and watch for their status to change. > > I could start each slave with "--log-slave-updates" and watch the slaves' > binlogs for changes. > > I could watch for the creation/deletion of the slave's relay logs. This seems to indicate that you are afraid of selecting rows on the slave that are in the middle of being updated from the master. A single update statement is still atomic, so you don't need to poll log files to determine if an update statement has finished. On the other hand, if there is some set of multiple updates and inserts that constitute a collection of data that you want to merge only when this collection is complete, you're better off finding a way to signal this through the database. You could have the master lock the tables in question until its finished and then the program quering the slave knows that when it gets a read lock, its will see the full set of data. You could also have a status column or a status table that has a flag letting the program on the slave side know when the data is ready. If this is off the mark, maybe some example statements would help... > Basically I wind up using a timer to check the status of (something) and I > am afraid that I will miss a status flip between timer ticks. Setting my > timer too short will just consume excessive CPU cycles and also be counter > productive. The target platform for my central server is tentatively Redhat > 9.x with MySQL 4.1.xxx (the stable version when we go live). Can Linux help > me here to hook into one of those events? If another OS can provide better > hooks into this I can work that into the plan. > > Can anyone tell me why these ideas would or would not work? Is there a > better way to synchronize an external process with the end of a replication > event? Are there other options I didn't think of? (I even thought of > modifying the MySQL source to produce a special "merge" slave but that will > take too many people, too much time, be too task-specific, and may not be > compatible with future versions of MySQL to be a good choice.) Please help! > > > Thanks in advance! > Shawn > [EMAIL PROTECTED] > > ** delete all the parts with "no" in them to reveal my real address. ** -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: Get MySQL to ignore the backslashes?
Hi Alex. This question was discussed last week. Search the archives
for 'backslash'. The short answer is that there is no global option to
do what you want. You'll have to do it in code.
On Mon, 2004-04-12 at 17:10, [EMAIL PROTECTED] wrote:
> Is there a 'global' way to tell MySQL to not interpret the backslashes ('\'s) in the
> submitted SQL as escape characters? In other words, I'd like for them to always be
> treated as if they themselves were already escaped with a backslash (i.e. '\\').
> I'm using 3.23.52, accessing it with an older (2.x?) MM JDBC driver. I know of a
> couple of code-level solutions that will require a number of changes in the existing
> code, but was hoping to find a more 'global' solution, via either a global call or a
> configuration setting. Any ideas are much appreciated.
>
> Thanks in advance,
>
> Alex Zeltser
>
>
> __
> Introducing the New Netscape Internet Service.
> Only $9.95 a month -- Sign up today at http://isp.netscape.com/register
>
> Netscape. Just the Net You Need.
>
> New! Netscape Toolbar for Internet Explorer
> Search from anywhere on the Web and block those annoying pop-ups.
> Download now at http://channels.netscape.com/ns/search/install.jsp
--
|- Garth Webb -|
|- [EMAIL PROTECTED] -|
signature.asc
Description: This is a digitally signed message part
Re: Am I doing things right? (selecting groups of objects problems)
On Fri, 2004-04-09 at 09:48, [EMAIL PROTECTED] wrote: > Hello, > > I've recently came across a problem I couldn't seem to solve right by myself. > > I have a db with objects table, each of those objects may belong to groups > of objects. The number of groups can be about 256 and an object > belongs from one to many different groups at once. > > I cannot find a good way to store/select information about groups for > objects that belong to several groups. > > My first idea was to put a 'groups' column and keep there the > id_numbers of groups to which particular object belongs to (with > separators). So the groups column would have values like '100,203,204,208' > etc. Of course this is not the right way to do it as those values are not > atom and the lenght of the field may easily grow out of tinytext length > type I was using... > > It worked ok though for small number of groups and objects though I > know this was a perfect example of how *not* do do it in a RDB... If > I wanted an object that belonged to 2 groups (100 and 203 for example) > I would do "SELECT [...] LIKE '%100%203%'". You definitely do not want to do it this way. You'd have to keep the 'groups' column items sorted to make sure you never have a situation where you query for '%100%203%' when it contains (203,204,100). This makes it complicated/non-trivial to add or remove objects from a group. Additionally, you'd be doing a full text search for each query which is vastly slower than querying on an indexed field. > My next attempt was to create separate table with object_id and > group_id column, this way an object that belongs to many groups would have > several rows in the table. If I wanted to know what are the groups > that my object belongs to - a piece of cake. But things get really rough if > I want to get objects that (for example) belong to two particular (1 & > 2) groups. I would go with something like that: > > SELECT DISTINCT(g1.object_id) FROM connecting_table as g1, connecting_table > as g2 WHERE g1.group_id = 1 AND g2.group_id = 2 AND g1.object_id = > g2.object_id; > > But this is getting more and more complex if I want to select objects > that belong to 3, 4 and N groups at once :/ The first solution > although very ugly seems to be much more clearer (and maybe even more > efficient for groupn number of 2+?). This is the right way and more efficient, but why are you making it so difficult on yourself? ;) SELECT DISTINCT object_id FROM connecting_table WHERE group_id = 1 AND group_id = 2; There's no need to select the same table twice. You can make this even simpler by using 'IN': SELECT DISTINCT object_id FROM connecting_table WHERE group_id IN (1, 2) > My working solution for now is to have a separate table with object_id > and groups_id1 column of INT type, I have assigned the first 31 existing > groups to corresponding bits of the INT field. That is if I want to > select objects that belong to the groups that have ids of 1,3,5 and 26 i > would set the bits nr 1,2,4 and 26 of a variable $MY_INT and then query: > > SELECT object_id,groups_id1 FROM connecting_table WHERE > BIT_COUNT(groups_id1 & $MY_INT) = 4; > > this seems to give me what I want but now if I want to add next 31 > groups I need to create another groups_id2 column and things again > get a bit complicated - I need now to check in which column the > current group flag I need to find out may be stored and act > accordingly (so groups with id 1-31 in column groups_id1, groups 32-63 in > groups_id2 etc). Not a big problem and this solutions seems to be quite > cheap (is it?) but somehow I am feeling there is a better way to > do all this... This has the same problems as the LIKE '%100%203%' approach, but a full text search is replaced by math on each row. In both cases you lose the ability to use any kind of index. > Sorry for this long letter I hope I have managed to put the problem > straight, I may miss something obvious here but as I > said I have limited knowledge about sql. I would be much grateful for any > tips. > > > > Lecho -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: Need help creating table...
Looks like you are using backticks instead of single quotes on your column names. You don't really need the quotes on your table name or column names anyway, so I'd just remove them. On Tue, 2004-04-06 at 09:29, Marvin Cummings wrote: > > > > _ > > From: Marvin Cummings [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 06, 2004 9:26 AM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Need help creating table... > > > > I attempt to create this table from the command line and get the following > error: > > Error 1064 (42000): You have an error in your SQL syntax. Check the manual > that corresponds to your MySQL version for the right syntax to use near > ''ad_id' smallint(5) unsigned NOT NULL auto_increment, 'cat_id' smallint(5) > unsign' at line 3 > > CREATE TABLE 'nuke_zc_ads' > > ( > `ad_id` smallint(5) unsigned NOT NULL auto_increment, > `cat_id` smallint(5) unsigned NOT NULL default '0', > `user_name` varchar(20) NOT NULL default '', > `email` varchar(50) NOT NULL default '', > `price` text NOT NULL, > `condition` varchar(10) NOT NULL default '', > `city` varchar(20) NOT NULL default '', > `state` varchar(20) NOT NULL default '', > `country` varchar(20) NOT NULL default '', > `lastup_date` int(11) NOT NULL default '0', > `subject` text NOT NULL, > `descript` text NOT NULL, > `url` text NOT NULL, > `views` int(11) NOT NULL default '0', > `paypal` char(3) NOT NULL default 'No', > `add_date` int(11) NOT NULL default '0', > `exp_date` int(11) NOT NULL default '0', > PRIMARY KEY (`ad_id`) > ) TYPE=MyISAM AUTO_INCREMENT=1 ; > > > > I'm typing this exactly as it appears but it's not working. I also try to > create this table in phpMyAdmin but have a question: > > Field Type > > ad_id SmallInt 5 > > > > Length/Values > > 5 > > > > Charset > > Latin1 > > > > Attributes > > unsigned > > > > Null > > Not_null > > > > Default** > > ? > > > > Extra > > auto_increment > > > > I'm not sure what to enter for Default**. Can someone tell me what I'm > missing? > > I'm using MySQL5.0alpha w/PHP4.3.4 on W2K3. > > > > Thanks > > -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: Sorting with an alpha numeric field.
On Tue, 2004-03-23 at 09:54, Jacque Scott wrote: > I need to create a query that will sort by a alpha numeric field. I > need the ID field returned and one problem that I will face is that when > it sorts in alpha numeric the ID field will be out of sequence. I will > use this ID field in another part of the program so I need to make sure > I get all of the ID. First, you cannot have your results sorted by two columns at the same time. Choose the ID field or the alphanumeric field. Secondly, sorting by an alpha numeric field will still give you all rows in the table; you will not lose data when you sort. So what exactly is your question? A question ans some examples of things you have tried already would help. -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: A join I can not wrap my head around...
create table account ( id int(11), state varchar(2) ); create table transaction ( id int(11), user_id int(11), amount int(6), added timestamp ); select sum(transaction.amount) from transaction, account where transaction.id = account.id AND account.state = 'TX' AND transaction.added between '2003010100' and '20031231235959'; On Mon, 2004-03-15 at 14:37, Scott Haneda wrote: > Account table has a field state and a field id. > Transaction table has fields id, user_id, amount, added > > Id is the PK in the account table, related to the user_id in the transaction > table. > > I need to get a sum of the amount column for all users in state of TX in the > year of 2003. > > Tried a few ways, cant seem to get this one. So, given: create table account ( id int(11), state varchar(2) ); create table transaction ( id int(11), user_id int(11), amount int(6), added timestamp ); Did you try: select sum(transaction.amount) from transaction, account where transaction.id = account.id AND account.state = 'TX' AND transaction.added between '20030101000000' and '20031231235959'; ? -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: Reversing the group...
On Mon, 2004-03-08 at 15:52, David Scott wrote: > OK, here is a problem that has been bugging me for some time. > > If I have a table like this, showing 3 visitors to a website and the pages they > view > > idvisitorIDpageID > 113 > 214 > 312 > 423 > 524 > 632 > 734 > > so to get the count for each entry page to the site I would do: > > SELECT pageid, count(pageID) as 'count' FROM hits GROUP BY visitorid ORDER BY > 'count' DESC > > pageidcount > 32 > 21 > > so page 3 has been the first page people visit 2 times and page 2 has been the first > page once. I think you've misunderstood the results of this query. First off, with the data you've provided, the result of the above query will be: mysql> SELECT pageid, count(pageID) as 'count' FROM hits GROUP BY visitorid ORDER BY 'count' DESC; ++---+ | pageid | count | ++---+ | 3 | 3 | | 3 | 2 | | 2 | 2 | ++---+ 3 rows in set (0.05 sec) Note the extra row and that page ID 2 has a count of 2. The problem here is that the 'count' column isn't a count of the number of times a page ID has been the first page (and I assume "first" is defined by having the lowest 'id' value for a particular user) hit by a visitor. Rather, its the count of the *total* pages seen by each visitor. Your query tells MySQL you want to group the result set on visitorid, which means that you only want to see one row returned per unique visitorid. Aggregate functions like "count" and "sum" tell MySQL what do to with the other non-grouped columns on each row that a particular visitorid stays the same. You've specified that you want a count of each pageid while visitorid stays the same, which is what the 'count' result column shows. You've also specified that you want to show pageid. However, pageid isn't grouped and its isn't part of an aggregate function, so in this case MySQL happens to show you the first value it found for that visitorid. However, as stated in the MySQL docs, you aren't guaranteed to get the first value found for these non-grouped columns: http://www.mysql.com/doc/en/GROUP-BY-hidden-fields.html Since the field you've left out of the group clause aren't unique in the group, you can't depend on it being what you think. Unfortunately, I don't think you can do what you want in one SQL statement. You could do it with temporary tables though: create temporary table first ( id int(11), pageid int(11) ); insert into first (pageid, id) select pageid, min(id) as firstid from hits group by visitorid; select pageid, count(pageid) as count from first group by pageid order by count; > BUT how do I get the last page they have visited? a sort of reverse GROUP BY??? > Which would return... > > pageidcount > 42 > 21 > > Please Help its driving me nuts :S If you want the last page seen, then you could change the 'min' to a 'max' in the statements above. -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: Changing default delimiter
I'm not sure in what context you want to change the delimiter, but here's the doc page on the LOAD DATA command which shows how to terminate (a.k.a. delimit) fields with different characters: http://www.mysql.com/doc/en/LOAD_DATA.html On Mon, 2004-02-23 at 13:53, David Perron wrote: > Greetings - > Been scouring the docs for this all day and I come up with nothing for > 'delimiter' - Id like to change the default delimiter in mysql to something > other than tab. > Is there a command to do this, and what are the options? > > Thank you! > > David > > -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: [Slightly OT] Tricky SQL Query
On Tue, 2004-02-17 at 11:00, A. Clausen wrote: > I am trying to construct what is proving to be a somewhat tricky SQL query. > I have a database in which wireless activity data is dumped. The following > is a boiled down version of what the table and the contents may look like: > > Date,Unit,InputOctets,OutputOctets,AccountingID > 2004-01-31 23:59:05,0E:3A:0F,10101,3949,SLAKRMMM > 2004-02-01 00:04:07,0E:3A:0F,13843,4192,SLAKRMMM > 2004-02-01 00:09:10,0E:3A:0F,53233,9021,SLAKRMMM > 2004-02-01 00:14:08,0E:3A:0F,2383,109,NX0ADFF > ... > 2004-02-29 23:59:15,0E:3A:0F,481234,18233,LAQ5YN3 [snip] > What I need to do is to find out just how much traffic that unit has done in > the month of February. I realize I will have to do one query to discover > the byte counts for the last record in January, but beyond that I'm stumped. What about this: select Unit, sum(InputOctets), sum(OutputOctets) from your_table where date between '2004-02-01 00:00:00' and '2004-02-29 23:59:55' group by Unit; -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Inconsistant TIMESTAMP behavoir
Hi, I am experiencing strange behavior from a TIMESTAMP column in an
InnoDB table on my MySQL 4.0.16 server. Specifically, it fails to
select certain rows by this column. The table is defined as:
CREATE TABLE log (
s_id int(11) not null,
p_id int(11) not null,
action enum('A', 'B', 'C'),
tstamp timestamp(14),
index ( s_id, p_id ),
index ( tstamp )
);
My problem exists for the first few rows in this table. For example,
take the first row by date:
mysql> select min(tstamp) from log;
++
| min(tstamp)|
++
| 20031026010002 |
++
Now consider the following attempts to select this row:
mysql> select * from log where tstamp = 20031026010002;
mysql> select * from log where tstamp = '20031026010002';
mysql> select * from log where tstamp = "20031026010002";
mysql> select * from log where tstamp = "2003-10-26 01:00:02";
mysql> select * from log where tstamp > "20031026010001"
-> and tstamp < "20031026010003";
None of these return any rows. To add insult to injury, this database
is replicated, and on the *replicated* database, ALL of these queries
work. The only query by date that work on the main database is:
mysql> select * from log where tstamp < "20031026010003"
Which doesn't make any sense considering that the date range query
didn't work.
It seemed like this table must be corrupted somehow, so I ran a 'check
table' on it which return a status of OK.
Unfortunately I cannot replicate this problem, so my hope is that
someone else has experienced this as well, or that someone can suggest a
method to repair this table.
Some final notes. While this problem may have existed previously, I
only noticed it when I began deleting old log data by date via a
script. Furthermore, only the first 2000 or so earliest dates cannot be
selected. All dates after this 'blackout' period can be selected
without a problem.
--
|- Garth Webb -|
|- [EMAIL PROTECTED] -|
signature.asc
Description: This is a digitally signed message part
