Re: Can these two queries be combined into one?

[Peter Brawley]

Is there anyway to combine these 2 queries into 1?


Is this what you mean?

-- list all prods-cats
SELECT p.prod_id, c.category
FROM product_table p
LEFT JOIN catproduct_table cp ON cp.product_id = p.product_id
JOIN product_table p ON p.product_id = cp.product_id;

... or this ...

-- group cats for prods
SELECT p.prod_id, GROUP_CONCAT(c.category)
FROM product_table p
LEFT JOIN catproduct_table cp ON cp.product_id = p.product_id
JOIN product_table p ON p.product_id = cp.product_id
GROUP BY p.prod_id ;

PB

[EMAIL PROTECTED] wrote:

Hi,

 I'm currently using 2 queries to obtain some data and would like to know if I 
can combine these two queries into one query.

I have a database with 3 tables:

1.  A category table
2.  A product table
3.  A normalized "catproduct" table that is used to determine which product is 
in which category.  A product may be in multiple categories.

I have a website where when a person navigates to a category it shows all the products.  
I would like to create a "related categories" list by finding all the 
categories the products in the current category belong to.

Right now I use 2 SQL statements:

1. The first query gets all the products in the current category.

SELECT product_table.prod_id FROM
   product_table
LEFT JOIN catproduct_table ON (catproduct_table.product_id = 
product_table.product_id)
WHERE (catproduct_table.category_id = '1') LIMIT 5

2. The second query gets all the categories that these products belong to:

SELECT category_table.category FROM category_table
  LEFT JOIN catproduct_table ON (catproduct_table.category_id = 
category_table.category_id)
  LEFT JOIN product_table ON (product_table.product_id = 
catproduct_table.product_id)
  WHERE product_table.prod_id IN ('1000,1001,1002,1003,1004')


These two queries are my proof of concept so I'm only limiting the first query 
to 5 results.  In reality I have thousands of products per category so I prefer 
not to limit the first query.  I don't have many categories so the results of 
the second query are quite small.

Is there anyway to combine these 2 queries into 1?

Thanks,
Mason

http://www.retailretreat.com
 




[EMAIL PROTECTED] wrote:

Hi,

  I'm currently using 2 queries to obtain some data and would like to know if I 
can combine these two queries into one query.

I have a database with 3 tables:

1.  A category table
2.  A product table
3.  A normalized "catproduct" table that is used to determine which product is 
in which category.  A product may be in multiple categories.

I have a website where when a person navigates to a category it shows all the products.  
I would like to create a "related categories" list by finding all the 
categories the products in the current category belong to.

Right now I use 2 SQL statements:

1. The first query gets all the products in the current category.

SELECT product_table.prod_id FROM
product_table
 LEFT JOIN catproduct_table ON (catproduct_table.product_id = 
product_table.product_id)
 WHERE (catproduct_table.category_id = '1') LIMIT 5

2. The second query gets all the categories that these products belong to:

SELECT category_table.category FROM category_table
   LEFT JOIN catproduct_table ON (catproduct_table.category_id = 
category_table.category_id)
   LEFT JOIN product_table ON (product_table.product_id = 
catproduct_table.product_id)
   WHERE product_table.prod_id IN ('1000,1001,1002,1003,1004')


These two queries are my proof of concept so I'm only limiting the first query 
to 5 results.  In reality I have thousands of products per category so I prefer 
not to limit the first query.  I don't have many categories so the results of 
the second query are quite small.

Is there anyway to combine these 2 queries into 1?

Thanks,
Mason

http://www.retailretreat.com
  



No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.516 / Virus Database: 269.19.16/1251 - Release Date: 1/30/2008 9:29 AM
  


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Can these two queries be combined into one?

[webtek2001-mysql]

Hi,

  I'm currently using 2 queries to obtain some data and would like to know if I 
can combine these two queries into one query.

I have a database with 3 tables:

1.  A category table
2.  A product table
3.  A normalized "catproduct" table that is used to determine which product is 
in which category.  A product may be in multiple categories.

I have a website where when a person navigates to a category it shows all the 
products.  I would like to create a "related categories" list by finding all 
the categories the products in the current category belong to.

Right now I use 2 SQL statements:

1. The first query gets all the products in the current category.

SELECT product_table.prod_id FROM
product_table
 LEFT JOIN catproduct_table ON (catproduct_table.product_id = 
product_table.product_id)
 WHERE (catproduct_table.category_id = '1') LIMIT 5

2. The second query gets all the categories that these products belong to:

SELECT category_table.category FROM category_table
   LEFT JOIN catproduct_table ON (catproduct_table.category_id = 
category_table.category_id)
   LEFT JOIN product_table ON (product_table.product_id = 
catproduct_table.product_id)
   WHERE product_table.prod_id IN ('1000,1001,1002,1003,1004')


These two queries are my proof of concept so I'm only limiting the first query 
to 5 results.  In reality I have thousands of products per category so I prefer 
not to limit the first query.  I don't have many categories so the results of 
the second query are quite small.

Is there anyway to combine these 2 queries into 1?

Thanks,
Mason

http://www.retailretreat.com

Spontaneous corrupted tables on MyISAM with 5.0.x release

[Markus Fischer]

Hi,

our scenario (was):
server1: 5.0.32-Debian_7etch1-log
server2: 5.0.32-Debian_7etch1-log

Hardware-wise (attention, Vmware, see below) they're equal: ~1GHz CPU at at 
minimum 2GB ram.


Suddenly about 4 to 6 weeks ago, server1 started getting serious problems with 
 spontaneous corrupted tables, so that in the end our hoster upgrade server1 
to 5.0.51-2-log what we're currently running.


Unfortunately, things haven't changed a bit. server2 is running different 
databases/applications, some tables are replicated from server1 to server2, 
some from server2 to server1. However, server2, as far as I can remember, 
never had those spontaneous table problems and still hasn't (yet).


Both servers are running on VMware (I think ESX is the product our Hoster is 
using) and the MySQL data files are on a NFS exported share. Those 
share/fileserver is reported to be some kind of Ueber-Beast-Killer-Maschine. 
All servers running in Vmware don't contain virtual Vmware hard discs but have 
NFS mounted root and data, etc. partitions.


We're very often, daily!, getting spontaneous corrupted tables on the server 
with the version which gets us really in trouble.


The only pattern so far:
1) it only affects MyISAM tables
2) once converted to InnoDB, no troubles (so far)

Unfortunately there are tables we weren't able to convert because they contain 
an fulltext index. Interestingly, once we converted all (except mentioned) 
tables in a database, the remaining MyISAM tables won't crash anymore (so far).


Besides this, there's no distinct pattern, because we get crashes
* on high and low traffic tables
* on intensive and non-intensive write tables
* on big (range between 100 and 150MB) and small tables

It often occurred that a simple "REPAIR table" statement didn't always helped. 
 Sometimes "EXTENDED" was required, sometimes an offline repair with the 
myisamchk had to be done.


The tables didn't crash because the whole MySQL server went down, this was 
while the server was running.


We've been running the applications using the databases for years. The were 
two major changes during the last year:

* our moved the MySQL server from a physical machine to Vmware
* we upgraded (better, let our Hoster upgrade) from some MySQL4 version to the 
mentioned versions above.


We don't use any fancy stuff, more or less simple SELECT, DELETE, UPDATE, 
INSERT. No Subselects, no triggers, no stored procedures, no key constraints, 
etc, no locking, no REPLACE.


Our Hoster refers to the following MySQL bugs
http://bugs.mysql.com/bug.php?id=28154
http://bugs.mysql.com/bug.php?id=33596

However specially for 33596 I don't see any related information because this 
issue described there never applied.


For 28154: Unfortunately I don't remember seeing this 127 error, however if it 
ever occurred, then only a long time ago. Recent errors are just corrupted 
tables once we start seeing problems in our web application. Our "thread cache 
size" is 128. Mentioned in #28154 is http://bugs.mysql.com/bug.php?id=29838 . 
I think that's the reason why our Hoster upgraded to 5.0.51.



For what it matters, I just can't believe that MyISAM is to blame completely 
at fault. If it had that problems I just couldn't believe this was in a stable 
product. I'm really curious to just to fix the problems but also find out what 
the cause really is.


I would be glad for any help on this matter and I'm happy to provide any 
information you want.


thanks,
- Markus

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Navicat MySQL GUI for Linux version 8.0.23 is released.

[Barry Newton]

Hmm.  Speaking of Navicat, does anybody out there have an easy way to 
scrub the control coding from scripts developed under Navicat?  I expect 
that they're there mostly for coloration on displays, but it's kind of 
obnoxious when you want to do anything else with them.


--

Barry


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ON vs. WHERE

[Kévin Labécot]

-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 30, 2008 11:50 AM
To: Jerry Schwartz; mysql@lists.mysql.com
Subject: Re: ON vs. WHERE

At 11:46 AM -0500 1/30/08, Jerry Schwartz wrote:


The documentation says (I think) that you should not use an ON join
condition when you can use a WHERE, but I'm pretty sure someone  
from

MySQL.com posted a message suggesting the opposite.


The manual says this:

"The ON conditional is any conditional expression of the form that
can be used in a WHERE clause. Generally, you should use the ON
clause for conditions that specify how to join tables, and the WHERE
clause to restrict which rows you want in the result set."

[JS] Thanks for that. I don't know where I read the opposite.  
Perhaps I was

passing a brain-stone.


Or maybe in a comment ? :p

--
Kévin Labécot
www.cv.labecot.fr


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

GROUP question

[Jerry Schwartz]

I have two tables that (simplified) look like this:

customers
=
customer_id [integer, autoincrement]
email [varchar]

memos
=
memo_id [integer, autoincrement]
customer_id [integer]
memo_text [varchar]

There are some duplicate email addresses in the "customers" table. A
customer might have any number of memos.

What I want to do is locate all of the duplicate email addresses and their
associated memos (so that we can try to decide which to keep).

If I don't try to handle the memos, I can easily get a list of duplicated
emails:

SELECT customers.email, COUNT(*) AS n
FROM customers
GROUP BY customers.email
HAVING n > 1;

That works fine: I get a list of all of the duplicated email addresses, and
the number of each.

The problem starts when I try to fetch the memos. I want one email address,
two or more customer_ids (so I can tell them apart), and all of the memos
for each customer_id (preferably using GROUP_CONCAT(). In other words, this
is what I want:

[EMAIL PROTECTED]  fred_id_1  fred_memos_1
   fred_id_2  fred_memos_2
   fred_id_3  fred_memos_3
[EMAIL PROTECTED]  john_id_1  john_memos_1
   john_id_2  john_memos_2

No matter how I try, I always wind up with something like this:

[EMAIL PROTECTED]  fred_id_1  fred_memos_1
[EMAIL PROTECTED]  john_id_1  john_memos_1

I can't seem to avoid collapsing duplicate email records into one; that is,
I only see the the first occurrence of the email (which is what I want), the
first customer_id for that email address, and the first set of memos for
that email address.

If I don't group, then I can get every record; but then I don't see how I
can pick off only the email addresses that are in the customers table more
than once.

I might be missing the obvious here. I've fiddled with sub-queries, but
didn't really get anywhere.



Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ON vs. WHERE

[Les Fletcher]

Are specifying the joins more optimal than using a multiple from and a 
where clause when doing an inner join?


SELECT tbl1.*,tb2.* FROM tbl1, tbl2 WHERE tbl1.col1=tbl2.col2;

vs.

SELECT tbl1.*,tb2.* FROM tbl1 JOIN tbl2 ON tbl1.col1=tbl2.col2;

vs.

SELECT tbl1.*,tb2.* FROM tbl2 JOIN tbl1 ON tbl1.col1=tbl2.col2;

Does switching up the on from and the join change the driving table?

Les


Jerry Schwartz wrote:

-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 30, 2008 11:50 AM
To: Jerry Schwartz; mysql@lists.mysql.com
Subject: Re: ON vs. WHERE

At 11:46 AM -0500 1/30/08, Jerry Schwartz wrote:


The documentation says (I think) that you should not use an ON join
condition when you can use a WHERE, but I'm pretty sure someone from
MySQL.com posted a message suggesting the opposite.
  

The manual says this:

"The ON conditional is any conditional expression of the form that
can be used in a WHERE clause. Generally, you should use the ON
clause for conditions that specify how to join tables, and the WHERE
clause to restrict which rows you want in the result set."


[JS] Thanks for that. I don't know where I read the opposite. Perhaps I was
passing a brain-stone.




  


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transfer huge mysql db

[David Schneider-Joseph]

On Jan 30, 2008, at 10:44 AM, Jerry Schwartz wrote:


mysqldump -A > file.dump
tar -jcf file.dump
rsync


[JS] You could also just pipe the output of mysqldump through gzip.
tar buys
you nothing, since it is a single file.


-j is the bzip2 compression option. :)

[JS] Yes, but tar is just extra baggage.


Good point, duh.

David


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: different results between FULLTEXT search and LIKE search

[Lamp Lists]

--- Lamp Lists <[EMAIL PROTECTED]> wrote:

> 
> --- Lamp Lists <[EMAIL PROTECTED]> wrote:
> 
> > hi,
> > I created table "tasks"
> > create table tasks(
> > task_id, int(4) not null primary key, 
> > task text not null, 
> > resolution text not null, 
> > fulltext (task, resolution)
> > )engine=myisam
> > 
> > when I run
> > 
> > seect * from tasks match(task,resolution)
> > against('"certain service"' in boolean mode)
> > 
> > I would get one record and the phrase is in
> > resolution
> > column.
> > 
> > though, when I serach using LIKE
> > select * from tasks where task like '%certain
> > service%' or resolution like '%certain service%'
> > I would get 2 records. one record is the same as
> the
> > one above and the 2nd has the phrase in task
> column.
> > means there are 2 records, but fulltext shows me
> > only
> > one.
> > 
> > what I'm doing wrong?
> > 
> > thanks.
> > 
> > -ll
> > 
> 
> 
> just made 2nd test and got different reault too:
> 
> select * from tasks match(task,resolution)
> against('+certain +service' in boolean mode) 
> result: 232 records
> 
> select * from tasks where (task like '%certain%' and
> task like '%service%') or (resolution like
> '%certain%'
> and resolution like '%service%')
> result: 7 records
> 
> ?!?!!??
> 
> -ll
> 
> 
> 
> 
>  
>

> Be a better friend, newshound, and 
> know-it-all with Yahoo! Mobile.  Try it now. 
>
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
> 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


even worse:
select count(*) from tasks match(task,resolution)
against('certain') 
result: 0
 
select count(*) from tasks where task like '%certain%'
or resolution like '%certain%';
result: 173

?

-ll


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Strange result of Group by on real values

[Joris Kinable]

Evening,

Could someone explain the result of the Query 2? Query 2 does a GROUP
BY avgOctets, but still there are duplicate avgOctets in my result
set! Boxplot(octets,"AVG",0) is a UDF which returns REAL values. In
this case, the real values have no decimals.
The only reasonable explanation I could think of is that the REAL
value returned by boxplot(...) is an estimation and therefore should
be casted to an INTEGER before comparison by GROUP BY is possible?


Inner Query: SELECT boxplot(octets,"AVG",0) AS avgOctets FROM
joris_filter GROUP BY ipv4_src, ipv4_dst

Result:

avgOctets
46
46
46
46
46
46
46
40
46
46
46
144
48
48



Query 2: (With "inner query" as inner query):

SELECT avgOctets, COUNT(*) AS frequency FROM
  (
SELECT boxplot(octets,"AVG",0) AS avgOctets FROM joris_filter
GROUP BY ipv4_src, ipv4_dst
) avarages
GROUP BY avgOctets ORDER BY avgOctets;

Result:

avgOctetsfrequency
40   350026
41   1
41   1
41   6
41   2
41   3
41   1
42   1
...

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: different results between FULLTEXT search and LIKE search

[Lamp Lists]

--- Lamp Lists <[EMAIL PROTECTED]> wrote:

> hi,
> I created table "tasks"
> create table tasks(
> task_id, int(4) not null primary key, 
> task text not null, 
> resolution text not null, 
> fulltext (task, resolution)
> )engine=myisam
> 
> when I run
> 
> seect * from tasks match(task,resolution)
> against('"certain service"' in boolean mode)
> 
> I would get one record and the phrase is in
> resolution
> column.
> 
> though, when I serach using LIKE
> select * from tasks where task like '%certain
> service%' or resolution like '%certain service%'
> I would get 2 records. one record is the same as the
> one above and the 2nd has the phrase in task column.
> means there are 2 records, but fulltext shows me
> only
> one.
> 
> what I'm doing wrong?
> 
> thanks.
> 
> -ll
> 


just made 2nd test and got different reault too:

select * from tasks match(task,resolution)
against('+certain +service' in boolean mode) 
result: 232 records

select * from tasks where (task like '%certain%' and
task like '%service%') or (resolution like '%certain%'
and resolution like '%service%')
result: 7 records

?!?!!??

-ll




  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: ON vs. WHERE

[Jerry Schwartz]

> -Original Message-
> From: Paul DuBois [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, January 30, 2008 11:50 AM
> To: Jerry Schwartz; mysql@lists.mysql.com
> Subject: Re: ON vs. WHERE
>
> At 11:46 AM -0500 1/30/08, Jerry Schwartz wrote:
> >The documentation says (I think) that you should not use an ON join
> >condition when you can use a WHERE, but I'm pretty sure someone from
> >MySQL.com posted a message suggesting the opposite.
>
> The manual says this:
>
> "The ON conditional is any conditional expression of the form that
> can be used in a WHERE clause. Generally, you should use the ON
> clause for conditions that specify how to join tables, and the WHERE
> clause to restrict which rows you want in the result set."
[JS] Thanks for that. I don't know where I read the opposite. Perhaps I was
passing a brain-stone.




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

different results between FULLTEXT search and LIKE search

[Lamp Lists]

hi,
I created table "tasks"
create table tasks(
task_id, int(4) not null primary key, 
task text not null, 
resolution text not null, 
fulltext (task, resolution)
)engine=myisam

when I run

seect * from tasks match(task,resolution)
against('"certain service"' in boolean mode)

I would get one record and the phrase is in resolution
column.

though, when I serach using LIKE
select * from tasks where task like '%certain
service%' or resolution like '%certain service%'
I would get 2 records. one record is the same as the
one above and the 2nd has the phrase in task column.
means there are 2 records, but fulltext shows me only
one.

what I'm doing wrong?

thanks.

-ll



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ON vs. WHERE

[Paul DuBois]

At 11:46 AM -0500 1/30/08, Jerry Schwartz wrote:

The documentation says (I think) that you should not use an ON join
condition when you can use a WHERE, but I'm pretty sure someone from
MySQL.com posted a message suggesting the opposite.


The manual says this:

"The ON conditional is any conditional expression of the form that 
can be used in a WHERE clause. Generally, you should use the ON 
clause for conditions that specify how to join tables, and the WHERE 
clause to restrict which rows you want in the result set."


See: http://dev.mysql.com/doc/refman/5.0/en/join.html

--
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]

ON vs. WHERE

[Jerry Schwartz]

The documentation says (I think) that you should not use an ON join
condition when you can use a WHERE, but I'm pretty sure someone from
MySQL.com posted a message suggesting the opposite.

 

Aside from LEFT JOINs, which require an ON, does anyone have any opinions or
experience?

 

Regards,

 

Jerry Schwartz

The Infoshop by Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

 

860.674.8796 / FAX: 860.674.8341

 

  www.the-infoshop.com

  www.giiexpress.com

www.etudes-marche.com

Looking for a more efficient way to achieve the same result.

[Richard]

Hello, I'm in the process of programming a customer area with a list of 
subscriptions :


Reference | Title | Type | Date of first subscription | Expires

Each item in this list will have a link to it's details with will show a 
list like this :


Subscribed on : date of first subscription
Renewed on : date of first renewal
Renewed on : date of second renewal
Renewed on : date of third renewal
Expires on : date when expires.

At the moment I have got three mysql tables :
--
1) Products

Reference | Title | Type

2) Subscriptions

number(autoincrement) | reference | date_begin

3) subscriptions details

number(autoincrement) | subscription_number | length(number of months)
--

To get the first table I would :

List subscriptions
For each subscription get list of "lengths" which I would add together 
and then calculate expire date by :

Total "lengths" + date_begin

Which gives me the expire date.

However all this seems alot of queries and resources for such a small 
list and as I have not started the programming yet and have not created 
the tables either, I thought I might ask your advice to see if you think 
this is the best way, or if you could think of a better way of achieving 
the same result. Maybe there is a way to get the expire date with mysql, 
or maybe I should rethink my tables?


I hope that everything is clear, and thanks in advance :)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Decimal - Maximum is 30

[Warren Young]

Eli Shemer wrote:


Is there any possible way to increase this limit ?


I'm curious to know what it is you're doing where you need accuracy 
better than one part in a nonillion.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: transfer huge mysql db

[Jerry Schwartz]

> >> mysqldump -A > file.dump
> >> tar -jcf file.dump
> >> rsync
> >
> > [JS] You could also just pipe the output of mysqldump through gzip.
> > tar buys
> > you nothing, since it is a single file.
>
> -j is the bzip2 compression option. :)
[JS] Yes, but tar is just extra baggage.


Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: feature request: statement SELECT...(INSERT|UPDATE) :)

[Martijn Tonies]

> On Jan 30, 2008 12:50 PM, Dmitry E. Oboukhov wrote:
> > Is it possible to add to the syntax of the INSERT operator appoximately
> > in such way:
> > SELECT list INSERT [IGNORE] INTO ... - an added one.
> > SELECT list UPDATE  - an added one.
> 
> > PS: I understand that adding the changes into a language is a very
> > serious question that needs a great discussion but one never can tell,
> > may be mysql developers will be interested in my proposal ;)
> 
> I think it would be a good idea to look at the way other databases can
> do this and see if there is some common syntax that could also be
> implemented by MySQL. For instance PostgreSQL implements a INSERT /
> UPDATE / DELETE ... RETURNING ... statement. ISTM that is exactly the
> functionality you want, but with a different syntax. If there are no
> other (better) competing implementation syntaxes (I don't know any,
> but maybe other list members do), I would like MySQL to adopt the
> PostgreSQL example.

Firebird uses this syntax as well, I believe it's the SQL standard syntax
for this feature.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: feature request: statement SELECT...(INSERT|UPDATE) :)

[Jochem van Dieten]

On Jan 30, 2008 12:50 PM, Dmitry E. Oboukhov wrote:
> Is it possible to add to the syntax of the INSERT operator appoximately
> in such way:
> SELECT list INSERT [IGNORE] INTO ... - an added one.
> SELECT list UPDATE  - an added one.

> PS: I understand that adding the changes into a language is a very
> serious question that needs a great discussion but one never can tell,
> may be mysql developers will be interested in my proposal ;)

I think it would be a good idea to look at the way other databases can
do this and see if there is some common syntax that could also be
implemented by MySQL. For instance PostgreSQL implements a INSERT /
UPDATE / DELETE ... RETURNING ... statement. ISTM that is exactly the
functionality you want, but with a different syntax. If there are no
other (better) competing implementation syntaxes (I don't know any,
but maybe other list members do), I would like MySQL to adopt the
PostgreSQL example.

Jochem

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

feature request: statement SELECT...(INSERT|UPDATE) :)

[Dmitry E. Oboukhov]

Hi!

I use mysql on amateurish level mainly for personal needs and so please
forgive me if this feature request is impossible to fulfil or if it is
sent to the wrong mail-list, or if this functionality has been already
realized in other ways :)

Note: I've read the discription of C-function mysql_insert_id() and the
discription of SELECT LAST_INSERT_ID() in new versions, but as far as
I've understood it concerns only AUTOINCREMENT columns, and very often it
isn't enough (some columns may be filled by mysql functions (for example
data/time/mathematical functions etc) and very often the resulting
values are needed at once for report representation or for using in
the next statements INSERT)

So: The INSERT(UPDATE) statement returns the quantity of
strings inserted(updated) into a table.

(*) However as far as I understand at the stage of fulfilling this
operator mysql operates with these very strings.

Is it possible to add to the syntax of the INSERT operator appoximately
in such way:

INSERT [IGNORE] INTO ... - a general syntax

SELECT list INSERT [IGNORE] INTO ... - an added one.

UPDATE ... - a general syntax

SELECT list UPDATE  - an added one.

Where can it be needed?

Example 1
~

We have a data base:

table (id, name, value1, value2, value3)

Now let's imagine CGI-script which makes an insert(update) and shows
the results to a user.

In the current case we need to:
1. INSERT INTO table (name, value1, value2, value3) VALUES
(?,?,?,?), (?,?,?,?),(?,?,?,?);
2. SELECT * FROM table WHERE ...

while the server has all the data needed for the step 2 already on the
step 1 :)

When selecting a few inserted strings at once we have either a complex
expression in the statement WHERE, or we need to split
the INSERT call into few single ones and replace 1 and 2 by the sequence
INSERT - SELECT - INSERT - SELECT.

It would be excellent to write:
SELECT * INSERT table (name, value1, value2, value3)
VALUES (?,?,?,?), (?,?,?,?), (?,?,?,?);
and, having on the entry the data for the insert on the exit, to get the
result of insert at once (and to display it if necessary)

similar UPDATE statement:

SELECT column1, column2
UPDATE table SET column3=value, column4=value
WHERE ...;

Example 2:
~~

For example we have a data base:

table1 (id, name) -- id - AUTOINCREMENT
table2 (id, table1_id, value1)
table3 (id, table1_id, value2)

Now let's imagine CGI-script working with such data base.
In case if it makes a data insert into this data base we need to:
1. INSERT [IGNORE] INTO table1 SET name=?

2. SELECT id FROM table1 WHERE name=?
 or SELECT LAST_INSERT_ID() as id;

3. INSERT INTO table2 SET value1=?, table1_id=? -- value, id_fromt1
   INSERT INTO table3 SET value2=?, table1_id=? -- value, id_fromt1


In the current situation all the data necessary for fulfillment of the
point 3 are actually available on mysql server when fulfilling point 1
however unfortunately it is impossible to extract them by making an
additional request :(

But if we had an additional syntax then we could unite points 1 and 2:

SELECT id INSERT INTO table1 SET name=?

And in some cases even points 1,2,3 altogether:

We insert in all the tables at once:
INSERT INTO table3 (table1_id, value2)
SELECT table1_id, ? INSERT INTO table2 (table1_id, value1)
SELECT id, ? INSERT INTO table1 SET name=?;
-- value2, value1, name

That is by adding the mirror statement SELECT...INSERT to the existing
statement INSERT...SELECT
we would gain a very interesting functionality, allowing sometimes to
get rid of using transactions and (or) to refuse from storage procedures
and to replace the mass colls by the single ones etc.

PS: I understand that adding the changes into a language is a very
serious question that needs a great discussion but one never can tell,
may be mysql developers will be interested in my proposal ;)

I think that taking into account (*) it will be relatively simply to
realise such an operator (even not embedded for a start). Or am I not
right?


signature.asc
Description: Digital signature

Navicat MySQL GUI for Linux version 8.0.23 is released.

[samnav]

By Premiumsoft. Navicat MySQL GUI Manager is a powerful yet easy to use MySQL
client provides extensive functionality for managing and developing MySQL.
It features an intuitive interface and provides a set of useful tool to
import/ export, backup/ restore data, Synchronize database and connect to
remote MySQL server, etc. Navicat MySQL GUI ver. 8 is the latest iteration
of the database management tool that offers more than 100 improvements and
some new powerful features. Major new features of ver. 8 at a glance: To
check out details, please see a release notes at:
http://www.navicat.com/release_notes_linux.html.

Major new features:
1.Code Completion - Speedup your sql writing.
2.Form View - Input record via form view.
3.Virtual Grouping - Provides better categorization for objects.
4.Event Scheduler - New MySQL feature as of MySQL 5.1.

Other major features: 

Visual Query Builder 
SSH/ HTTP Tunnel for remote database connection 
Database transfer from server to server 
Data and Structure Synchronization 
Data backup and restore 
Import/ Export of most popular data formats to and from MySQL 
Report Builder 
Tasks Scheduling and Easy-to-use wizards tool. 
 
Further Enhancements:

Compatible with any MySQL server version up to 5.1.x.
Incremental Search, Hex Editor and more supports in Table View.
Usability enhancement in Table Design.
Many other improvements on features and user-friendliness.

To find out more details about the product, please visit at:
http://www.navicat.com/linux_detail.html


-- 
View this message in context: 
http://www.nabble.com/Navicat-MySQL-GUI-for-Linux-version-8.0.23-is-released.-tp15177231p15177231.html
Sent from the MySQL - General mailing list archive at Nabble.com.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]