Re: Relational query question

[Divesh Kamra]

It better to LEFT join rather then NOT IN




On Wed, Sep 30, 2015 at 6:00 PM, Mogens Melander 
wrote:

> Maybe not the most optimal, but (probably) the most simple:
>
> SELECT * FROM fruit
> where id not in (select fruit from purchase
> where customer=1);
>
> 1, 'Apples'
> 3, 'Oranges'
>
>
> On 2015-09-30 00:01, Richard Reina wrote:
>
>> If I have three simple tables:
>>
>> mysql> select * from customer;
>> +++
>> | ID | NAME   |
>> +++
>> |  1 | Joey   |
>> |  2 | Mike   |
>> |  3 | Kellie |
>> +++
>> 3 rows in set (0.00 sec)
>>
>> mysql> select * from fruit;
>> ++-+
>> | ID | NAME|
>> ++-+
>> |  1 | Apples  |
>> |  2 | Grapes  |
>> |  3 | Oranges |
>> |  4 | Kiwis   |
>> ++-+
>> 4 rows in set (0.00 sec)
>>
>> mysql> select * from purchases;
>> ++-+--+
>> | ID | CUST_ID | FRUIT_ID |
>> ++-+--+
>> |  2 |  3 |   2   |
>> |  3 |  1 |   4   |
>> |  4 |  1 |   2   |
>> |  5 |  2 |   1   |
>> ++-+--+
>>
>> I am having trouble understanding a relational query. How can I select
>> those fruits that Joey has not purchased?
>>
>
> --
> Mogens
> +66 8701 33224
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: Relational query question

[Mogens Melander]

Maybe not the most optimal, but (probably) the most simple:

SELECT * FROM fruit
where id not in (select fruit from purchase
where customer=1);

1, 'Apples'
3, 'Oranges'

On 2015-09-30 00:01, Richard Reina wrote:

If I have three simple tables:

mysql> select * from customer;
+++
| ID | NAME   |
+++
|  1 | Joey   |
|  2 | Mike   |
|  3 | Kellie |
+++
3 rows in set (0.00 sec)

mysql> select * from fruit;
++-+
| ID | NAME|
++-+
|  1 | Apples  |
|  2 | Grapes  |
|  3 | Oranges |
|  4 | Kiwis   |
++-+
4 rows in set (0.00 sec)

mysql> select * from purchases;
++-+--+
| ID | CUST_ID | FRUIT_ID |
++-+--+
|  2 |  3 |   2   |
|  3 |  1 |   4   |
|  4 |  1 |   2   |
|  5 |  2 |   1   |
++-+--+

I am having trouble understanding a relational query. How can I select
those fruits that Joey has not purchased?


--
Mogens
+66 8701 33224


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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

Relational query question

[Richard Reina]

If I have three simple tables:

mysql> select * from customer;
+++
| ID | NAME   |
+++
|  1 | Joey   |
|  2 | Mike   |
|  3 | Kellie |
+++
3 rows in set (0.00 sec)

mysql> select * from fruit;
++-+
| ID | NAME|
++-+
|  1 | Apples  |
|  2 | Grapes  |
|  3 | Oranges |
|  4 | Kiwis   |
++-+
4 rows in set (0.00 sec)

mysql> select * from purchases;
++-+--+
| ID | CUST_ID | FRUIT_ID |
++-+--+
|  2 |  3 |   2   |
|  3 |  1 |   4   |
|  4 |  1 |   2   |
|  5 |  2 |   1   |
++-+--+

I am having trouble understanding a relational query. How can I select
those fruits that Joey has not purchased?

Re: Relational query question

[shawn l.green]

On 9/29/2015 1:27 PM, Ron Piggott wrote:



On 29/09/15 13:01, Richard Reina wrote:

If I have three simple tables:

mysql> select * from customer;
+++
| ID | NAME   |
+++
|  1 | Joey   |
|  2 | Mike   |
|  3 | Kellie |
+++
3 rows in set (0.00 sec)

mysql> select * from fruit;
++-+
| ID | NAME|
++-+
|  1 | Apples  |
|  2 | Grapes  |
|  3 | Oranges |
|  4 | Kiwis   |
++-+
4 rows in set (0.00 sec)

mysql> select * from purchases;
++-+--+
| ID | CUST_ID | FRUIT_ID |
++-+--+
|  2 |  3 |   2   |
|  3 |  1 |   4   |
|  4 |  1 |   2   |
|  5 |  2 |   1   |
++-+--+

I am having trouble understanding a relational query. How can I select
those fruits that Joey has not purchased?



I think you are going to want to use a "LEFT JOIN" using "purchases" as
the common table to join with a WHERE purchases.FRUIT_ID IS NULL





SELECT f.name
FROM fruit f
LEFT JOIN purchases p
  on f.id = p.fruit_id
INNER JOIN customer c
  on p.cust_id = c.id
  and c.name='Joey'
WHERE c.id IS NULL;

You have to make that "and...Joey" part of the LEFT JOIN to be selective 
for just "what Joey bought". it is the WHERE c.id IS NULL part that 
filters out and returns only the stuff that Joey did not buy.


If you put the c.name='Joey' term in the WHERE clause then you force a 
value to exist at that point of the query turning your LEFT JOIN into 
INNER JOIN (which would only show you what Joey did buy).


If you put WHERE c.name !='Joey' into the WHERE clause, then you would 
get the list of fruits that anyone else but Joey had purchased.


To see how this works and to understand the process a little better, 
expose all 3 layers of the problem as a big matrix (you'll get all 48 
row combinations).


SELECT f.id as f_id, f.name, p.id as p_id, p.cust_id, p.fruit_id, c.id 
as c_id,  c.name

FROM fruit f
LEFT JOIN purchases p
  on f.id = p.fruit_id
LEFT JOIN customer c
  on p.cust_id = c.id


From here, look at when the columns are NULL and when they aren't. Then 
experiment with different conditions. You are almost there. This should 
push you right to the top of the learning curve.


--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


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

Re: Relational query question

[Ron Piggott]

On 29/09/15 13:01, Richard Reina wrote:

If I have three simple tables:

mysql> select * from customer;
+++
| ID | NAME   |
+++
|  1 | Joey   |
|  2 | Mike   |
|  3 | Kellie |
+++
3 rows in set (0.00 sec)

mysql> select * from fruit;
++-+
| ID | NAME|
++-+
|  1 | Apples  |
|  2 | Grapes  |
|  3 | Oranges |
|  4 | Kiwis   |
++-+
4 rows in set (0.00 sec)

mysql> select * from purchases;
++-+--+
| ID | CUST_ID | FRUIT_ID |
++-+--+
|  2 |  3 |   2   |
|  3 |  1 |   4   |
|  4 |  1 |   2   |
|  5 |  2 |   1   |
++-+--+

I am having trouble understanding a relational query. How can I select
those fruits that Joey has not purchased?



I think you are going to want to use a "LEFT JOIN" using "purchases" as 
the common table to join with a WHERE purchases.FRUIT_ID IS NULL




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

Re: Another query question...

[hsv]

 2013/11/08 17:35 -0800, Jan Steinman 
Okay, I think I found it:
http://bugs.mysql.com/bug.php?id=47713

I added a comment with a link to a page I set up to show the behaviour on my 
system.
http://www.ecoreality.org/wiki/WITH_ROLLUP_problem

It was submitted in 2009, severity Critical, triaged Serious, and still not 
fixed! 

Yea, and the bug to which I referred is also evident in the same report, where 
SumQuantity is 78, and not all the rest is NULL. 'Twouldn't surprise me if the 
bugs are akin.


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

Re: Another query question...

[Jan Steinman]

 From: h...@tbbs.net
 
 2013/11/04 09:32 -0800, Jan Steinman
 I noticed that I have similar queries that work as expected. The difference 
 appears to be that every query that is broken uses  WITH ROLLUP, and 
 removing this makes them behave as expected.
 
 Is this a known bug? Should I submit it as such?
 
 There is a bug that I about a half year ago reported, 
 http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with WITH 
 ROLLUP is not always NULL, but is instead the foregoing string in the same 
 field.

Okay, I think I found it:
http://bugs.mysql.com/bug.php?id=47713

I added a comment with a link to a page I set up to show the behaviour on my 
system.
http://www.ecoreality.org/wiki/WITH_ROLLUP_problem

It was submitted in 2009, severity Critical, triaged Serious, and still not 
fixed!

 The raw milk movement provides a real solution to the problem of 
food-borne illness -- because raw milk consumers make sure their milk comes 
from small, pasture-based farms and healthy animals unlikely to harbor 
pathogens and unlikely to contribute to water pollution, and because raw milk 
builds immunity to disease-causing organisms that are simply a natural part of 
the world in which we live. -- Ron Schmid
 Jan Steinman, EcoReality Co-op 


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

Re: Another query question...

[Jan Steinman]

The plot thickens...

I noticed that I have similar queries that work as expected. The difference 
appears to be that every query that is broken uses  WITH ROLLUP, and removing 
this makes them behave as expected.

Is this a known bug? Should I submit it as such?

If someone would be so kind as to point me to the bug system, I'll crawl around 
in there to see if it's a known problem.

Jan

Begin forwarded message:

 From: Jan Steinman j...@bytesmiths.com
 Date: 3 November 2013 18:35:47 PST
 
 MySQL 5.0.92-log
 
 I'm trying to form a clickable link using CONCAT, but the link as displayed 
 points to the NEXT row's URL, not the one from the same row as the other data 
 displayed!
 
 Is there something I don't understand about this?
 
 Below is the query. {{{1}}} is replaced by a year, like 2013.
 
 The second column is the problem one. When the table is displayed, the link 
 in the `Product` field points to the NEXT SEQUENTIAL product row! In other 
 words, if you click on the link for garlic, you'll get the page for 
 gherkins.
 
 Live example is at: http://www.EcoReality.org/wiki/2013_harvest
 
 If you hover over the link in the `Product` column, you can clearly see that 
 the page at the link is not the same as that in the `ID` column, but is in 
 fact the same `ID` as the next sequential row. I am so confused.
 
 SELECT
   harvest.product AS ID,
   CONCAT('a href=http://www.EcoReality.org/wiki/Product/', s_product.ID, 
 '', COALESCE(s_product.name, 'TOTAL:'), '/a') AS `Product`,
   FORMAT(sum(harvest.quantity), 3) AS `a 
 href=http://www.EcoReality.org/wiki/Harvest;Harvest/a`,
   harvest.units AS Units,
  CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value,
   prices.market_type AS `R-W`,
   COUNT(*) AS Harvests,
   DATE(MIN(harvest.date)) AS Begin,
   DATE(MAX(harvest.date)) AS End
 FROM
   s_product_harvest harvest
 INNER JOIN
   s_product on s_product.ID = harvest.product AND
   s_product.units = harvest.units
 LEFT OUTER JOIN
   s_product_market_prices prices ON prices.product_ID = harvest.product AND
   prices.units = harvest.units AND
   year(prices.price_date) = year(harvest.date)
 WHERE
   year(harvest.date) = {{{1}}}
 GROUP BY
   s_product.name WITH ROLLUP
 
  Some days I wonder if it might not be better to culturally engineer 
 humans to enjoy small scale garden farming than to genetically engineer weeds 
 to save large scale agribusiness. -- Gene Logsdon
  Jan Steinman, EcoReality Co-op 
 

 The competition for grain between the wealthy car drivers of the world and 
the poorest people who are trying to survive is a moral issue that we should 
not ignore. The continued increase in biofuels production will result in a 
continued decrease in food availability, which we could someday consider to be 
a crime against humanity. -- Pat Murphy
 Jan Steinman, EcoReality Co-op 


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

Another query question...

[Jan Steinman]

MySQL 5.0.92-log

I'm trying to form a clickable link using CONCAT, but the link as displayed 
points to the NEXT row's URL, not the one from the same row as the other data 
displayed!

Is there something I don't understand about this?

Below is the query. {{{1}}} is replaced by a year, like 2013.

The second column is the problem one. When the table is displayed, the link in 
the `Product` field points to the NEXT SEQUENTIAL product row! In other words, 
if you click on the link for garlic, you'll get the page for gherkins.

Live example is at: http://www.EcoReality.org/wiki/2013_harvest

If you hover over the link in the `Product` column, you can clearly see that 
the page at the link is not the same as that in the `ID` column, but is in fact 
the same `ID` as the next sequential row. I am so confused.

SELECT
   harvest.product AS ID,
   CONCAT('a href=http://www.EcoReality.org/wiki/Product/', s_product.ID, 
'', COALESCE(s_product.name, 'TOTAL:'), '/a') AS `Product`,
   FORMAT(sum(harvest.quantity), 3) AS `a 
href=http://www.EcoReality.org/wiki/Harvest;Harvest/a`,
   harvest.units AS Units,
  CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value,
   prices.market_type AS `R-W`,
   COUNT(*) AS Harvests,
   DATE(MIN(harvest.date)) AS Begin,
   DATE(MAX(harvest.date)) AS End
FROM
   s_product_harvest harvest
 INNER JOIN
   s_product on s_product.ID = harvest.product AND
   s_product.units = harvest.units
 LEFT OUTER JOIN
   s_product_market_prices prices ON prices.product_ID = harvest.product AND
   prices.units = harvest.units AND
   year(prices.price_date) = year(harvest.date)
WHERE
   year(harvest.date) = {{{1}}}
GROUP BY
   s_product.name WITH ROLLUP

 Some days I wonder if it might not be better to culturally engineer humans 
to enjoy small scale garden farming than to genetically engineer weeds to save 
large scale agribusiness. -- Gene Logsdon
 Jan Steinman, EcoReality Co-op 


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

Re: Another query question...

[hsv]

 2013/11/04 09:32 -0800, Jan Steinman 
I noticed that I have similar queries that work as expected. The difference 
appears to be that every query that is broken uses  WITH ROLLUP, and removing 
this makes them behave as expected.

Is this a known bug? Should I submit it as such?

If someone would be so kind as to point me to the bug system, I'll crawl around 
in there to see if it's a known problem. 

There is a bug that I about a half year ago reported, 
http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with WITH 
ROLLUP is not always NULL, but is instead the foregoing string in the same 
field. I suspect that other bugs with missing NULL found by searching for 
ROLLUP are the same problem. (note link titled Affects Me!)

Another, one year ago reported, bug of mine was handled in 5.7.2, but this one 
not.


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

Re: Another query question...

[Reindl Harald]

Am 04.11.2013 22:55, schrieb h...@tbbs.net:
 2013/11/04 09:32 -0800, Jan Steinman 
 I noticed that I have similar queries that work as expected. The difference 
 appears to be that every query that is broken uses  WITH ROLLUP, and 
 removing this makes them behave as expected.
 
 Is this a known bug? Should I submit it as such?
 
 If someone would be so kind as to point me to the bug system, I'll crawl 
 around in there to see if it's a known problem. 
 
 There is a bug that I about a half year ago reported, 
 http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with WITH 
 ROLLUP is not always NULL, but is instead the foregoing string in the same 
 field. I suspect that other bugs with missing NULL found by searching for 
 ROLLUP are the same problem. (note link titled Affects Me!)
 
 Another, one year ago reported, bug of mine was handled in 5.7.2, but this 
 one not

would you please quote in a readable way instead  



signature.asc
Description: OpenPGP digital signature

Simple Query Question

[Willy Mularto]

Hi,
Please help what is wrong with this simple query SELECT COUNT(key_agent) total 
FROM agents_consolidated  WHERE total = 180
Thanks.



Willy Mularto
F300HD+MR18DE (NLC1725)

Re: Simple Query Question

[Stefan Kuhn]

On Saturday 14 April 2012 09:51:11 Willy Mularto wrote:
 Hi,
 Please help what is wrong with this simple query SELECT COUNT(key_agent)
 total FROM agents_consolidated  WHERE total = 180 Thanks.
You need to use having instead of where, see the documentation.
Stefan



 Willy Mularto
 F300HD+MR18DE (NLC1725)



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

Re: Simple Query Question

[Willy Mularto]

Hi many thanks for the help :)



On Apr 14, 2012, at 6:21 PM, Stefan Kuhn wrote:

 On Saturday 14 April 2012 09:51:11 Willy Mularto wrote:
 Hi,
 Please help what is wrong with this simple query SELECT COUNT(key_agent)
 total FROM agents_consolidated  WHERE total = 180 Thanks.
 You need to use having instead of where, see the documentation.
 Stefan
 
 
 
 Willy Mularto
 F300HD+MR18DE (NLC1725)
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 

Willy Mularto
F300HD+MR18DE (NLC1725)










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

Fw: Simple Query Question

[Abhishek Choudhary]

Hi ,
count() function is a group function so use group by clause  in your select 
statement if you are selecting more than one column data..

here is some sample query related to help you,

select count(*)   from trndisburse:

output:1467

select count(*),pkdisburseid from trndisburse_TMP m Group by
 pkdisburseid;

output:
COUNT(*)PKDISBURSEID

100011120414090807001226
100011120414090807001228
100011120414090807001246
100011120414090807001252
100011120414090807001173
100011120414090807001187
100011120414090807001230
100011120414090807000859
10001112041409080742
10001112041409080751
10001112041409080797
100011120414090807001309
100011120414090807001314
100011120414090807001333
100011120414090807001290
10001112041409
080701
..
..some more  data...

last equivlent to your  problem:

select count(pkdisburseid) from trndisburse_TMP m
where grossamt=6000
  Group by pkdisburseid;

select count(pkdisburseid),grossamt from trndisburse_TMP m
where grossamt=6000
  Group by pkdisburseid,grossamt;

Thanks ,
abhisehk choudhary
www.tech4urhelp.blogspot.com



 From: Stefan Kuhn stef...@web.de
To: mysql@lists.mysql.com 
Sent: Saturday, 14 April 2012 4:51 PM
Subject: Re: Simple Query Question
 
On Saturday 14 April 2012 09:51:11 Willy Mularto wrote:
 Hi,
 Please help what is wrong with this simple query SELECT COUNT(key_agent)
 total FROM agents_consolidated  WHERE total = 180 Thanks.
You need to use having instead of where, see the documentation.
Stefan



 Willy Mularto
 F300HD+MR18DE (NLC1725)



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

query question

[Richard Reina]

Hello All,

Hope everyone is having a wonderful holiday.  I have a table like:

|ID  |city|ST  |memo|

I would like to write a query that somewhat randomly grabs a record for a
for a given city and state. I say randomly because what I'm specifically
after is that if city IS NOT NULL than I want it to grab a record for that
city count(*)/10 of the times.  So that if there are four records for
Carmel, IN then a record that has city Carmel and ST IN will get
picked 40% of the time and record with IN and city=NULL will get picked
60% of the time.  Is this possible in a query or do I need to write code
that will select a query?

Thanks you,

Richard Reina

Yet another query question

[Michael Stroh]

Hi everyone and thanks in advance for the help. I have a query that I'd like to 
perform using two tables but am not sure what the best way to perform it short 
of creating a loop in my code and performing multiple queries.

I have two tables. The first table acts as a master table of sorts and Num in 
Table1 maps directly to Num in Table2. One way to think of this is that I'm 
performing a query on Table2 and grouping the records by MAX(version) but I am 
not interested in records if state = new in Table1 for the value of Num in 
Table2. I've tried to give an example below.

Table1:
Num, state
1  final
2 new
3 final

Table2:
Num, ID,IDt, version
11   100   1
12   101   1
13   102   1
24   100   2
25   103   1
36   100   2
37   103   1
38   104   1

Preferred result:
IDt,   ID,  Num, MAX(version)
100   6  3   2
101   2  1   1
102   3  1   1
103   7  3   1
104   8  3   1

Cheers,
Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Yet another query question

[Gavin Towey]

You'll need to use the technique described here:
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html


-Original Message-
From: Michael Stroh [mailto:st...@astroh.org]
Sent: Monday, July 26, 2010 2:50 PM
To: MySql
Subject: Yet another query question

Hi everyone and thanks in advance for the help. I have a query that I'd like to 
perform using two tables but am not sure what the best way to perform it short 
of creating a loop in my code and performing multiple queries.

I have two tables. The first table acts as a master table of sorts and Num in 
Table1 maps directly to Num in Table2. One way to think of this is that I'm 
performing a query on Table2 and grouping the records by MAX(version) but I am 
not interested in records if state = new in Table1 for the value of Num in 
Table2. I've tried to give an example below.

Table1:
Num, state
1  final
2 new
3 final

Table2:
Num, ID,IDt, version
11   100   1
12   101   1
13   102   1
24   100   2
25   103   1
36   100   2
37   103   1
38   104   1

Preferred result:
IDt,   ID,  Num, MAX(version)
100   6  3   2
101   2  1   1
102   3  1   1
103   7  3   1
104   8  3   1

Cheers,
Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


This message contains confidential information and is intended only for the 
individual named.  If you are not the named addressee, you are notified that 
reviewing, disseminating, disclosing, copying or distributing this e-mail is 
strictly prohibited.  Please notify the sender immediately by e-mail if you 
have received this e-mail by mistake and delete this e-mail from your system. 
E-mail transmission cannot be guaranteed to be secure or error-free as 
information could be intercepted, corrupted, lost, destroyed, arrive late or 
incomplete, or contain viruses. The sender therefore does not accept liability 
for any loss or damage caused by viruses or errors or omissions in the contents 
of this message, which arise as a result of e-mail transmission. [FriendFinder 
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Yet another query question

[Geert-Jan Brits]

Aren't you grouping on IDt?

something like ? :
select t2.IDt,t2.ID,t2.Num,max(t2.version) from table1 as t1, tabl2 as t2
where t1.num=t2.num and t1.state!='new' group by t2.IDt

Cheers,
Geert-Jan

2010/7/26 Michael Stroh st...@astroh.org

 Hi everyone and thanks in advance for the help. I have a query that I'd
 like to perform using two tables but am not sure what the best way to
 perform it short of creating a loop in my code and performing multiple
 queries.

 I have two tables. The first table acts as a master table of sorts and Num
 in Table1 maps directly to Num in Table2. One way to think of this is that
 I'm performing a query on Table2 and grouping the records by MAX(version)
 but I am not interested in records if state = new in Table1 for the value of
 Num in Table2. I've tried to give an example below.

 Table1:
 Num, state
 1  final
 2 new
 3 final

 Table2:
 Num, ID,IDt, version
 11   100   1
 12   101   1
 13   102   1
 24   100   2
 25   103   1
 36   100   2
 37   103   1
 38   104   1

 Preferred result:
 IDt,   ID,  Num, MAX(version)
 100   6  3   2
 101   2  1   1
 102   3  1   1
 103   7  3   1
 104   8  3   1

 Cheers,
 Michael


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=gbr...@gmail.com

Re: Yet another query question

[Michael Stroh]

Yes, sorry, you are correct. I am actually grouping on that other column. I'll 
take a look at this and see if it works for me. Thanks!

Michael


On Jul 26, 2010, at 6:10 PM, Geert-Jan Brits wrote:

 Aren't you grouping on IDt?
 
 something like ? :
 select t2.IDt,t2.ID,t2.Num,max(t2.version) from table1 as t1, tabl2 as t2
 where t1.num=t2.num and t1.state!='new' group by t2.IDt
 
 Cheers,
 Geert-Jan
 
 2010/7/26 Michael Stroh st...@astroh.org
 
 Hi everyone and thanks in advance for the help. I have a query that I'd
 like to perform using two tables but am not sure what the best way to
 perform it short of creating a loop in my code and performing multiple
 queries.
 
 I have two tables. The first table acts as a master table of sorts and Num
 in Table1 maps directly to Num in Table2. One way to think of this is that
 I'm performing a query on Table2 and grouping the records by MAX(version)
 but I am not interested in records if state = new in Table1 for the value of
 Num in Table2. I've tried to give an example below.
 
 Table1:
 Num, state
 1  final
 2 new
 3 final
 
 Table2:
 Num, ID,IDt, version
 11   100   1
 12   101   1
 13   102   1
 24   100   2
 25   103   1
 36   100   2
 37   103   1
 38   104   1
 
 Preferred result:
 IDt,   ID,  Num, MAX(version)
 100   6  3   2
 101   2  1   1
 102   3  1   1
 103   7  3   1
 104   8  3   1
 
 Cheers,
 Michael
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=gbr...@gmail.com
 
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Count Query question

[Keith Clark]

Bob,

Here are a few rows of my data:

17462, 0, '0929998596', '/GraphicNovels/0929998596.jpg', '8.5000',
'2010-05-12 19:02:47', '2008-10-01 00:00:00', '2008-10-01 00:00:00',
'0.50', 1, 1, 7429, 0, '1', 

17461, 1, '1561481912', '/Cooking/1561481912.jpg', '3.', '2010-05-12
19:00:17', '2008-10-01 00:00:00', '2008-10-01 00:00:00', '0.50', 1, 1,
7428, 0, '1', 

Here is the query I ran:

SELECT date(products_date_available) as Date,
COUNT(products_quantity) as 'Titles Available',
SUM(products_quantity) as 'Books Available'
FROM products
WHERE products_quantity  0
GROUP BY date(products_date_available);

And I only got back two rows from over 2 years of daily entries:

'2008-01-01', 3327, '3736'
'2008-10-01', 2739, '2904'

I'm not sure I understand where I'm going wrong.

Keith


On Wed, 2010-05-12 at 21:54 -0500, Bob Cole wrote:
 Kevin:
 I assumed the following data:
 products_id   products_date_available products_quantity
 112010-05-01  1
 112010-05-02  0
 112010-05-03  3
 112010-05-04  3
 112010-05-05  3
 112010-05-06  1
 112010-05-07  0
 112010-05-08  3
 112010-05-09  3
 112010-05-10  3
 112010-05-11  3
 112010-05-12  3
 222010-05-01  1
 222010-05-02  2
 222010-05-03  0
 222010-05-04  3
 222010-05-05  3
 222010-05-06  1
 222010-05-07  0
 222010-05-08  3
 222010-05-09  0
 222010-05-10  3
 222010-05-11  3
 222010-05-12  3
 332010-05-01  1
 332010-05-02  2
 332010-05-03  3
 332010-05-04  3
 332010-05-05  3
 332010-05-06  0
 332010-05-07  0
 332010-05-08  3
 332010-05-09  3
 332010-05-10  0
 332010-05-11  3
 332010-05-12  3
 
 and used the following query:
  SELECT products_date_available, COUNT(products_quantity), 
 SUM(products_quantity)
  FROM products
  WHERE products_quantity  0
  GROUP BY products_date_available
 
 and got the following results:
  products_date_available   COUNT   SUM
  2010-05-01 00:00:00  3   3
  2010-05-02 00:00:00  2   4
  2010-05-03 00:00:00  2   6
  2010-05-04 00:00:00  3   9
  2010-05-05 00:00:00  3   9
  2010-05-06 00:00:00  2   2
  2010-05-08 00:00:00  3   9
  2010-05-09 00:00:00  2   6
  2010-05-10 00:00:00  2   6
  2010-05-11 00:00:00  3   9
  2010-05-12 00:00:00  3   9
 
 One line for each day except that 2010-05-07 is missing because each product 
 had 0 quantity on that day.
 For example, on 2010-05-01, there were 3 products (each with a quantity of 1) 
 for a total quantity of 3.
 I wonder if I am representing your situation correctly.  What am I missing?
 Bob
 
 On May 12, 2010, at 8:00 PM, Keith Clark wrote:
  Hi Bob,
  No, actually it does not.  I'm looking for the count of items.  From
  your query example I only get two rows.  This table has over 2 1/2 years
  of daily sales data.
  Maybe I'm not stating my question correctly...h
  Thanks for responding though, greatly appreciated.
  Keith
  On Wed, 2010-05-12 at 19:46 -0500, Bob Cole wrote:
  Keith:
  Does this work?
  SELECT products_date_available, COUNT(products_quantity)
  FROM products
  WHERE products_quantity  0
 GROUP BY products_date_available
  Hope this helps,
  Bob
  On May 12, 2010, at 3:06 PM, Keith Clark wrote:
  On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote:
  Chris,
  Here is my full table definition:
  
  CREATE TABLE `products` (
  `products_id` int(15) NOT NULL AUTO_INCREMENT,
  `products_quantity` int(4) NOT NULL,
  `products_model` varchar(15) NOT NULL DEFAULT '',
  `products_image` varchar(64) DEFAULT NULL,
  `products_price` decimal(15,4) DEFAULT NULL,
  `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00',
  `products_date_available` datetime DEFAULT '2008-10-01 00:00:00',
  `products_weight` decimal(5,2) DEFAULT '0.50',
  `products_status` tinyint(1) NOT NULL DEFAULT '1',
  `products_tax_class_id` int(11) DEFAULT '1',
  `manufacturers_id` int(11) DEFAULT NULL,
  `products_ordered` int(11) DEFAULT '0',
  `products_format` varchar(20) DEFAULT NULL,
  `abebooks_price` decimal(15,4) DEFAULT NULL,
  PRIMARY KEY (`products_id`,`products_model`),
  UNIQUE KEY `products_model` (`products_model`),
  KEY `idx_products_date_added` (`products_date_added`),
  KEY `manufacturers_id` (`manufacturers_id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1
  
  So, I'd like to create a report that grouped by products_date_available,
  counts all records before products_date_available with a
  products_quantity0.
  
  
  I 

RE: Count Query question

[webmaster]

Hi Keith,

The way I would go about this is to try and pinpoint what the issue is, by 
breaking the query up.

For instance, if you remove the 'product_quantity  0' condition, do you get 
any more rows in your result ?

I also notice that your 'products_date_avaiable' is defaulting to '2008-10-01 
00:00:00' which is the same date in your two sample rows.

Run a query like
  SELECT distinct (products_date_available) FROM products

and see if there are there are any other dates ... if there are only 2, then 
you'll get only two rows.

Thanks,
Justin Tifang

-Original Message-
From: Keith Clark [mailto:keithcl...@k-wbookworm.com] 
Sent: 13 May 2010 14:11
To: mysql@lists.mysql.com
Subject: Re: Count Query question

Bob,

Here are a few rows of my data:

17462, 0, '0929998596', '/GraphicNovels/0929998596.jpg', '8.5000',
'2010-05-12 19:02:47', '2008-10-01 00:00:00', '2008-10-01 00:00:00',
'0.50', 1, 1, 7429, 0, '1', 

17461, 1, '1561481912', '/Cooking/1561481912.jpg', '3.', '2010-05-12
19:00:17', '2008-10-01 00:00:00', '2008-10-01 00:00:00', '0.50', 1, 1,
7428, 0, '1', 

Here is the query I ran:

SELECT date(products_date_available) as Date,
COUNT(products_quantity) as 'Titles Available',
SUM(products_quantity) as 'Books Available'
FROM products
WHERE products_quantity  0
GROUP BY date(products_date_available);

And I only got back two rows from over 2 years of daily entries:

'2008-01-01', 3327, '3736'
'2008-10-01', 2739, '2904'

I'm not sure I understand where I'm going wrong.

Keith


On Wed, 2010-05-12 at 21:54 -0500, Bob Cole wrote:
 Kevin:
 I assumed the following data:
 products_id   products_date_available products_quantity
 112010-05-01  1
 112010-05-02  0
 112010-05-03  3
 112010-05-04  3
 112010-05-05  3
 112010-05-06  1
 112010-05-07  0
 112010-05-08  3
 112010-05-09  3
 112010-05-10  3
 112010-05-11  3
 112010-05-12  3
 222010-05-01  1
 222010-05-02  2
 222010-05-03  0
 222010-05-04  3
 222010-05-05  3
 222010-05-06  1
 222010-05-07  0
 222010-05-08  3
 222010-05-09  0
 222010-05-10  3
 222010-05-11  3
 222010-05-12  3
 332010-05-01  1
 332010-05-02  2
 332010-05-03  3
 332010-05-04  3
 332010-05-05  3
 332010-05-06  0
 332010-05-07  0
 332010-05-08  3
 332010-05-09  3
 332010-05-10  0
 332010-05-11  3
 332010-05-12  3
 
 and used the following query:
  SELECT products_date_available, COUNT(products_quantity), 
 SUM(products_quantity)
  FROM products
  WHERE products_quantity  0
  GROUP BY products_date_available
 
 and got the following results:
  products_date_available   COUNT   SUM
  2010-05-01 00:00:00  3   3
  2010-05-02 00:00:00  2   4
  2010-05-03 00:00:00  2   6
  2010-05-04 00:00:00  3   9
  2010-05-05 00:00:00  3   9
  2010-05-06 00:00:00  2   2
  2010-05-08 00:00:00  3   9
  2010-05-09 00:00:00  2   6
  2010-05-10 00:00:00  2   6
  2010-05-11 00:00:00  3   9
  2010-05-12 00:00:00  3   9
 
 One line for each day except that 2010-05-07 is missing because each product 
 had 0 quantity on that day.
 For example, on 2010-05-01, there were 3 products (each with a quantity of 1) 
 for a total quantity of 3.
 I wonder if I am representing your situation correctly.  What am I missing?
 Bob
 
 On May 12, 2010, at 8:00 PM, Keith Clark wrote:
  Hi Bob,
  No, actually it does not.  I'm looking for the count of items.  From
  your query example I only get two rows.  This table has over 2 1/2 years
  of daily sales data.
  Maybe I'm not stating my question correctly...h
  Thanks for responding though, greatly appreciated.
  Keith
  On Wed, 2010-05-12 at 19:46 -0500, Bob Cole wrote:
  Keith:
  Does this work?
  SELECT products_date_available, COUNT(products_quantity)
  FROM products
  WHERE products_quantity  0
 GROUP BY products_date_available
  Hope this helps,
  Bob
  On May 12, 2010, at 3:06 PM, Keith Clark wrote:
  On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote:
  Chris,
  Here is my full table definition:
  
  CREATE TABLE `products` (
  `products_id` int(15) NOT NULL AUTO_INCREMENT,
  `products_quantity` int(4) NOT NULL,
  `products_model` varchar(15) NOT NULL DEFAULT '',
  `products_image` varchar(64) DEFAULT NULL,
  `products_price` decimal(15,4) DEFAULT NULL,
  `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00',
  `products_date_available` datetime DEFAULT '2008-10-01 00:00:00',
  `products_weight` decimal(5,2

Count Query question

[Keith Clark]

I'm trying to produce a report that will tell me how many products were
available with a Quantity0 before a certain date, and have that ordered
by date.

Table:

Date
Quantity

Result desired

DateQuantity Available
May 1   5000
May 2   5050
May 3   5075

Thanks,

Keith



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Count Query question

[Chris W]

With out the table definitions, I'm not sure how anyone could help.  Can 
you send the output of show create table for each of the tables 
involved in this query?


Chris W

Keith Clark wrote:

I'm trying to produce a report that will tell me how many products were
available with a Quantity0 before a certain date, and have that ordered
by date.

Table:

Date
Quantity

Result desired

DateQuantity Available
May 1   5000
May 2   5050
May 3   5075

Thanks,

Keith



  


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Count Query question

[Keith Clark]

Chris,

Here is my full table definition:

CREATE TABLE `products` (
 `products_id` int(15) NOT NULL AUTO_INCREMENT,
 `products_quantity` int(4) NOT NULL,
 `products_model` varchar(15) NOT NULL DEFAULT '',
 `products_image` varchar(64) DEFAULT NULL,
 `products_price` decimal(15,4) DEFAULT NULL,
 `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00',
 `products_date_available` datetime DEFAULT '2008-10-01 00:00:00',
 `products_weight` decimal(5,2) DEFAULT '0.50',
 `products_status` tinyint(1) NOT NULL DEFAULT '1',
 `products_tax_class_id` int(11) DEFAULT '1',
 `manufacturers_id` int(11) DEFAULT NULL,
 `products_ordered` int(11) DEFAULT '0',
 `products_format` varchar(20) DEFAULT NULL,
 `abebooks_price` decimal(15,4) DEFAULT NULL,
 PRIMARY KEY (`products_id`,`products_model`),
 UNIQUE KEY `products_model` (`products_model`),
 KEY `idx_products_date_added` (`products_date_added`),
 KEY `manufacturers_id` (`manufacturers_id`)
) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1

So, I'd like to create a report that grouped by products_date_available,
counts all records before products_date_available with a
products_quantity0.





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Count Query question

[Keith Clark]

On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote:
 Chris,
 
 Here is my full table definition:
 
 CREATE TABLE `products` (
  `products_id` int(15) NOT NULL AUTO_INCREMENT,
  `products_quantity` int(4) NOT NULL,
  `products_model` varchar(15) NOT NULL DEFAULT '',
  `products_image` varchar(64) DEFAULT NULL,
  `products_price` decimal(15,4) DEFAULT NULL,
  `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00',
  `products_date_available` datetime DEFAULT '2008-10-01 00:00:00',
  `products_weight` decimal(5,2) DEFAULT '0.50',
  `products_status` tinyint(1) NOT NULL DEFAULT '1',
  `products_tax_class_id` int(11) DEFAULT '1',
  `manufacturers_id` int(11) DEFAULT NULL,
  `products_ordered` int(11) DEFAULT '0',
  `products_format` varchar(20) DEFAULT NULL,
  `abebooks_price` decimal(15,4) DEFAULT NULL,
  PRIMARY KEY (`products_id`,`products_model`),
  UNIQUE KEY `products_model` (`products_model`),
  KEY `idx_products_date_added` (`products_date_added`),
  KEY `manufacturers_id` (`manufacturers_id`)
 ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1
 
 So, I'd like to create a report that grouped by products_date_available,
 counts all records before products_date_available with a
 products_quantity0.
 
 
I don't think I'm asking this question properly.

For every date in products_date_available in the table, I'd like to know
the count of items available with products_quantity0 up until that
date.

So if there are 500 days in the table, there should be 500 rows in the
report.  Each showing the products available as of that date in time.

I hope that clarifies it.  I can write a query to do so for each
individual date, just not a report for all dates at the same time.





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Count Query question

[Bob Cole]

Keith:
Does this work?
 SELECT products_date_available, COUNT(products_quantity)
 FROM products
 WHERE products_quantity  0
GROUP BY products_date_available
Hope this helps,
Bob


On May 12, 2010, at 3:06 PM, Keith Clark wrote:

 On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote:
 Chris,
 
 Here is my full table definition:
 
 CREATE TABLE `products` (
 `products_id` int(15) NOT NULL AUTO_INCREMENT,
 `products_quantity` int(4) NOT NULL,
 `products_model` varchar(15) NOT NULL DEFAULT '',
 `products_image` varchar(64) DEFAULT NULL,
 `products_price` decimal(15,4) DEFAULT NULL,
 `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00',
 `products_date_available` datetime DEFAULT '2008-10-01 00:00:00',
 `products_weight` decimal(5,2) DEFAULT '0.50',
 `products_status` tinyint(1) NOT NULL DEFAULT '1',
 `products_tax_class_id` int(11) DEFAULT '1',
 `manufacturers_id` int(11) DEFAULT NULL,
 `products_ordered` int(11) DEFAULT '0',
 `products_format` varchar(20) DEFAULT NULL,
 `abebooks_price` decimal(15,4) DEFAULT NULL,
 PRIMARY KEY (`products_id`,`products_model`),
 UNIQUE KEY `products_model` (`products_model`),
 KEY `idx_products_date_added` (`products_date_added`),
 KEY `manufacturers_id` (`manufacturers_id`)
 ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1
 
 So, I'd like to create a report that grouped by products_date_available,
 counts all records before products_date_available with a
 products_quantity0.
 
 
 I don't think I'm asking this question properly.
 
 For every date in products_date_available in the table, I'd like to know
 the count of items available with products_quantity0 up until that
 date.
 
 So if there are 500 days in the table, there should be 500 rows in the
 report.  Each showing the products available as of that date in time.
 
 I hope that clarifies it.  I can write a query to do so for each
 individual date, just not a report for all dates at the same time.
 
 
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=bobc...@earthlink.net
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Count Query question

[Keith Clark]

Hi Bob,

No, actually it does not.  I'm looking for the count of items.  From
your query example I only get two rows.  This table has over 2 1/2 years
of daily sales data.

Maybe I'm not stating my question correctly...h

Thanks for responding though, greatly appreciated.

Keith

On Wed, 2010-05-12 at 19:46 -0500, Bob Cole wrote:
 Keith:
 Does this work?
  SELECT products_date_available, COUNT(products_quantity)
  FROM products
  WHERE products_quantity  0
 GROUP BY products_date_available
 Hope this helps,
 Bob
 
 
 On May 12, 2010, at 3:06 PM, Keith Clark wrote:
 
  On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote:
  Chris,
  
  Here is my full table definition:
  
  CREATE TABLE `products` (
  `products_id` int(15) NOT NULL AUTO_INCREMENT,
  `products_quantity` int(4) NOT NULL,
  `products_model` varchar(15) NOT NULL DEFAULT '',
  `products_image` varchar(64) DEFAULT NULL,
  `products_price` decimal(15,4) DEFAULT NULL,
  `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00',
  `products_date_available` datetime DEFAULT '2008-10-01 00:00:00',
  `products_weight` decimal(5,2) DEFAULT '0.50',
  `products_status` tinyint(1) NOT NULL DEFAULT '1',
  `products_tax_class_id` int(11) DEFAULT '1',
  `manufacturers_id` int(11) DEFAULT NULL,
  `products_ordered` int(11) DEFAULT '0',
  `products_format` varchar(20) DEFAULT NULL,
  `abebooks_price` decimal(15,4) DEFAULT NULL,
  PRIMARY KEY (`products_id`,`products_model`),
  UNIQUE KEY `products_model` (`products_model`),
  KEY `idx_products_date_added` (`products_date_added`),
  KEY `manufacturers_id` (`manufacturers_id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1
  
  So, I'd like to create a report that grouped by products_date_available,
  counts all records before products_date_available with a
  products_quantity0.
  
  
  I don't think I'm asking this question properly.
  
  For every date in products_date_available in the table, I'd like to know
  the count of items available with products_quantity0 up until that
  date.
  
  So if there are 500 days in the table, there should be 500 rows in the
  report.  Each showing the products available as of that date in time.
  
  I hope that clarifies it.  I can write a query to do so for each
  individual date, just not a report for all dates at the same time.
  
  
  
  
  
  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?unsub=bobc...@earthlink.net
  
 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Count Query question

[Bob Cole]

Kevin:
I assumed the following data:
products_id products_date_available products_quantity
11  2010-05-01  1
11  2010-05-02  0
11  2010-05-03  3
11  2010-05-04  3
11  2010-05-05  3
11  2010-05-06  1
11  2010-05-07  0
11  2010-05-08  3
11  2010-05-09  3
11  2010-05-10  3
11  2010-05-11  3
11  2010-05-12  3
22  2010-05-01  1
22  2010-05-02  2
22  2010-05-03  0
22  2010-05-04  3
22  2010-05-05  3
22  2010-05-06  1
22  2010-05-07  0
22  2010-05-08  3
22  2010-05-09  0
22  2010-05-10  3
22  2010-05-11  3
22  2010-05-12  3
33  2010-05-01  1
33  2010-05-02  2
33  2010-05-03  3
33  2010-05-04  3
33  2010-05-05  3
33  2010-05-06  0
33  2010-05-07  0
33  2010-05-08  3
33  2010-05-09  3
33  2010-05-10  0
33  2010-05-11  3
33  2010-05-12  3

and used the following query:
 SELECT products_date_available, COUNT(products_quantity), 
SUM(products_quantity)
 FROM products
 WHERE products_quantity  0
 GROUP BY products_date_available

and got the following results:
 products_date_available   COUNT   SUM
 2010-05-01 00:00:00  3   3
 2010-05-02 00:00:00  2   4
 2010-05-03 00:00:00  2   6
 2010-05-04 00:00:00  3   9
 2010-05-05 00:00:00  3   9
 2010-05-06 00:00:00  2   2
 2010-05-08 00:00:00  3   9
 2010-05-09 00:00:00  2   6
 2010-05-10 00:00:00  2   6
 2010-05-11 00:00:00  3   9
 2010-05-12 00:00:00  3   9

One line for each day except that 2010-05-07 is missing because each product 
had 0 quantity on that day.
For example, on 2010-05-01, there were 3 products (each with a quantity of 1) 
for a total quantity of 3.
I wonder if I am representing your situation correctly.  What am I missing?
Bob

On May 12, 2010, at 8:00 PM, Keith Clark wrote:
 Hi Bob,
 No, actually it does not.  I'm looking for the count of items.  From
 your query example I only get two rows.  This table has over 2 1/2 years
 of daily sales data.
 Maybe I'm not stating my question correctly...h
 Thanks for responding though, greatly appreciated.
 Keith
 On Wed, 2010-05-12 at 19:46 -0500, Bob Cole wrote:
 Keith:
 Does this work?
 SELECT products_date_available, COUNT(products_quantity)
 FROM products
 WHERE products_quantity  0
GROUP BY products_date_available
 Hope this helps,
 Bob
 On May 12, 2010, at 3:06 PM, Keith Clark wrote:
 On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote:
 Chris,
 Here is my full table definition:
 
 CREATE TABLE `products` (
 `products_id` int(15) NOT NULL AUTO_INCREMENT,
 `products_quantity` int(4) NOT NULL,
 `products_model` varchar(15) NOT NULL DEFAULT '',
 `products_image` varchar(64) DEFAULT NULL,
 `products_price` decimal(15,4) DEFAULT NULL,
 `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00',
 `products_date_available` datetime DEFAULT '2008-10-01 00:00:00',
 `products_weight` decimal(5,2) DEFAULT '0.50',
 `products_status` tinyint(1) NOT NULL DEFAULT '1',
 `products_tax_class_id` int(11) DEFAULT '1',
 `manufacturers_id` int(11) DEFAULT NULL,
 `products_ordered` int(11) DEFAULT '0',
 `products_format` varchar(20) DEFAULT NULL,
 `abebooks_price` decimal(15,4) DEFAULT NULL,
 PRIMARY KEY (`products_id`,`products_model`),
 UNIQUE KEY `products_model` (`products_model`),
 KEY `idx_products_date_added` (`products_date_added`),
 KEY `manufacturers_id` (`manufacturers_id`)
 ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1
 
 So, I'd like to create a report that grouped by products_date_available,
 counts all records before products_date_available with a
 products_quantity0.
 
 
 I don't think I'm asking this question properly.
 
 For every date in products_date_available in the table, I'd like to know
 the count of items available with products_quantity0 up until that
 date.
 
 So if there are 500 days in the table, there should be 500 rows in the
 report.  Each showing the products available as of that date in time.
 
 I hope that clarifies it.  I can write a query to do so for each
 individual date, just not a report for all dates at the same time.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Query question

[Jan Steinman]

I have three tables that work together.

s_product is a list of farm products with an autoincrementing ID.

s_product_market_prices is a list of market pricings, obtained from  
various sources. Each one is dated and refers to exactly one s_product  
record via its ID.


s_product_harvest is a list of harvests, including s_product.ID,  
amount, and date/time.


Now I want to generate a report showing the harvest sums and their  
values, based upon an appropriate market pricing. It was all happy  
when I only had one pricing per product, but then I added new dated  
pricings, and got unexpected results.


I'd be happy if the pricings used were simply in the same year as the  
harvest, but it seems like it picks a random one when I do a LEFT JOIN  
on these tables. When I put additional AND clauses on the join to get  
it to pick a price within the desired date range, it seems to affect  
the number of harvests summed, and they are reduced somehow.


(Apologies for not fully qualifying the unexpected results; I'm hoping  
someone can look at this and quickly show me something stupid I've  
done! :-)


Here's the report:
http://www.EcoReality.org/wiki/2009_harvest

which is generated by the following SQL:

SELECT
 product AS ID,
 MAX(s_product.name) AS Name,
 SUM(quantity) AS Quantity,
 MIN(harvest.units) AS Units,
 CONCAT('$', ROUND((SUM(quantity) * prices.price), 2)) AS Value,
 prices.market_type AS `R-W`,
 COUNT(*) AS Harvests,
 MIN(date) AS Begin,
 MAX(date) AS End
FROM s_product_harvest harvest
INNER JOIN s_product on s_product.ID = harvest.product
LEFT OUTER JOIN s_product_market_prices prices ON ID = prices.product_ID
WHERE date = '{{{1}}}-01-01' AND date = '{{{1}}}-12-31 23:59:59'  
GROUP BY s_product.name


(Note that the token {{{1}}} is replaced with a four-digit year,  
like 2009.)


My first impulse was to change the LEFT OUTER JOIN to:

 s_product_market_prices prices ON ID = prices.product_ID AND  
prices.price_date = '{{{1}}}-01-10' AND prices.price_date =  
'{{{1}}}-12-31 23:59:59'


So that the prices table would only join for the desired year.

What am I doing wrong here?

Following are schemas of the three tables:

CREATE TABLE IF NOT EXISTS `s_product` (
`ID` int(10) unsigned NOT NULL auto_increment,
`super` int(11) default NULL COMMENT 'generalization',
`name` varchar(31) character set utf8 NOT NULL,
`units`  
enum 
('kilograms 
','grams','pounds','ounces','liters','each','cords','bales') character  
set utf8 NOT NULL default 'kilograms' COMMENT 'preferred unit',

`description` varchar(255) character set utf8 NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `Name` (`name`),
KEY `Description` (`description`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='list of  
EcoReality farm products' AUTO_INCREMENT=86 ;


CREATE TABLE IF NOT EXISTS `s_product_harvest` (
`date` datetime NOT NULL COMMENT 'Date and time of harvest.',
`product` int(11) NOT NULL default '53',
`resource` varchar(255) character set utf8 NOT NULL COMMENT  
'Particular animal or tree, etc.',

`quantity` decimal(10,2) NOT NULL default '0.80',
`units`  
enum 
('kilograms 
','grams','pounds','ounces','liters','each','cords','bales') character  
set utf8 NOT NULL default 'kilograms',
`who1` smallint(5) unsigned NOT NULL default '2' COMMENT 'Who  
harvested this resource?',
`who2` smallint(5) unsigned NOT NULL default '4' COMMENT 'Who helped  
harvest this resource?',

`notes` varchar(255) character set utf8 NOT NULL,
KEY `product` (`product`),
KEY `date` (`date`),
KEY `who1` (`who1`,`who2`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin  
COMMENT='historical list of EcoReality farm products harvested';


CREATE TABLE IF NOT EXISTS `s_product_market_prices` (
`product_ID` int(11) NOT NULL,
`price_date` date NOT NULL,
`price_source` varchar(255) character set utf8 NOT NULL,
`market_type` enum('retail','wholesale') character set utf8 NOT NULL  
default 'wholesale',

`price` float NOT NULL,
`units` enum('kilograms','grams','pounds','ounces','liters','each')  
character set utf8 NOT NULL default 'kilograms' COMMENT 'change in  
sync with s_product_harvest.units',

PRIMARY KEY (`product_ID`,`price_date`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='market  
pricing information for EcoReality products';


Thanks for whatever help you can offer!

The Apocalypse has Four Horsemen: climate change, habitat destruction,  
industrial agriculture, and poverty. Each Horseman holds a whip called  
Growth in his hand. None can be stopped unless all are stopped. --  
David Foley

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Query Question

[Dirk Bremer]

 

For the given table:

 

 

FIELD TYPE  COLLATION  NULL
KEY DEFAULT  Extra   PRIVILEGES   COMMENT

 

    -
--  --  ---  --  ---
---

 

job_coop  VARCHAR(6)latin1_swedish_ci
PRI  SELECT,INSERT,UPDATE,REFERENCES

 

ftp_serverVARCHAR(255)  latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES

 

ftp_login VARCHAR(255)  latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES

 

ftp_password  VARCHAR(255)  latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES

 

ftp_mode  ENUM('Production','Test','Both')  latin1_swedish_ci  YES
(NULL)   SELECT,INSERT,UPDATE,REFERENCES

 

ftp_passive   ENUM('Normal','Passive')  latin1_swedish_ci  YES
(NULL)   SELECT,INSERT,UPDATE,REFERENCES

 

url_serverVARCHAR(255)  latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES

 

url_port  INT(11)   NULL
0SELECT,INSERT,UPDATE,REFERENCES 

 

 

 

I have situations where different rows have the same value in the
ftp_server column:

 

 

 

job_coop  ftp_server ftp_loginftp_password
ftp_modeftp_passiveurl_serverurl_port

B1502715027dbs.nisc.lanmailroomca15027
Both  Normal 15027dbs35000

B1512715027dbs.nisc.lanmailroomca15027
Both  Normal   0

B1522715027dbs.nisc.lanmailroomca15027
Test  Normal   0

 

 

I'd like to update the url_server and url_port fields in this example
for B15127 and B15227 to the values contained in B15027. There are other
examples as well. I would like a query that would update all instances
where the ftp_server values matched and where the url_server and
url_port have no assigned values and they would be updated from the
matching ftp_server that did have values in the url_server and url_port.
I imagine that this might require a join and perhaps a temporary table.
Please advise.

 

Dirk Bremer - Senior Systems Engineer - Utility - AMS

NISC Lake St. Louis MO - USA Central Time Zone

636-755-2652 fax 636-755-2502 

dirk.bre...@nisc.coop www.nisc.coop

Simple Query Question

[Ian]

Hi,

I am sure there is a simple solution to this problem, I just cant find it :)

I have got a table that records views for an article for each blog per day.
So the structure is as follows:

CREATE TABLE `wp_views` (
`blog_id` int(11) NOT NULL,
`post_id` int(11) NOT NULL,
`date` date NOT NULL,
`views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Now thats fine and I can pull top blogs per day and thats all fine, but what
I am after is pulling the top articles for a time period and where I am
running into problems is where two blogs have the same post_id's the views
get sum()'d for the day and I cant figure out (read end of year mind block)
how to get around it. Here is my current query (for last 7 days):

SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date =
2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY
views DESC LIMIT 10

Any ideas as to whats wrong. I know its something simple, I just cant put my
finger on it.

Thanks in advance,
Ian

Re: Simple Query Question

[Aleksandar Bradaric]

Hi Ian,

Why do you think something's wrong? Here is my test data and the results 
of your query:

---
mysql SELECT * FROM wp_views;
+-+-++---+
| blog_id | post_id | date   | views |
+-+-++---+
|   1 |   1 | 2009-12-16 | 2 |
|   1 |   1 | 2009-12-17 | 3 |
|   1 |   2 | 2009-12-16 | 4 |
|   1 |   2 | 2009-12-17 | 5 |
|   2 |   1 | 2009-12-16 | 6 |
|   2 |   1 | 2009-12-17 | 7 |
|   2 |   2 | 2009-12-16 | 8 |
|   2 |   2 | 2009-12-17 | 9 |
|   1 |   1 | 2009-12-18 | 1 |
|   1 |   2 | 2009-12-18 | 1 |
|   2 |   1 | 2009-12-18 | 1 |
|   2 |   2 | 2009-12-18 | 1 |
+-+-++---+
12 rows in set (0.00 sec)

mysql SELECT blog_id, post_id, sum( views ) AS views FROM wp_views 
WHERE (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, 
post_id ORDER BY views DESC LIMIT 10;

+-+-+---+
| blog_id | post_id | views |
+-+-+---+
|   2 |   2 |17 |
|   2 |   1 |13 |
|   1 |   2 | 9 |
|   1 |   1 | 5 |
+-+-+---+
4 rows in set (0.00 sec)
---

Seems OK to me... Are you getting different results?


Take care,
Aleksandar


Ian wrote:

Hi,

I am sure there is a simple solution to this problem, I just cant find it :)

I have got a table that records views for an article for each blog per day.
So the structure is as follows:

CREATE TABLE `wp_views` (
`blog_id` int(11) NOT NULL,
`post_id` int(11) NOT NULL,
`date` date NOT NULL,
`views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Now thats fine and I can pull top blogs per day and thats all fine, but what
I am after is pulling the top articles for a time period and where I am
running into problems is where two blogs have the same post_id's the views
get sum()'d for the day and I cant figure out (read end of year mind block)
how to get around it. Here is my current query (for last 7 days):

SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date =
2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY
views DESC LIMIT 10

Any ideas as to whats wrong. I know its something simple, I just cant put my
finger on it.

Thanks in advance,
Ian




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Simple Query Question

[Ian]

Hi,

Thanks, I just checked and it was a memcache that was caching the output.
See I knew it was a simple solution ;)

Thanks for the effort everyone and sorry for wasting time.

Regards
Ian

2009/12/17 Aleksandar Bradaric leann...@gmail.com

 Hi Ian,

 Why do you think something's wrong? Here is my test data and the results of
 your query:
 ---
 mysql SELECT * FROM wp_views;
 +-+-++---+
 | blog_id | post_id | date   | views |
 +-+-++---+
 |   1 |   1 | 2009-12-16 | 2 |
 |   1 |   1 | 2009-12-17 | 3 |
 |   1 |   2 | 2009-12-16 | 4 |
 |   1 |   2 | 2009-12-17 | 5 |
 |   2 |   1 | 2009-12-16 | 6 |
 |   2 |   1 | 2009-12-17 | 7 |
 |   2 |   2 | 2009-12-16 | 8 |
 |   2 |   2 | 2009-12-17 | 9 |
 |   1 |   1 | 2009-12-18 | 1 |
 |   1 |   2 | 2009-12-18 | 1 |
 |   2 |   1 | 2009-12-18 | 1 |
 |   2 |   2 | 2009-12-18 | 1 |
 +-+-++---+
 12 rows in set (0.00 sec)

 mysql SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE
 (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id
 ORDER BY views DESC LIMIT 10;
 +-+-+---+
 | blog_id | post_id | views |
 +-+-+---+
 |   2 |   2 |17 |
 |   2 |   1 |13 |
 |   1 |   2 | 9 |
 |   1 |   1 | 5 |
 +-+-+---+
 4 rows in set (0.00 sec)
 ---

 Seems OK to me... Are you getting different results?


 Take care,
 Aleksandar



 Ian wrote:

 Hi,

 I am sure there is a simple solution to this problem, I just cant find it
 :)

 I have got a table that records views for an article for each blog per
 day.
 So the structure is as follows:

 CREATE TABLE `wp_views` (
 `blog_id` int(11) NOT NULL,
 `post_id` int(11) NOT NULL,
 `date` date NOT NULL,
 `views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 Now thats fine and I can pull top blogs per day and thats all fine, but
 what
 I am after is pulling the top articles for a time period and where I am
 running into problems is where two blogs have the same post_id's the views
 get sum()'d for the day and I cant figure out (read end of year mind
 block)
 how to get around it. Here is my current query (for last 7 days):

 SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date
 =
 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY
 views DESC LIMIT 10

 Any ideas as to whats wrong. I know its something simple, I just cant put
 my
 finger on it.

 Thanks in advance,
 Ian

sql query question that puzzles me

[Lech Buszczynski]

Hi,

This thing puzzles me for quite some time and I wasn't successful in
finding a clear answer anywhere - I would be grateful for some
help.

Here is a db example:

table_1
id
some_field_01
[...]
some_field_20

table_2
itemid (table_1_id)
value

Let's say that the table_2 is used to store some properties of the
item in table_1, there can be many of them (let's say these are just
integers values - not that important in this example).

What I'd like to get is the item from table_1 and the item properties
from table_2, I can do 2 things:

1:
on table_1
SELECT * FROM table_1 WHERE id = SOME_ID
then on table_2
SELECT value FROM table_1 WHERE itemid = SOME_ID.from.table_1

so I get one row from table_1 and many rows from table_2

2:
on both tables:
SELECT tb_1.*,tb_2.value AS property FROM table_1 AS tb_1, table_2 AS
tb_2 WHERE tb_1.id = SOME_ID AND tb_1.id = tb_2.itemid

so I get many rows with one query. The question is, which way is
better if these requests may be executed a large number of times?

I think 1 query is better than 2 but if the table_1 holds a lot of
data - resending the whole thing every time (example 2) just to get the
one integer value seems like a waste of resources.

Or is there a better way to do it? Could someone enlighten me? Thanks!


--
Regards,

Lecho



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Query Question

[Bill Arbuckle]

I am in need of some help for the following:

 

Say I have a table with 1M rows.  Users are being added constantly (not
deleted) during the queries that I am about to explain.  The pk is uid and
appid.  I need to run queries in increments of 100K rows until reaching the
end without duplicating rows in the queries.  I am using a select statement
with a limit of row_index and row_count.  This start row is where my
question arises.

 

If I make a query with limit 0,10 then 2 minutes later 10,10
then 2minutes later 30,10 and so on.  My question is are new rows
added to the end of the table or will they randomly appear in my queries?
If they are added to the end of the table, that is fine because I will pick
them up in my final pass.

 

I hope this is clear enough.  If not, let me know and I will provide more
information.  Thanks!

Re: Query Question

[Walter Heck - OlinData.com]

Bill,

if you use an order by clause in your query, the limit will pick the first
100K rows in that order. That way you can ensure that all rows will be
processed in (wait for it...) order :)

Cheers,

Walter

On Tue, Aug 18, 2009 at 18:44, Bill Arbuckle b...@arbucklellc.com wrote:

 I am in need of some help for the following:



 Say I have a table with 1M rows.  Users are being added constantly (not
 deleted) during the queries that I am about to explain.  The pk is uid and
 appid.  I need to run queries in increments of 100K rows until reaching the
 end without duplicating rows in the queries.  I am using a select statement
 with a limit of row_index and row_count.  This start row is where my
 question arises.



 If I make a query with limit 0,10 then 2 minutes later 10,10
 then 2minutes later 30,10 and so on.  My question is are new rows
 added to the end of the table or will they randomly appear in my queries?
 If they are added to the end of the table, that is fine because I will pick
 them up in my final pass.



 I hope this is clear enough.  If not, let me know and I will provide more
 information.  Thanks!




-- 
Walter Heck, Engineer @ Open Query (http://openquery.com)
Affordable Training and ProActive Support for MySQL  related technologies

Follow our blog at http://openquery.com/blog/
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org

RE: Query Question

[Gavin Towey]

To further emphasize this point:  A table has no order by itself, and you 
should make no assumptions about the order of rows you will get back in a 
select statement, unless you use an ORDER BY clause.

Regards,
Gavin Towey

-Original Message-
From: walterh...@gmail.com [mailto:walterh...@gmail.com] On Behalf Of Walter 
Heck - OlinData.com
Sent: Tuesday, August 18, 2009 9:51 AM
To: b...@arbucklellc.com
Cc: mysql@lists.mysql.com
Subject: Re: Query Question

Bill,

if you use an order by clause in your query, the limit will pick the first
100K rows in that order. That way you can ensure that all rows will be
processed in (wait for it...) order :)

Cheers,

Walter

On Tue, Aug 18, 2009 at 18:44, Bill Arbuckle b...@arbucklellc.com wrote:

 I am in need of some help for the following:



 Say I have a table with 1M rows.  Users are being added constantly (not
 deleted) during the queries that I am about to explain.  The pk is uid and
 appid.  I need to run queries in increments of 100K rows until reaching the
 end without duplicating rows in the queries.  I am using a select statement
 with a limit of row_index and row_count.  This start row is where my
 question arises.



 If I make a query with limit 0,10 then 2 minutes later 10,10
 then 2minutes later 30,10 and so on.  My question is are new rows
 added to the end of the table or will they randomly appear in my queries?
 If they are added to the end of the table, that is fine because I will pick
 them up in my final pass.



 I hope this is clear enough.  If not, let me know and I will provide more
 information.  Thanks!




--
Walter Heck, Engineer @ Open Query (http://openquery.com)
Affordable Training and ProActive Support for MySQL  related technologies

Follow our blog at http://openquery.com/blog/
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org

The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the original 
message.

Re: Query Question

[Martijn Tonies]

To further emphasize this point:  A table has no order by itself,


That's not entirely true ;-) Records are stored in some kind of physical
order, some DBMSses implement clustered keys, meaning that the
records are stored ascending order on disk.

However...

and you should make no assumptions about the order of rows you will get 
back in a select statement, unless you use an ORDER BY clause.


This is correct in that a -result set- does not have an order defined
unless you specify an ORDER BY clause.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database 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/mysql?unsub=arch...@jab.org

Re: Query Question

[Johnny Withers]

It may be true that some DBMSs physically store rows in whatever order you
speicfy; however, this is a MySQL list, and MySQL does not do this (InnoDB
anyway).

For example, take a table with 10,000,000 rows and run a simple select on
it:

Database changed
mysql SELECT id FROM trans_item LIMIT 1\G
*** 1. row ***
id: 8919552
1 row in set (0.08 sec)
mysql SELECT id FROM trans_item ORDER BY id ASC LIMIT 1\G
*** 1. row ***
id: 8441275
1 row in set (0.08 sec)

Sure, the first query may always return that ID number; however, it may not.



On Tue, Aug 18, 2009 at 2:31 PM, Martijn Tonies m.ton...@upscene.comwrote:



 To further emphasize this point:  A table has no order by itself,


 That's not entirely true ;-) Records are stored in some kind of physical
 order, some DBMSses implement clustered keys, meaning that the
 records are stored ascending order on disk.

 However...

 and you should make no assumptions about the order of rows you will get
 back in a select statement, unless you use an ORDER BY clause.


 This is correct in that a -result set- does not have an order defined
 unless you specify an ORDER BY clause.

 With regards,

 Martijn Tonies
 Upscene Productions
 http://www.upscene.com

 Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
 Anywhere, MySQL, InterBase, NexusDB and Firebird!

 Database 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/mysql?unsub=joh...@pixelated.net




-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: Query Question

[Martijn Tonies]

It may be true that some DBMSs physically store rows in whatever order 
you

speicfy;


That's not what I said.


however, this is a MySQL list, and MySQL does not do this (InnoDB
anyway).

For example, take a table with 10,000,000 rows and run a simple select on
it:

Database changed
mysql SELECT id FROM trans_item LIMIT 1\G
*** 1. row ***
id: 8919552
1 row in set (0.08 sec)
mysql SELECT id FROM trans_item ORDER BY id ASC LIMIT 1\G
*** 1. row ***
id: 8441275
1 row in set (0.08 sec)

Sure, the first query may always return that ID number; however, it may 
not.


And you're confusing -physical order- (table order) with -result set order- 
...


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database 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/mysql?unsub=arch...@jab.org

SELECT query question

[Rytsareva, Inna (I)]

Hello.

I have 4 tables:

MainTable (Main_ID, Main_Name)
Table1 (Source1_ID, Source1_Name, Main_ID)
Table2 (Source2_ID, Source2_Name, Main_ID)
Table3 (Source3_ID, Source3_Name, Main_ID)

And a search box.

A user can type any names from Source1_Name or Source2_Name or
Source3_Name.
I need to get Main_ID

How to make it?

Thanks, Inna

Re: SELECT query question

[Jo�o C�ndido de Souza Neto]

select
*
from
MainTable MT
left join Table1 T1 on MT.Main_ID = T1.MainID
left join Table2 T2 on MT.Main_ID = T2.MainID
left join Table3 T3 on MT.Main_ID = T3.MainID
where
T1.Source1_Name = anything or
T2.Source2_Name = anything or
T3.Source3_Name = anything

Not tested.

-- 
João Cândido de Souza Neto
SIENS SOLUÇÕES EM GESTÃO DE NEGÓCIOS
Fone: (0XX41) 3033-3636 - JS
www.siens.com.br

Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem 
news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com...
Hello.

I have 4 tables:

MainTable (Main_ID, Main_Name)
Table1 (Source1_ID, Source1_Name, Main_ID)
Table2 (Source2_ID, Source2_Name, Main_ID)
Table3 (Source3_ID, Source3_Name, Main_ID)

And a search box.

A user can type any names from Source1_Name or Source2_Name or
Source3_Name.
I need to get Main_ID

How to make it?

Thanks, Inna




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: SELECT query question

[Gavin Towey]

Should be more efficient to do something like:

SELECT Main_ID FROM Table1 WHERE Source1_Name = 'name'
UNION
SELECT Main_ID FROM Table2 WHERE Source2_Name = 'name'
UNION
SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name'


-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br]
Sent: Monday, July 27, 2009 1:09 PM
To: mysql@lists.mysql.com
Subject: Re: SELECT query question

select
*
from
MainTable MT
left join Table1 T1 on MT.Main_ID = T1.MainID
left join Table2 T2 on MT.Main_ID = T2.MainID
left join Table3 T3 on MT.Main_ID = T3.MainID
where
T1.Source1_Name = anything or
T2.Source2_Name = anything or
T3.Source3_Name = anything

Not tested.

--
João Cândido de Souza Neto
SIENS SOLUÇÕES EM GESTÃO DE NEGÓCIOS
Fone: (0XX41) 3033-3636 - JS
www.siens.com.br

Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem
news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com...
Hello.

I have 4 tables:

MainTable (Main_ID, Main_Name)
Table1 (Source1_ID, Source1_Name, Main_ID)
Table2 (Source2_ID, Source2_Name, Main_ID)
Table3 (Source3_ID, Source3_Name, Main_ID)

And a search box.

A user can type any names from Source1_Name or Source2_Name or
Source3_Name.
I need to get Main_ID

How to make it?

Thanks, Inna




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the original 
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: SELECT query question

[Jo�o C�ndido de Souza Neto]

There are many ways to get the same result. hehehehe

Gavin Towey gto...@ffn.com escreveu na mensagem 
news:30b3df511cec5c4dae4d0d290504753413956dc...@aaa.pmgi.local...
Should be more efficient to do something like:

SELECT Main_ID FROM Table1 WHERE Source1_Name = 'name'
UNION
SELECT Main_ID FROM Table2 WHERE Source2_Name = 'name'
UNION
SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name'


-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br]
Sent: Monday, July 27, 2009 1:09 PM
To: mysql@lists.mysql.com
Subject: Re: SELECT query question

select
*
from
MainTable MT
left join Table1 T1 on MT.Main_ID = T1.MainID
left join Table2 T2 on MT.Main_ID = T2.MainID
left join Table3 T3 on MT.Main_ID = T3.MainID
where
T1.Source1_Name = anything or
T2.Source2_Name = anything or
T3.Source3_Name = anything

Not tested.

Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem
news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com...
Hello.

I have 4 tables:

MainTable (Main_ID, Main_Name)
Table1 (Source1_ID, Source1_Name, Main_ID)
Table2 (Source2_ID, Source2_Name, Main_ID)
Table3 (Source3_ID, Source3_Name, Main_ID)

And a search box.

A user can type any names from Source1_Name or Source2_Name or
Source3_Name.
I need to get Main_ID

How to make it?

Thanks, Inna




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the 
original message. 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: query question...

[Martijn Engler]

It sounds to me like you want to join the two tables?
http://dev.mysql.com/doc/refman/5.1/en/join.html

On Mon, Jun 15, 2009 at 03:56, brucebedoug...@earthlink.net wrote:
 hi.

 i've got a situation, where i'm trying to figure out how to select an item
 from tblA that may/maynot be in tblB.

 if the item is only in tblA, i can easilty get a list of the items
  select * from tblA

 if the item is in tblA but not linked to tblB, i can get the items as well
  select * from tblA where id not in (select id from tblB);

 but i have no idea how to combine the two selects..

 i need to combine them, as the app can create tblA for a given item, and
 then later on create the data in tblB, with thblA.id = tblB.aid.

 thoughts/pointers on this would be appreciated.

 thanks!



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: query question...

[Shawn Green]

Hi Bruce,

bruce wrote:

hi.

i've got a situation, where i'm trying to figure out how to select an item
from tblA that may/maynot be in tblB.

if the item is only in tblA, i can easilty get a list of the items
 select * from tblA

if the item is in tblA but not linked to tblB, i can get the items as well
 select * from tblA where id not in (select id from tblB);

but i have no idea how to combine the two selects..

i need to combine them, as the app can create tblA for a given item, and
then later on create the data in tblB, with thblA.id = tblB.aid.

thoughts/pointers on this would be appreciated.



Just as the other reply said, you need a JOIN. Specifically you need one 
of the OUTER JOIN such as LEFT JOIN or RIGHT JOIN. Here's the pattern 
for exists in A and is/is not in B (using the fields you specified in 
your sample)


SELECT ...
FROM a
LEFT JOIN b
  ON a.id = b.aid

Any row where b.id is null would indicate a mismatch (the row exists in 
A but not in B). So you can test for things like


all rows in A that have no match in B

SELECT ...
FROM a
LEFT JOIN b
  ON a.id = b.aid
WHERE b.id IS NULL

all rows in B that have no match in A

SELECT ...
FROM b
LEFT JOIN a
  ON a.id = b.aid
WHERE a.id IS NULL

- or -

SELECT ...
FROM a
RIGHT JOIN b
  ON a.id = b.aid
WHERE a.id is null

only rows from A or B that have a matching row in in the other table

SELECT ...
FROM a
INNER JOIN b
  ON a.id = b.id

All Rows from A and only those matching rows from B where b.datecol  
'2009-06-13' 


SELECT ...
FROM a
LEFT JOIN b
  ON a.id = b.id
  AND b.datecol  '2009-06-13'

- this will NOT work -

SELECT ...
FROM a
LEFT JOIN b
  ON a.id = b.id
WHERE b.datecol  '2009-06-13'

It will not work because the WHERE clause will filter from the results 
any rows from A where b.datecol is not  '2009-06-13' which would 
include those rows from A that had no matching row from B (effectively 
turning the LEFT join into an INNER join).  Try it both ways and see.


Yours,

--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

query question...

[bruce]

hi.

i've got a situation, where i'm trying to figure out how to select an item
from tblA that may/maynot be in tblB.

if the item is only in tblA, i can easilty get a list of the items
 select * from tblA

if the item is in tblA but not linked to tblB, i can get the items as well
 select * from tblA where id not in (select id from tblB);

but i have no idea how to combine the two selects..

i need to combine them, as the app can create tblA for a given item, and
then later on create the data in tblB, with thblA.id = tblB.aid.

thoughts/pointers on this would be appreciated.

thanks!



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Optimizing query question, EXPLAIN SELECT ...

[Thomas Thomas]

Hi,

I am pretty new in optimizing tables with index and may need some help.
This is my query:

EXPLAIN SELECT timestamp
FROM Meting_INT_COPY
WHERE blockid = '200811252000'
ORDER BY timestamp DESC
LIMIT 1

If I have an index(blockid),
EXPLAIN will return the following information:

type possible_keys key  rows Extra
ref index_blockid index_blockid 2638 Using where; Using filesort

If I add an index(blockid,timestamp)
EXPLAIN will display the following:

type possible_keys   key
   rows Extra
ref index_blockid,index_blockid_timestampindex_blockid_timestamp8248
Using where; Using index


The index(blockid,timestamp) avoid the filesort + returns the result from
index ! (Using where; Using index)
But why for the index(blockid) 2638 rows are returned and for a more
specific index(blockid,timestamp) 8248 rows are returned ?

Thank you for any answer !

Re: Large Query Question.

[mos]

At 02:49 PM 9/3/2008, Jim Leavitt wrote:

Hi Mike,

Yes sometimes,  the application is an online book selection tool with 
about 1 million titles in it.  Now the queries which return 100,000 rows 
would be something like returning all titles from a given publisher. Most 
of the common searches are fairly quick (1-5 sec).  But this was a 
specific example given to me.  As you can imaging we're joining on many 
tables to pull author data, publication data, etc and displaying it all on 
a detail page.  An example query is.  (note: this is on a development box 
with nothing else on it)


SELECT p.Title FROM products AS p LEFT JOIN productcontributors AS pc ON 
p.RecordReference = pc.RecordReference WHERE pc.rowtype = PR8 AND 
p.feedid = 5 GROUP BY p.id LIMIT 0,10;


returns

10 rows in set (42.12 sec).
(Total of 194557 rows found.)

Now we've never dealt with anything like this before, but there are other 
sites returning similar counts fairly quickly.  The only thing I can think 
of is hardware.  What hardware upgrades would you recommend?  Would it 
even help? Would clustering be an option here?


Any advice is greatly appreciated.

Thanks much.


Jim,
 The problem is likely your index is not defined properly. Use an 
Explain in front of the query to see if it can use just one index from 
each table.


I would try building a compound index on

Products: (RecordReference, FeedId)

ProductContributors: (RecordReference, RowType)

This should get it to execute the join and where clause using just one 
index from each table. Give that a try and see if it speeds things up. :)


Mike





On 3-Sep-08, at 3:02 PM, mos wrote:


Jim,
 Retrieving 100,000 rows will always take some time. Do you really 
need to return that many rows? Are you selecting just the columns you 
need? What are the slow queries?


Mike

At 12:05 PM 9/3/2008, Jim Leavitt wrote:

Greetings List,

We have a medium-large size database application which we are trying
to optimize and I have a few questions.

Server Specs
1 Dual Core 2.6 Ghz
2GB Ram

Database Specs
51 Tables
Min 10 rows, Max 100 rows
Total size approx 2GB

My.cnf
[mysqld]
set-variable=local-infile=0
log-slow-queries=slow-queries.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
key_buffer = 512M
max_allowed_packet=4M
sort_buffer_size = 512M
read_buffer_size = 512M
read_rnd_buffer_size = 256M
record_buffer = 256M
myisam_sort_buffer_size = 512M
thread_cache = 128
query_cache_limit = 1M
query_cache_type = 1
query_cache_size = 32M
join_buffer = 512M
table_cache = 512


We are having trouble with certain queries which are returning
anywhere from 10 - 30 rows.  Total query time is taking approx
1 - 2 mins depending on load.  Is there anything in our conf file
which could improve our performance?  Are there any hardware
recommendations that could help us improve the speed?  Would more
memory help us?  Any comments or recommendations are greatly
appreciated.

Thanks much.


Jim Leavitt
Developer
Treefrog Interactive Inc. (http://www.treefrog.cawww.treefrog.ca)
Bringing the Internet to Life






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


Jim Leavitt
Developer
Treefrog Interactive Inc. (http://www.treefrog.ca/www.treefrog.ca)
Bringing the Internet to Life
ph: 905-836-4442 ext 104
fx: 905-895-6561





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

Re: Large Query Question.

[Jim Lyons]

It's highly unlikely hardware upgrades are needed unless you're on a really
underpowered machine.  How similar are the queries on the other machines?
The limit clause won't reduce the time taken to do the join and grouping,
it will only reduce the amount of output.

Also, I assumeyou have indexes on p.RecordReference, pc.RecordReference,
pc.rowtype, and p.feedid, otherwise you'll be doing table scans.  Are the
indexes up-to-date, ie have you run analyze or optimize table to be sure
they're balanced?  I found that analyze out-of-date stats can make a HUGE
difference in performance.

Also, look at the memory set aside for joins in join_buffer_size.

On Thu, Sep 4, 2008 at 9:38 AM, mos [EMAIL PROTECTED] wrote:

 At 02:49 PM 9/3/2008, Jim Leavitt wrote:

 Hi Mike,

 Yes sometimes,  the application is an online book selection tool with
 about 1 million titles in it.  Now the queries which return 100,000 rows
 would be something like returning all titles from a given publisher. Most of
 the common searches are fairly quick (1-5 sec).  But this was a specific
 example given to me.  As you can imaging we're joining on many tables to
 pull author data, publication data, etc and displaying it all on a detail
 page.  An example query is.  (note: this is on a development box with
 nothing else on it)

 SELECT p.Title FROM products AS p LEFT JOIN productcontributors AS pc ON
 p.RecordReference = pc.RecordReference WHERE pc.rowtype = PR8 AND p.feedid
 = 5 GROUP BY p.id LIMIT 0,10;

 returns

 10 rows in set (42.12 sec).
 (Total of 194557 rows found.)

 Now we've never dealt with anything like this before, but there are other
 sites returning similar counts fairly quickly.  The only thing I can think
 of is hardware.  What hardware upgrades would you recommend?  Would it even
 help? Would clustering be an option here?

 Any advice is greatly appreciated.

 Thanks much.


 Jim,
 The problem is likely your index is not defined properly. Use an
 Explain in front of the query to see if it can use just one index from
 each table.

 I would try building a compound index on

 Products: (RecordReference, FeedId)

 ProductContributors: (RecordReference, RowType)

 This should get it to execute the join and where clause using just one
 index from each table. Give that a try and see if it speeds things up. :)

 Mike




  On 3-Sep-08, at 3:02 PM, mos wrote:

  Jim,
 Retrieving 100,000 rows will always take some time. Do you really
 need to return that many rows? Are you selecting just the columns you need?
 What are the slow queries?

 Mike

 At 12:05 PM 9/3/2008, Jim Leavitt wrote:

 Greetings List,

 We have a medium-large size database application which we are trying
 to optimize and I have a few questions.

 Server Specs
 1 Dual Core 2.6 Ghz
 2GB Ram

 Database Specs
 51 Tables
 Min 10 rows, Max 100 rows
 Total size approx 2GB

 My.cnf
 [mysqld]
 set-variable=local-infile=0
 log-slow-queries=slow-queries.log
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 old_passwords=1
 key_buffer = 512M
 max_allowed_packet=4M
 sort_buffer_size = 512M
 read_buffer_size = 512M
 read_rnd_buffer_size = 256M
 record_buffer = 256M
 myisam_sort_buffer_size = 512M
 thread_cache = 128
 query_cache_limit = 1M
 query_cache_type = 1
 query_cache_size = 32M
 join_buffer = 512M
 table_cache = 512


 We are having trouble with certain queries which are returning
 anywhere from 10 - 30 rows.  Total query time is taking approx
 1 - 2 mins depending on load.  Is there anything in our conf file
 which could improve our performance?  Are there any hardware
 recommendations that could help us improve the speed?  Would more
 memory help us?  Any comments or recommendations are greatly
 appreciated.

 Thanks much.


 Jim Leavitt
 Developer
 Treefrog Interactive Inc. (http://www.treefrog.cawww.treefrog.ca)
 Bringing the Internet to Life





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


 Jim Leavitt
 Developer
 Treefrog Interactive Inc. (http://www.treefrog.ca/www.treefrog.ca)
 Bringing the Internet to Life
 ph: 905-836-4442 ext 104
 fx: 905-895-6561




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




-- 
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

RE: Large Query Question.

[Jerry Schwartz]

-Original Message-
From: Brent Baisley [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 03, 2008 5:35 PM
To: Jim Leavitt
Cc: mysql@lists.mysql.com
Subject: Re: Large Query Question.

That's a lot of data to return, make sure you factor in data load and
transfer time. You may try breaking your query into smaller parts and
recombining the results in a scripting language. If you are searching
on a range (i.e. date range), break the range into smaller parts and
run multiple queries.
Divide and conquer, it will scale better.

[JS] I'm considering changing one of my programs so that it leaves the
result set on the server and pulls one record at a time. Do you have any
sense of how much that might hurt me? We're talking about less than 100,000
records but they are relatively chunky.

In this case, it's the memory usage for the result set that is a concern. I
have to keep increasing the amount of memory available for PHP.
Brent Baisley

On Wed, Sep 3, 2008 at 1:05 PM, Jim Leavitt [EMAIL PROTECTED] wrote:
 Greetings List,

 We have a medium-large size database application which we are trying
to
 optimize and I have a few questions.

 Server Specs
 1 Dual Core 2.6 Ghz
 2GB Ram

 Database Specs
 51 Tables
 Min 10 rows, Max 100 rows
 Total size approx 2GB

 My.cnf
 [mysqld]
 set-variable=local-infile=0
 log-slow-queries=slow-queries.log
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 old_passwords=1
 key_buffer = 512M
 max_allowed_packet=4M
 sort_buffer_size = 512M
 read_buffer_size = 512M
 read_rnd_buffer_size = 256M
 record_buffer = 256M
 myisam_sort_buffer_size = 512M
 thread_cache = 128
 query_cache_limit = 1M
 query_cache_type = 1
 query_cache_size = 32M
 join_buffer = 512M
 table_cache = 512


 We are having trouble with certain queries which are returning
anywhere from
 10 - 30 rows.  Total query time is taking approx 1 - 2 mins
 depending on load.  Is there anything in our conf file which could
improve
 our performance?  Are there any hardware recommendations that could
help us
 improve the speed?  Would more memory help us?  Any comments or
 recommendations are greatly appreciated.

 Thanks much.


 Jim Leavitt
 Developer
 Treefrog Interactive Inc. (www.treefrog.ca)
 Bringing the Internet to Life







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





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

Re: Large Query Question.

[mos]

Jim,
 I've re-posted your message to the list so others can join in the 
fray. :)


Mike

At 10:50 AM 9/4/2008, you wrote:

Hi Mike,

I do believe we have done the indexing properly.  Please advise if we can 
make any adjustments.  Here is the output from the explain statements;


16634be.png


Thanks,

Jim

On 3-Sep-08, at 10:02 PM, mos wrote:


At 02:49 PM 9/3/2008, Jim Leavitt wrote:

Hi Mike,

Yes sometimes,  the application is an online book selection tool with 
about 1 million titles in it.  Now the queries which return 100,000 rows 
would be something like returning all titles from a given publisher. 
Most of the common searches are fairly quick (1-5 sec).  But this was a 
specific example given to me.  As you can imaging we're joining on many 
tables to pull author data, publication data, etc and displaying it all 
on a detail page.  An example query is.  (note: this is on a development 
box with nothing else on it)


SELECT p.Title FROM products AS p LEFT JOIN productcontributors AS pc ON 
p.RecordReference = pc.RecordReference WHERE pc.rowtype = PR8 AND 
p.feedid = 5 GROUP BY p.id LIMIT 0,10;


returns

10 rows in set (42.12 sec).
(Total of 194557 rows found.)

Now we've never dealt with anything like this before, but there are 
other sites returning similar counts fairly quickly.  The only thing I 
can think of is hardware.  What hardware upgrades would you 
recommend?  Would it even help? Would clustering be an option here?


Any advice is greatly appreciated.

Thanks much.


Jim,
 The problem is likely your index is not defined properly. Use an 
Explain in front of the query to see if it can use just one index from 
each table.


I would try building a compound index on

Products: (RecordReference, FeedId)

ProductContributors: (RecordReference, RowType)

This should get it to execute the join and where clause using just one 
index from each table. Give that a try and see if it speeds things up. :)


Mike





On 3-Sep-08, at 3:02 PM, mos wrote:


Jim,
 Retrieving 100,000 rows will always take some time. Do you really 
need to return that many rows? Are you selecting just the columns you 
need? What are the slow queries?


Mike

At 12:05 PM 9/3/2008, Jim Leavitt wrote:

Greetings List,

We have a medium-large size database application which we are trying
to optimize and I have a few questions.

Server Specs
1 Dual Core 2.6 Ghz
2GB Ram

Database Specs
51 Tables
Min 10 rows, Max 100 rows
Total size approx 2GB

My.cnf
[mysqld]
set-variable=local-infile=0
log-slow-queries=slow-queries.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
key_buffer = 512M
max_allowed_packet=4M
sort_buffer_size = 512M
read_buffer_size = 512M
read_rnd_buffer_size = 256M
record_buffer = 256M
myisam_sort_buffer_size = 512M
thread_cache = 128
query_cache_limit = 1M
query_cache_type = 1
query_cache_size = 32M
join_buffer = 512M
table_cache = 512


We are having trouble with certain queries which are returning
anywhere from 10 - 30 rows.  Total query time is taking approx
1 - 2 mins depending on load.  Is there anything in our conf file
which could improve our performance?  Are there any hardware
recommendations that could help us improve the speed?  Would more
memory help us?  Any comments or recommendations are greatly
appreciated.

Thanks much.


Jim Leavitt
Developer
Treefrog Interactive Inc. (http://www.treefrog.cawww.treefrog.ca)
Bringing the Internet to Life






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


Jim Leavitt
Developer
Treefrog Interactive Inc. (http://www.treefrog.ca/www.treefrog.ca)
Bringing the Internet to Life
ph: 905-836-4442 ext 104
fx: 905-895-6561




Jim Leavitt
Developer
Treefrog Interactive Inc. (http://www.treefrog.ca/www.treefrog.ca)
Bringing the Internet to Life
ph: 905-836-4442 ext 104
fx: 905-895-6561








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

Re: Large Query Question.

[David Ashley]

On Thu, Sep 4, 2008 at 10:38 AM, mos [EMAIL PROTECTED] wrote
Jim,

The problem is likely your index is not defined properly. Use an Explain
in front of the query to see if it can use just one index from each table.

I would try building a compound index on

Products: (RecordReference, FeedId)

ProductContributors: (RecordReference, RowType)

This should get it to execute the join and where clause using just one index
from each table. Give that a try and see if it speeds things up. :)

Mike



I concur.

The SELECT time is going to resemble something like:

K_1 * F_1(number_of_records_in_database) + K_2 *
F_2(number_of_records_selected)

If the indices are effective, F_1 = log(N), but if the indices are not
effective, F_1 = N.

One thing you may want to try to narrow down the problem is just
retrieving 100 records (the COUNT clause of a query) and see how that
affects the speed, then try the full set and see how it is different.

If they aren't very different, then it is a F_1 problem.

But if they are different, then it is a K_2 / F_2 problem.

As far as K_2 or F_2 problems ...

Another possibility is that you are using ORDER BY on a large result set
that isn't indexed for an effective sort.  Try dropping the ORDER BY and see
what happens.

My view of how MySQL might work internally is perhaps naive.  But sorting
can be worst case O(N**2).

Dave.

 On 3-Sep-08, at 3:02 PM, mos wrote:

 Jim,
 Retrieving 100,000 rows will always take some time. Do you really
 need to return that many rows? Are you selecting just the columns you need?
 What are the slow queries?

 Mike

 At 12:05 PM 9/3/2008, Jim Leavitt wrote:

 Greetings List,

 We have a medium-large size database application which we are trying
 to optimize and I have a few questions.

 Server Specs
 1 Dual Core 2.6 Ghz
 2GB Ram

 Database Specs
 51 Tables
 Min 10 rows, Max 100 rows
 Total size approx 2GB

 My.cnf
 [mysqld]
 set-variable=local-infile=0
 log-slow-queries=slow-queries.log
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 old_passwords=1
 key_buffer = 512M
 max_allowed_packet=4M
 sort_buffer_size = 512M
 read_buffer_size = 512M
 read_rnd_buffer_size = 256M
 record_buffer = 256M
 myisam_sort_buffer_size = 512M
 thread_cache = 128
 query_cache_limit = 1M
 query_cache_type = 1
 query_cache_size = 32M
 join_buffer = 512M
 table_cache = 512


 We are having trouble with certain queries which are returning
 anywhere from 10 - 30 rows.  Total query time is taking approx
 1 - 2 mins depending on load.  Is there anything in our conf file
 which could improve our performance?  Are there any hardware
 recommendations that could help us improve the speed?  Would more
 memory help us?  Any comments or recommendations are greatly
 appreciated.

 Thanks much.


 Jim Leavitt
 Developer
 Treefrog Interactive Inc. (http://www.treefrog.cawww.treefrog.ca)
 Bringing the Internet to Life





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


 Jim Leavitt
 Developer
 Treefrog Interactive Inc. (http://www.treefrog.ca/www.treefrog.ca)
 Bringing the Internet to Life
 ph: 905-836-4442 ext 104
 fx: 905-895-6561




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

Fwd: Large Query Question.

[David Ashley]

 I concur.

The SELECT time is going to resemble something like:

K_1 * F_1(number_of_records_in_database) + K_2 *
F_2(number_of_records_selected)

If the indices are effective, F_1 = log(N), but if the indices are not
effective, F_1 = N.

One thing you may want to try to narrow down the problem is just
retrieving 100 records (the COUNT clause of a query) and see how that
affects the speed, then try the full set and see how it is different.

If they aren't very different, then it is a F_1 problem.

But if they are different, then it is a K_2 / F_2 problem.

As far as K_2 or F_2 problems ...

Another possibility is that you are using ORDER BY on a large result set
that isn't indexed for an effective sort.  Try dropping the ORDER BY and see
what happens.

My view of how MySQL might work internally is perhaps naive.  But sorting
can be worst case O(N**2).

Dave.


Addendum:  I misremembered the SQL keywords.  It isn't COUNT.  It is (I
think) LIMIT.

Also, ORDER BY might be GROUP BY.

Oopsie.

Large Query Question.

[Jim Leavitt]

Greetings List,

We have a medium-large size database application which we are trying  
to optimize and I have a few questions.


Server Specs
1 Dual Core 2.6 Ghz
2GB Ram

Database Specs
51 Tables
Min 10 rows, Max 100 rows
Total size approx 2GB

My.cnf
[mysqld]
set-variable=local-infile=0
log-slow-queries=slow-queries.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
key_buffer = 512M
max_allowed_packet=4M
sort_buffer_size = 512M
read_buffer_size = 512M
read_rnd_buffer_size = 256M
record_buffer = 256M
myisam_sort_buffer_size = 512M
thread_cache = 128
query_cache_limit = 1M
query_cache_type = 1
query_cache_size = 32M
join_buffer = 512M
table_cache = 512


We are having trouble with certain queries which are returning  
anywhere from 10 - 30 rows.  Total query time is taking approx  
1 - 2 mins depending on load.  Is there anything in our conf file  
which could improve our performance?  Are there any hardware  
recommendations that could help us improve the speed?  Would more  
memory help us?  Any comments or recommendations are greatly  
appreciated.


Thanks much.


Jim Leavitt
Developer
Treefrog Interactive Inc. (www.treefrog.ca)
Bringing the Internet to Life

Re: Large Query Question.

[mos]

Jim,
  Retrieving 100,000 rows will always take some time. Do you really 
need to return that many rows? Are you selecting just the columns you need? 
What are the slow queries?


Mike

At 12:05 PM 9/3/2008, Jim Leavitt wrote:

Greetings List,

We have a medium-large size database application which we are trying
to optimize and I have a few questions.

Server Specs
1 Dual Core 2.6 Ghz
2GB Ram

Database Specs
51 Tables
Min 10 rows, Max 100 rows
Total size approx 2GB

My.cnf
[mysqld]
set-variable=local-infile=0
log-slow-queries=slow-queries.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
key_buffer = 512M
max_allowed_packet=4M
sort_buffer_size = 512M
read_buffer_size = 512M
read_rnd_buffer_size = 256M
record_buffer = 256M
myisam_sort_buffer_size = 512M
thread_cache = 128
query_cache_limit = 1M
query_cache_type = 1
query_cache_size = 32M
join_buffer = 512M
table_cache = 512


We are having trouble with certain queries which are returning
anywhere from 10 - 30 rows.  Total query time is taking approx
1 - 2 mins depending on load.  Is there anything in our conf file
which could improve our performance?  Are there any hardware
recommendations that could help us improve the speed?  Would more
memory help us?  Any comments or recommendations are greatly
appreciated.

Thanks much.


Jim Leavitt
Developer
Treefrog Interactive Inc. (www.treefrog.ca)
Bringing the Internet to Life








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

Re: Large Query Question.

[Jim Lyons]

What are the queries?  Are they straight forward selects or joins?  Are the
columns you select from indexed and are the indexes up-to-date?

On Wed, Sep 3, 2008 at 12:05 PM, Jim Leavitt [EMAIL PROTECTED] wrote:

 Greetings List,

 We have a medium-large size database application which we are trying to
 optimize and I have a few questions.

 Server Specs
 1 Dual Core 2.6 Ghz
 2GB Ram

 Database Specs
 51 Tables
 Min 10 rows, Max 100 rows
 Total size approx 2GB

 My.cnf
 [mysqld]
 set-variable=local-infile=0
 log-slow-queries=slow-queries.log
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 old_passwords=1
 key_buffer = 512M
 max_allowed_packet=4M
 sort_buffer_size = 512M
 read_buffer_size = 512M
 read_rnd_buffer_size = 256M
 record_buffer = 256M
 myisam_sort_buffer_size = 512M
 thread_cache = 128
 query_cache_limit = 1M
 query_cache_type = 1
 query_cache_size = 32M
 join_buffer = 512M
 table_cache = 512


 We are having trouble with certain queries which are returning anywhere
 from 10 - 30 rows.  Total query time is taking approx 1 - 2 mins
 depending on load.  Is there anything in our conf file which could improve
 our performance?  Are there any hardware recommendations that could help us
 improve the speed?  Would more memory help us?  Any comments or
 recommendations are greatly appreciated.

 Thanks much.


 Jim Leavitt
 Developer
 Treefrog Interactive Inc. (www.treefrog.ca)
 Bringing the Internet to Life








-- 
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

Re: Large Query Question.

[Brent Baisley]

That's a lot of data to return, make sure you factor in data load and
transfer time. You may try breaking your query into smaller parts and
recombining the results in a scripting language. If you are searching
on a range (i.e. date range), break the range into smaller parts and
run multiple queries.
Divide and conquer, it will scale better.

Brent Baisley

On Wed, Sep 3, 2008 at 1:05 PM, Jim Leavitt [EMAIL PROTECTED] wrote:
 Greetings List,

 We have a medium-large size database application which we are trying to
 optimize and I have a few questions.

 Server Specs
 1 Dual Core 2.6 Ghz
 2GB Ram

 Database Specs
 51 Tables
 Min 10 rows, Max 100 rows
 Total size approx 2GB

 My.cnf
 [mysqld]
 set-variable=local-infile=0
 log-slow-queries=slow-queries.log
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 old_passwords=1
 key_buffer = 512M
 max_allowed_packet=4M
 sort_buffer_size = 512M
 read_buffer_size = 512M
 read_rnd_buffer_size = 256M
 record_buffer = 256M
 myisam_sort_buffer_size = 512M
 thread_cache = 128
 query_cache_limit = 1M
 query_cache_type = 1
 query_cache_size = 32M
 join_buffer = 512M
 table_cache = 512


 We are having trouble with certain queries which are returning anywhere from
 10 - 30 rows.  Total query time is taking approx 1 - 2 mins
 depending on load.  Is there anything in our conf file which could improve
 our performance?  Are there any hardware recommendations that could help us
 improve the speed?  Would more memory help us?  Any comments or
 recommendations are greatly appreciated.

 Thanks much.


 Jim Leavitt
 Developer
 Treefrog Interactive Inc. (www.treefrog.ca)
 Bringing the Internet to Life







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

RE: Large Query Question.

[Tom Horstmann]

Right... and perhaps try MySQL Enterprise Monitor. A trial is available from
mysql.com. It may give you hints on your mysql.cnf.

Kind regards,

TomH

-Original Message-
From: Brent Baisley [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 03, 2008 11:35 PM
To: Jim Leavitt
Cc: mysql@lists.mysql.com
Subject: Re: Large Query Question.

That's a lot of data to return, make sure you factor in data load and
transfer time. You may try breaking your query into smaller parts and
recombining the results in a scripting language. If you are searching
on a range (i.e. date range), break the range into smaller parts and
run multiple queries.
Divide and conquer, it will scale better.

Brent Baisley

On Wed, Sep 3, 2008 at 1:05 PM, Jim Leavitt [EMAIL PROTECTED] wrote:
 Greetings List,

 We have a medium-large size database application which we are trying to
 optimize and I have a few questions.

 Server Specs
 1 Dual Core 2.6 Ghz
 2GB Ram

 Database Specs
 51 Tables
 Min 10 rows, Max 100 rows
 Total size approx 2GB

 My.cnf
 [mysqld]
 set-variable=local-infile=0
 log-slow-queries=slow-queries.log
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 old_passwords=1
 key_buffer = 512M
 max_allowed_packet=4M
 sort_buffer_size = 512M
 read_buffer_size = 512M
 read_rnd_buffer_size = 256M
 record_buffer = 256M
 myisam_sort_buffer_size = 512M
 thread_cache = 128
 query_cache_limit = 1M
 query_cache_type = 1
 query_cache_size = 32M
 join_buffer = 512M
 table_cache = 512


 We are having trouble with certain queries which are returning anywhere
from
 10 - 30 rows.  Total query time is taking approx 1 - 2 mins
 depending on load.  Is there anything in our conf file which could improve
 our performance?  Are there any hardware recommendations that could help
us
 improve the speed?  Would more memory help us?  Any comments or
 recommendations are greatly appreciated.

 Thanks much.


 Jim Leavitt
 Developer
 Treefrog Interactive Inc. (www.treefrog.ca)
 Bringing the Internet to Life







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


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

Re: Large Query Question.

[David Ashley]

On Wed, Sep 3, 2008 at 1:05 PM, Jim Leavitt [EMAIL PROTECTED] wrote:


 We are having trouble with certain queries which are returning anywhere
 from 10 - 30 rows.  Total query time is taking approx 1 - 2 mins
 depending on load.  Is there anything in our conf file which could improve
 our performance?  Are there any hardware recommendations that could help us
 improve the speed?  Would more memory help us?  Any comments or
 recommendations are greatly appreciated.

Returning 100,000 to 300,000 rows will take some time no matter how you
slice it.

A more common approach is to be sure that the database is organized for
O(log N) retrieval, then to retrieve only the records you need (the ones you
need to display, for example), then to execute a second query to get more,
and then a third query, etc.

O(log N) retrieval = indices for the columns and the database arranged so
that equality and ordering are implemented using native data types.

What is your application?

Do you really need all those rows at one time?

delete query question

[Jeff Mckeon]

I think this is possible but I'm having a total brain fart as to how to
construct the query..

Table2.ticket = table1.ID

Table2 is a many to 1 relationship to table1

I need to delete all records from table1 where created 
unix_timestamp(date_sub(now(), interval 3 month)) 
And all rows from table2 where Table2.ticket = Table1.ID (of the deleted
rows..)

Can't this be done in one query? Or two?

Thanks,

Jeff




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

Re: delete query question

[Ian Simpson]

If the tables are InnoDB, you could temporarily set up a foreign key
relationship between the two, with the 'ON DELETE CASCADE' option. 

On Tue, 2008-07-08 at 11:14 -0400, Jeff Mckeon wrote:
 I think this is possible but I'm having a total brain fart as to how to
 construct the query..
 
 Table2.ticket = table1.ID
 
 Table2 is a many to 1 relationship to table1
 
 I need to delete all records from table1 where created 
 unix_timestamp(date_sub(now(), interval 3 month)) 
 And all rows from table2 where Table2.ticket = Table1.ID (of the deleted
 rows..)
 
 Can't this be done in one query? Or two?
 
 Thanks,
 
 Jeff
 
 
 
 
-- 
Ian Simpson
System Administrator
MyJobGroup

This email may contain confidential information and is intended for the 
recipient(s) only. If an addressing or transmission error has misdirected this 
email, please notify the author by replying to this email. If you are not the 
intended recipient(s) disclosure, distribution, copying or printing of this 
email is strictly prohibited and you should destroy this mail. Information or 
opinions in this message shall not be treated as neither given nor endorsed by 
the company. Neither the company nor the sender accepts any responsibility for 
viruses or other destructive elements and it is your responsibility to scan any 
attachments.

RE: delete query question

[Jeff Mckeon]

 -Original Message-
 From: Ian Simpson [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, July 08, 2008 11:27 AM
 To: Jeff Mckeon
 Cc: mysql@lists.mysql.com
 Subject: Re: delete query question
 
 If the tables are InnoDB, you could temporarily set up a foreign key
 relationship between the two, with the 'ON DELETE CASCADE' option.
 

Nope, MyISAM...

 On Tue, 2008-07-08 at 11:14 -0400, Jeff Mckeon wrote:
  I think this is possible but I'm having a total brain fart as to how
 to
  construct the query..
 
  Table2.ticket = table1.ID
 
  Table2 is a many to 1 relationship to table1
 
  I need to delete all records from table1 where created 
  unix_timestamp(date_sub(now(), interval 3 month))
  And all rows from table2 where Table2.ticket = Table1.ID (of the
 deleted
  rows..)
 
  Can't this be done in one query? Or two?
 
  Thanks,
 
  Jeff
 
 
 
 
 --
 Ian Simpson
 System Administrator
 MyJobGroup
 
 This email may contain confidential information and is intended for the
 recipient(s) only. If an addressing or transmission error has
 misdirected this email, please notify the author by replying to this
 email. If you are not the intended recipient(s) disclosure,
 distribution, copying or printing of this email is strictly prohibited
 and you should destroy this mail. Information or opinions in this
 message shall not be treated as neither given nor endorsed by the
 company. Neither the company nor the sender accepts any responsibility
 for viruses or other destructive elements and it is your responsibility
 to scan any attachments.


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

RE: delete query question

[Ian Simpson]

Oh well ;)

It looks like you can use joins in a delete statement, and delete the
joined rows, which will delete from the individual tables.

So something like:

delete table1, table2 from table1 inner join table2 on table1.ID =
table2.ticket where...

should do it

I modified the above code from 

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

just search in the page for 'join' and you'll find the relevant section


On Tue, 2008-07-08 at 11:35 -0400, Jeff Mckeon wrote:
 
  -Original Message-
  From: Ian Simpson [mailto:[EMAIL PROTECTED]
  Sent: Tuesday, July 08, 2008 11:27 AM
  To: Jeff Mckeon
  Cc: mysql@lists.mysql.com
  Subject: Re: delete query question
  
  If the tables are InnoDB, you could temporarily set up a foreign key
  relationship between the two, with the 'ON DELETE CASCADE' option.
  
 
 Nope, MyISAM...
 
  On Tue, 2008-07-08 at 11:14 -0400, Jeff Mckeon wrote:
   I think this is possible but I'm having a total brain fart as to how
  to
   construct the query..
  
   Table2.ticket = table1.ID
  
   Table2 is a many to 1 relationship to table1
  
   I need to delete all records from table1 where created 
   unix_timestamp(date_sub(now(), interval 3 month))
   And all rows from table2 where Table2.ticket = Table1.ID (of the
  deleted
   rows..)
  
   Can't this be done in one query? Or two?
  
   Thanks,
  
   Jeff
  
  
  
  
  --
  Ian Simpson
  System Administrator
  MyJobGroup
  
  This email may contain confidential information and is intended for the
  recipient(s) only. If an addressing or transmission error has
  misdirected this email, please notify the author by replying to this
  email. If you are not the intended recipient(s) disclosure,
  distribution, copying or printing of this email is strictly prohibited
  and you should destroy this mail. Information or opinions in this
  message shall not be treated as neither given nor endorsed by the
  company. Neither the company nor the sender accepts any responsibility
  for viruses or other destructive elements and it is your responsibility
  to scan any attachments.
 
 
-- 
Ian Simpson
System Administrator
MyJobGroup

This email may contain confidential information and is intended for the 
recipient(s) only. If an addressing or transmission error has misdirected this 
email, please notify the author by replying to this email. If you are not the 
intended recipient(s) disclosure, distribution, copying or printing of this 
email is strictly prohibited and you should destroy this mail. Information or 
opinions in this message shall not be treated as neither given nor endorsed by 
the company. Neither the company nor the sender accepts any responsibility for 
viruses or other destructive elements and it is your responsibility to scan any 
attachments.

Re: delete query question

[Peter Brawley]

Jeff,


Table2.ticket = table1.ID
Table2 is a many to 1 relationship to table1
I need to delete all records from table1 where created 
unix_timestamp(date_sub(now(), interval 3 month)) 
And all rows from table2 where Table2.ticket = Table1.ID 
(of the deleted rows..)


Like this (untested)?

DELETE table1,table2
FROM table1 t1
JOIN table2 t2 ON t1.id=t2.ticket
WHERE t2.created  UNIX_TIMESTAMP( DATE_SUB( NOW(), INTERVAL 3 MONTH )) ;

PB

-

Jeff Mckeon wrote:

I think this is possible but I'm having a total brain fart as to how to
construct the query..

Table2.ticket = table1.ID

Table2 is a many to 1 relationship to table1

I need to delete all records from table1 where created 
unix_timestamp(date_sub(now(), interval 3 month)) 
And all rows from table2 where Table2.ticket = Table1.ID (of the deleted

rows..)

Can't this be done in one query? Or two?

Thanks,

Jeff




  


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

RE: delete query question

[Jeff Mckeon]

Thanks, that did it!

 -Original Message-
 From: Peter Brawley [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, July 08, 2008 11:57 AM
 To: Jeff Mckeon
 Cc: mysql@lists.mysql.com
 Subject: Re: delete query question
 
 Jeff,
 
 Table2.ticket = table1.ID
 Table2 is a many to 1 relationship to table1
 I need to delete all records from table1 where created 
 unix_timestamp(date_sub(now(), interval 3 month))
 And all rows from table2 where Table2.ticket = Table1.ID
 (of the deleted rows..)
 
 Like this (untested)?
 
 DELETE table1,table2
 FROM table1 t1
 JOIN table2 t2 ON t1.id=t2.ticket
 WHERE t2.created  UNIX_TIMESTAMP( DATE_SUB( NOW(), INTERVAL 3 MONTH ))
 ;
 
 PB
 
 -
 
 Jeff Mckeon wrote:
  I think this is possible but I'm having a total brain fart as to how
 to
  construct the query..
 
  Table2.ticket = table1.ID
 
  Table2 is a many to 1 relationship to table1
 
  I need to delete all records from table1 where created 
  unix_timestamp(date_sub(now(), interval 3 month))
  And all rows from table2 where Table2.ticket = Table1.ID (of the
 deleted
  rows..)
 
  Can't this be done in one query? Or two?
 
  Thanks,
 
  Jeff
 
 
 
 
 


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

Re: A SQL Query Question

[Peter Brawley]

userId long
picture MeduimBlob
datePosted DateTime
A userId can have many pictures posted. I want to write a 
query that returns a distinct userId along with the most 
recent picture posted. Can someone suggest an elegant and 
fast query to accomplish this?


Latest pic for user N:

SELECT userID,MAX(dateposted)
FROM tbl
WHERE userID=N;

Latest pics per user:

SELECT t1.userID,t1.dateposted
FROM tbl t1
LEFT JOIN tbl t2 ON t1.userID=t2.userID AND t1.datepostedt2.dateposted
WHERE t2.userID IS NULL;

PB

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

A SQL Query Question

[samk]

See Thread at: http://www.techienuggets.com/Detail?tx=32975 Posted on behalf of 
a User

Hello everyone,

I have a table A:

userId long
picture MeduimBlob
datePosted DateTime

A userId can have many pictures posted. I want to write a query that returns a 
distinct userId along with the most recent picture posted. Can someone suggest 
an elegant and fast query to accomplish this?

Thanks

Adam



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

Re: A SQL Query Question

[samk]

See Thread at: http://www.techienuggets.com/Detail?tx=32975 Posted on behalf of 
a User

select userId, picture, MAX(datePosted) from A order by datePosted;





In Response To: 

Hello everyone,

I have a table A:

userId long
picture MeduimBlob
datePosted DateTime

A userId can have many pictures posted. I want to write a query that returns a 
distinct userId along with the most recent picture posted. Can someone suggest 
an elegant and fast query to accomplish this?

Thanks

Adam

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

Re: SQL query question for GROUP BY

[Victor Danilchenko]

	I just thought of something else... could the same be accomplished 
using stored routines? I could find no way in MySQL to create stored 
routines which could be used with the 'group by' queries though.


	If this were possible, it should then be also possible to define a 
'LAST' stored routine, or something which would output a given field 
value based on whether some other field (say, numeric ID, or timestamp) 
was the highest in its group.


	This looks to be possible with external functions ('CREATE AGGREGATE 
FUNCTION'), but this would require writing an external library to handle 
the call, too. It would be strange it if were impossible to create an 
aggregate stored procedure.


Does anyone know if it's possible to define stored procedures this way?

Rob Wultsch wrote:

On Fri, Apr 11, 2008 at 1:01 PM, Victor Danilchenko
[EMAIL PROTECTED] wrote:

Oooh, this looks evil. It seems like such a simple thing. I guess
creating max(log_date) as a field, and then joining on it, is a solution --
but my actual query (not the abridged version) is already half a page long.

I think at this point, unless someone else suggests a better
solution, this would be easier to do programatically -- skip the group
altogether, and instead simply order the rows, and grab the last one for
each username in code.

I guess another alternative would be to use a View for the UNIONized
query, but doesn't MySQL 'fake' views in 5.0 somehow?


I have used views to good results, however I have read not good things
about them. I would not be surprised if they worked well for this use.

I would also not be surprised if the merge storage engine was a better
option for you.

Possibly interesting:
http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/




--
Victor Danilchenko
Senior Software Engineer, AskOnline.net
[EMAIL PROTECTED] - 617-273-0119

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

Re: SQL query question for GROUP BY

[Perrin Harkins]

On Fri, Apr 11, 2008 at 4:01 PM, Victor Danilchenko
[EMAIL PROTECTED] wrote:
 Oooh, this looks evil. It seems like such a simple thing. I guess
 creating max(log_date) as a field, and then joining on it, is a solution --
 but my actual query (not the abridged version) is already half a page long.

A derived table or a LEFT JOIN are your best bets, as shown here:
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

In most cases, the derived table is best.  It creates a temp table
automatically, so it's similar to using a view.  My experiments with
actual views gave dismal performance, and the user variable trick
described on Baron's blog is pretty hard to get right.

 I think at this point, unless someone else suggests a better
 solution, this would be easier to do programatically -- skip the group
 altogether, and instead simply order the rows, and grab the last one for
 each username in code.

If you don't have that much data to worry about then this could be
good, but it's often tricky to code correctly because of the state you
have to keep track of.

Also, use UNION ALL if you don't need MySQL to remove duplicate rows.
It makes a big difference in performance.

- Perrin

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

SQL query question for GROUP BY

[Victor Danilchenko]

Hi all,

	I trying to run a query where, after doing a UNION on two different 
SELECTs, I need to sort the result by username and log_date fields, and 
then grab the last entry for each username ('last' as determined by the 
ordering of the log_date field, which is a datetime).


	GROUP BY seems like an obvious choice; 'GROUP BY username', to be 
exact. However, this seems to produce not the last row's values, but 
ones from a random row in the group.


	I don't think the fact that I am doing this on a subquery is relevant, 
but just in case, I am including this info.


Here is what the query looks like, abridged:


SELECT id,username,log_date,event_type
FROM (SELECT * FROM a
  UNION
  SELECT * from b) as h
GROUP BY username


	Basically, what I need is the chronologically last event_type value for 
each user. I can achieve something similar by doing SELECT 
MAX(event_type) -- but I need the equivalent of SELECT LAST(event_type); 
last row instead of max-field-value row.


	I keep having a feeling that I am making this way more complicated than 
it has to be, and that there's a very simple way to return only the last 
row for each username; but i am at a loss as to how to do it.



--
Victor Danilchenko
Senior Software Engineer, AskOnline.net
[EMAIL PROTECTED] - 617-273-0119

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

Re: SQL query question for GROUP BY

[Rob Wultsch]

On Fri, Apr 11, 2008 at 11:46 AM, Victor Danilchenko
[EMAIL PROTECTED] wrote:
 GROUP BY seems like an obvious choice; 'GROUP BY username', to be
 exact. However, this seems to produce not the last row's values, but ones
 from a random row in the group.
Under most databases your query is erroneous. Take a look at
http://lists.mysql.com/mysql/212084 .

 I don't think the fact that I am doing this on a subquery is
 relevant, but just in case, I am including this info.

 Here is what the query looks like, abridged:


  SELECT id,username,log_date,event_type
  FROM (SELECT * FROM a
   UNION
   SELECT * from b) as h
  GROUP BY username
Read 
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/


Your are probably going to end up with a fairly ugly query (mostly
because of the union) with what you have a derived table which will
join against a and b again.

SELECT if(a2.id is NULL, b2.id, a2.id) ,a1.username ...
FROM
(SELECT username, MAX(log_date) as mlog_date
FROM (SELECT * FROM a
  UNION
SELECT * from b) as h
GROUP BY username) AS a1
LEFT JOIN a AS a2  ON a1.mlog_date = a2.log_date AND username...
LEFT JOIN b AS b2 ...

Any one have a suggestion for how to do with in a way that is not ugly as heck?
-- 
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)

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

Re: SQL query question for GROUP BY

[Victor Danilchenko]

	Oooh, this looks evil. It seems like such a simple thing. I guess 
creating max(log_date) as a field, and then joining on it, is a solution 
-- but my actual query (not the abridged version) is already half a page 
long.


	I think at this point, unless someone else suggests a better solution, 
this would be easier to do programatically -- skip the group altogether, 
and instead simply order the rows, and grab the last one for each 
username in code.


	I guess another alternative would be to use a View for the UNIONized 
query, but doesn't MySQL 'fake' views in 5.0 somehow?



Rob Wultsch wrote:

On Fri, Apr 11, 2008 at 11:46 AM, Victor Danilchenko
[EMAIL PROTECTED] wrote:

GROUP BY seems like an obvious choice; 'GROUP BY username', to be
exact. However, this seems to produce not the last row's values, but ones
from a random row in the group.

Under most databases your query is erroneous. Take a look at
http://lists.mysql.com/mysql/212084 .


I don't think the fact that I am doing this on a subquery is
relevant, but just in case, I am including this info.

Here is what the query looks like, abridged:


 SELECT id,username,log_date,event_type
 FROM (SELECT * FROM a
  UNION
  SELECT * from b) as h
 GROUP BY username

Read 
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/


Your are probably going to end up with a fairly ugly query (mostly
because of the union) with what you have a derived table which will
join against a and b again.

SELECT if(a2.id is NULL, b2.id, a2.id) ,a1.username ...
FROM
(SELECT username, MAX(log_date) as mlog_date
FROM (SELECT * FROM a
  UNION
SELECT * from b) as h
GROUP BY username) AS a1
LEFT JOIN a AS a2  ON a1.mlog_date = a2.log_date AND username...
LEFT JOIN b AS b2 ...

Any one have a suggestion for how to do with in a way that is not ugly as heck?



--
Victor Danilchenko
Senior Software Engineer, AskOnline.net
[EMAIL PROTECTED] - 617-273-0119

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

Re: SQL query question for GROUP BY

[Rob Wultsch]

On Fri, Apr 11, 2008 at 1:01 PM, Victor Danilchenko
[EMAIL PROTECTED] wrote:
 Oooh, this looks evil. It seems like such a simple thing. I guess
 creating max(log_date) as a field, and then joining on it, is a solution --
 but my actual query (not the abridged version) is already half a page long.

 I think at this point, unless someone else suggests a better
 solution, this would be easier to do programatically -- skip the group
 altogether, and instead simply order the rows, and grab the last one for
 each username in code.

 I guess another alternative would be to use a View for the UNIONized
 query, but doesn't MySQL 'fake' views in 5.0 somehow?

I have used views to good results, however I have read not good things
about them. I would not be surprised if they worked well for this use.

I would also not be surprised if the merge storage engine was a better
option for you.

Possibly interesting:
http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/

-- 
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)

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

Re: Help with query, (question simplified as last mail was very complicated to understand :))

[Richard]

Richard a écrit :

Sorry about my last email which was long and not clear.
This is what I want to do

Join two tables on code table1 = code table3 where messageid = for 
example 28


table 1 contains :

 message   from   messageid
--
message1  |  code1  |28
message2  |  code1  |28
message3  |  code1  |28
message4  |  code1  |29


table 2 contains

name |  code  |  num
--
name1  |  code2  |  1
name2  |  code1  |  2
name3  |  code1  |  3

If I do :
SELECT a.message,,b.name  FROM table1 a JOIN table2 b ON a.code=b.code 
WHERE a.id='28'


I get :
message|  name
---
message1  |  name2
message2  |  name2
message3  |  name2
message1  |  name3
message2  |  name3
message3  |  name3


But all I want to get is :

message|  name
---
message1  |  name3
message2  |  name3
message3  |  name3

If I do :
SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1

I get :

name |  code  |  num
--
name3  |  code1  |  3

I now need to somehow combine the two to get :

message|  name
---
message1  |  name3
message2  |  name3
message3  |  name3

Of course I have simplified everything down to the minimum :)

Thanks in advance,

Richard


As I have had no answer I presume that what I want to do is not possible 
or my question is not well explained. Anyhow I've rethought the system 
so I do not need to keep members information and now instead of adding a 
new entry I will now just change the existing one. I won't keep old 
members information in the database but I'll still have the database 
daily backups if I need the old information.


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

RE: Help with query, (question simplified as last mail was very complicated to understand :))

[Martin Gainty]

the simple answer is and b.name='name3' ?Bon 
ChanceMartin__Disclaimer and 
confidentiality noteEverything in this e-mail and any attachments relates to 
the official business of Sender. This transmission is of a confidential nature 
and Sender does not endorse distribution to any party other than intended 
recipient. Sender does not necessarily endorse content contained within this 
transmission. Date: Sun, 30 Dec 2007 13:54:32 +0100 From: [EMAIL PROTECTED] 
To: mysql@lists.mysql.com Subject: Re: Help with query, (question simplified 
as last mail was very complicated to understand :))  Richard a écrit :  
Sorry about my last email which was long and not clear.  This is what I want 
to do   Join two tables on code table1 = code table3 where messageid = 
for   example 28   table 1 contains :   message from messageid  
--  message1 | code1 | 28  message2 
| code1 | 28  message3 | code1 | 28  message4 | code1 | 29table 2 
contains   name | code | num  --  
name1 | code2 | 1  name2 | code1 | 2  name3 | code1 | 3   If I do :  
SELECT a.message,,b.name FROM table1 a JOIN table2 b ON a.code=b.code   WHERE 
a.id='28'   I get :  message | name  ---  
message1 | name2  message2 | name2  message3 | name2  message1 | name3  
message2 | name3  message3 | name3But all I want to get is :   
message | name  ---  message1 | name3  message2 | 
name3  message3 | name3   If I do :  SELECT * FROM table2 WHERE code = 
'code1' ORDER BY num DESC LIMIT 1   I get :   name | code | num  
--  name3 | code1 | 3   I now need to 
somehow combine the two to get :   message | name  
---  message1 | name3  message2 | name3  message3 
| name3   Of course I have simplified everything down to the minimum :)  
 Thanks in advance,   Richard   As I have had no answer I presume 
that what I want to do is not possible  or my question is not well explained. 
Anyhow I've rethought the system  so I do not need to keep members information 
and now instead of adding a  new entry I will now just change the existing 
one. I won't keep old  members information in the database but I'll still have 
the database  daily backups if I need the old information.  --  MySQL 
General Mailing List For list archives: http://lists.mysql.com/mysql To 
unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] 
_
The best games are on Xbox 360.  Click here for a special offer on an Xbox 360 
Console.
http://www.xbox.com/en-US/hardware/wheretobuy/

Help with query, (question simplified as last mail was very complicated to understand :))

[Richard]

Sorry about my last email which was long and not clear.
This is what I want to do

Join two tables on code table1 = code table3 where messageid = for 
example 28


table 1 contains :

 message   from   messageid
--
message1  |  code1  |28
message2  |  code1  |28
message3  |  code1  |28
message4  |  code1  |29


table 2 contains

name |  code  |  num
--
name1  |  code2  |  1
name2  |  code1  |  2
name3  |  code1  |  3

If I do :
SELECT a.message,,b.name  FROM table1 a JOIN table2 b ON a.code=b.code 
WHERE a.id='28'


I get :
message|  name
---
message1  |  name2
message2  |  name2
message3  |  name2
message1  |  name3
message2  |  name3
message3  |  name3


But all I want to get is :

message|  name
---
message1  |  name3
message2  |  name3
message3  |  name3

If I do :
SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1

I get :

name |  code  |  num
--
name3  |  code1  |  3

I now need to somehow combine the two to get :

message|  name
---
message1  |  name3
message2  |  name3
message3  |  name3

Of course I have simplified everything down to the minimum :)

Thanks in advance,

Richard


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

Query question.

[Richard Reina]

I have a database table paycheck like this.
empno, date, gross,  fed_with
1234 2007-09-01 1153.85 108.26
1323 2007-09-01 461.54 83.08
1289 2007-09-01 1153.85   94.41
1234 2007-09-15 1153.85  108.26
1323 2007-09-15 491.94  87.18
1289 2007-09-15 1153.8594.41


I can easily do a query like this

select (SUM(gross) * .153) + SUM(fed_with) FROM paycheck where
DATE=2007-09-01;

But then I have to do a query for each pay date in the pay period.

Accordingly, what would be really useful on a day like today would be to be
able to do a query like the following:

select (SUM(gross) * .153) + SUM(fed_with) FROM paycheck where DATE IS
distinct;

Does anyone know how to do this?

Thanks for the help.

Richard

Re: Query question.

[Joerg Bruehe]

Hi Richard,


Richard Reina wrote:

I have a database table paycheck like this.
empno, date, gross,  fed_with
1234 2007-09-01 1153.85 108.26
1323 2007-09-01 461.54 83.08
1289 2007-09-01 1153.85   94.41
1234 2007-09-15 1153.85  108.26
1323 2007-09-15 491.94  87.18
1289 2007-09-15 1153.8594.41


I can easily do a query like this

select (SUM(gross) * .153) + SUM(fed_with) FROM paycheck where
DATE=2007-09-01;

But then I have to do a query for each pay date in the pay period.

Accordingly, what would be really useful on a day like today would be to be
able to do a query like the following:

select (SUM(gross) * .153) + SUM(fed_with) FROM paycheck where DATE IS
distinct;

Does anyone know how to do this?


it seems you want to use group by:
SELECT (SUM(gross) * .153) + SUM(fed_with) FROM paycheck GROUP BY date;

See here:
http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

HTH,
Joerg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

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

Re: Query question.

[Adrian Bruce]

you need to group the result sets by date, look at the manual link below:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Richard Reina wrote:

I have a database table paycheck like this.
empno, date, gross,  fed_with
1234 2007-09-01 1153.85 108.26
1323 2007-09-01 461.54 83.08
1289 2007-09-01 1153.85   94.41
1234 2007-09-15 1153.85  108.26
1323 2007-09-15 491.94  87.18
1289 2007-09-15 1153.8594.41


I can easily do a query like this

select (SUM(gross) * .153) + SUM(fed_with) FROM paycheck where
DATE=2007-09-01;

But then I have to do a query for each pay date in the pay period.

Accordingly, what would be really useful on a day like today would be to be
able to do a query like the following:

select (SUM(gross) * .153) + SUM(fed_with) FROM paycheck where DATE IS
distinct;

Does anyone know how to do this?

Thanks for the help.

Richard

  



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

Re: query question

[Adrian Bruce]

there should be no space between function name and () i.e. it should be

group_concat(hosts.name)

(unless you have the sql mode IGNORE_SPACE set)



Andrey Dmitriev wrote:

I knew I’ve seen this error before ☺

Thanks a lot.

-andrey


From: Peter Brawley [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 30, 2007 1:55 AM

To: Andrey Dmitriev
Cc: mysql@lists.mysql.com
Subject: Re: query question

  

Thanks.. It doesn't seem to work though.. I did verify I am on 5.0


Try lose the space after group_concat.

PB

Andrey Dmitriev wrote: 
Thanks.. It doesn't seem to work though.. I did verify I am on 5.0



mysql select service_names.name as 'Service',
- group_concat (hosts.name)
- from monarch.hosts as hosts, monarch.services as services, 
monarch.service_names as service_names

- where
- hosts.host_id=services.host_id
- and service_names.servicename_id=services.servicename_id
- group by service_name.name
-
-
- ;
ERROR 1305 (42000): FUNCTION mysql.group_concat does not exist

-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 29, 2007 4:00 PM

To: Andrey Dmitriev
Cc: mysql@lists.mysql.com
Subject: Re: query question

Hi,

Andrey Dmitriev wrote:
  
This is kind of achievable in Oracle in either sqlplus mode, or with 

the
  
use of analytical functions. Or in the worst case by writing a 

function.
  
But basically I have a few tables

Services, Hosts, service_names


And I can have a query something like 



select service_names.name as 'Service', hosts.name as 'Host'
from hosts, services, service_names 
where 
hosts.host_id=services.host_id 
and service_names.servicename_id=services.servicename_id 
order by service_names.name


Which outputs something like

| SSH | mt-ns4 

 
  
|
| SSH | tsn-adm-core   

 
  
|
| SSH | tsn-juno   

 
  
|
| SSH | tsn-tsn2  


However, the desired output is one line per service name, so something
like

| SSH | mt-ns4,
tsn-adm-core, tsn-juno, tsn-tsn2 |


Can this be done w/o writing procedural code in mysql?



Yes.  Have a look at GROUP_CONCAT().

Baron



  



  



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

RE: query question

[Andrey Dmitriev]

I knew I’ve seen this error before ☺

Thanks a lot.

-andrey


From: Peter Brawley [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 30, 2007 1:55 AM
To: Andrey Dmitriev
Cc: mysql@lists.mysql.com
Subject: Re: query question

Thanks.. It doesn't seem to work though.. I did verify I am on 5.0
Try lose the space after group_concat.

PB

Andrey Dmitriev wrote: 
Thanks.. It doesn't seem to work though.. I did verify I am on 5.0


mysql select service_names.name as 'Service',
- group_concat (hosts.name)
- from monarch.hosts as hosts, monarch.services as services, 
monarch.service_names as service_names
- where
- hosts.host_id=services.host_id
- and service_names.servicename_id=services.servicename_id
- group by service_name.name
-
-
- ;
ERROR 1305 (42000): FUNCTION mysql.group_concat does not exist

-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 29, 2007 4:00 PM
To: Andrey Dmitriev
Cc: mysql@lists.mysql.com
Subject: Re: query question

Hi,

Andrey Dmitriev wrote:
  
This is kind of achievable in Oracle in either sqlplus mode, or with 

the
  
use of analytical functions. Or in the worst case by writing a 

function.
  
But basically I have a few tables
Services, Hosts, service_names


And I can have a query something like 


select service_names.name as 'Service', hosts.name as 'Host'
from hosts, services, service_names 
where 
hosts.host_id=services.host_id 
and service_names.servicename_id=services.servicename_id 
order by service_names.name

Which outputs something like

| SSH | mt-ns4 

 
  
|
| SSH | tsn-adm-core   

 
  
|
| SSH | tsn-juno   

 
  
|
| SSH | tsn-tsn2  

However, the desired output is one line per service name, so something
like

| SSH | mt-ns4,
tsn-adm-core, tsn-juno, tsn-tsn2 |


Can this be done w/o writing procedural code in mysql?


Yes.  Have a look at GROUP_CONCAT().

Baron



  


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

Re: query question

[Baron Schwartz]

Hi,

Andrey Dmitriev wrote:

This is kind of achievable in Oracle in either sqlplus mode, or with the
use of analytical functions. Or in the worst case by writing a function.

But basically I have a few tables
Services, Hosts, service_names


And I can have a query something like 



select service_names.name as 'Service', hosts.name as 'Host'
from hosts, services, service_names 
where 
hosts.host_id=services.host_id 
and service_names.servicename_id=services.servicename_id 
order by service_names.name


Which outputs something like

| SSH | mt-ns4  
|
| SSH | tsn-adm-core
|
| SSH | tsn-juno
|
| SSH | tsn-tsn2  


However, the desired output is one line per service name, so something
like

| SSH | mt-ns4,
tsn-adm-core, tsn-juno, tsn-tsn2 |


Can this be done w/o writing procedural code in mysql?


Yes.  Have a look at GROUP_CONCAT().

Baron

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

query question

[Andrey Dmitriev]

This is kind of achievable in Oracle in either sqlplus mode, or with the
use of analytical functions. Or in the worst case by writing a function.

But basically I have a few tables
Services, Hosts, service_names


And I can have a query something like 


select service_names.name as 'Service', hosts.name as 'Host'
from hosts, services, service_names 
where 
hosts.host_id=services.host_id 
and service_names.servicename_id=services.servicename_id 
order by service_names.name

Which outputs something like

| SSH | mt-ns4  
|
| SSH | tsn-adm-core
|
| SSH | tsn-juno
|
| SSH | tsn-tsn2  

However, the desired output is one line per service name, so something
like

| SSH | mt-ns4,
tsn-adm-core, tsn-juno, tsn-tsn2 |


Can this be done w/o writing procedural code in mysql?

We are running ver5.

Thanks,
Andrey

RE: query question

[Andrey Dmitriev]

Thanks.. It doesn't seem to work though.. I did verify I am on 5.0


mysql select service_names.name as 'Service',
- group_concat (hosts.name)
- from monarch.hosts as hosts, monarch.services as services, 
monarch.service_names as service_names
- where
- hosts.host_id=services.host_id
- and service_names.servicename_id=services.servicename_id
- group by service_name.name
-
-
- ;
ERROR 1305 (42000): FUNCTION mysql.group_concat does not exist

-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 29, 2007 4:00 PM
To: Andrey Dmitriev
Cc: mysql@lists.mysql.com
Subject: Re: query question

Hi,

Andrey Dmitriev wrote:
 This is kind of achievable in Oracle in either sqlplus mode, or with 
the
 use of analytical functions. Or in the worst case by writing a 
function.
 
 But basically I have a few tables
 Services, Hosts, service_names
 
 
 And I can have a query something like 
 
 
 select service_names.name as 'Service', hosts.name as 'Host'
 from hosts, services, service_names 
 where 
 hosts.host_id=services.host_id 
 and service_names.servicename_id=services.servicename_id 
 order by service_names.name
 
 Which outputs something like
 
 | SSH | mt-ns4 
 
 |
 | SSH | tsn-adm-core   
 
 |
 | SSH | tsn-juno   
 
 |
 | SSH | tsn-tsn2  
 
 However, the desired output is one line per service name, so something
 like
 
 | SSH | mt-ns4,
 tsn-adm-core, tsn-juno, tsn-tsn2 |
 
 
 Can this be done w/o writing procedural code in mysql?

Yes.  Have a look at GROUP_CONCAT().

Baron



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

Re: query question

[Peter Brawley]

Thanks.. It doesn't seem to work though.. I did verify I am on 5.0


Try lose the space after group_concat.

PB

Andrey Dmitriev wrote:

Thanks.. It doesn't seem to work though.. I did verify I am on 5.0


mysql select service_names.name as 'Service',
- group_concat (hosts.name)
- from monarch.hosts as hosts, monarch.services as services, 
monarch.service_names as service_names

- where
- hosts.host_id=services.host_id
- and service_names.servicename_id=services.servicename_id
- group by service_name.name
-
-
- ;
ERROR 1305 (42000): FUNCTION mysql.group_concat does not exist

-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 29, 2007 4:00 PM

To: Andrey Dmitriev
Cc: mysql@lists.mysql.com
Subject: Re: query question

Hi,

Andrey Dmitriev wrote:
  
This is kind of achievable in Oracle in either sqlplus mode, or with 


the
  
use of analytical functions. Or in the worst case by writing a 


function.
  

But basically I have a few tables
Services, Hosts, service_names


And I can have a query something like 



select service_names.name as 'Service', hosts.name as 'Host'
from hosts, services, service_names 
where 
hosts.host_id=services.host_id 
and service_names.servicename_id=services.servicename_id 
order by service_names.name


Which outputs something like

| SSH | mt-ns4 

 
  

|
| SSH | tsn-adm-core   

 
  

|
| SSH | tsn-juno   

 
  

|
| SSH | tsn-tsn2  


However, the desired output is one line per service name, so something
like

| SSH | mt-ns4,
tsn-adm-core, tsn-juno, tsn-tsn2 |


Can this be done w/o writing procedural code in mysql?



Yes.  Have a look at GROUP_CONCAT().

Baron



  

Delete query question

[Olaf Stein]

Hey all

I am stuck here (thinking wise) and need some ideas:

I have this table:

CREATE TABLE `geno_260k` (
  `genotype_id` int(10) unsigned NOT NULL auto_increment,
  `ident` int(10) unsigned NOT NULL,
  `marker_id` int(10) unsigned NOT NULL,
  `a1` tinyint(3) unsigned NOT NULL,
  `a2` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`genotype_id`),
  KEY `ident` (`ident`),
  KEY `marker_id` (`marker_id`),
  CONSTRAINT `geno_260k_ibfk_2` FOREIGN KEY (`marker_id`) REFERENCES
`markers` (`marker_id`),
  CONSTRAINT `geno_260k_ibfk_1` FOREIGN KEY (`ident`) REFERENCES
`individual` (`ident`)
) ENGINE=InnoDB AUTO_INCREMENT=91318273 DEFAULT CHARSET=utf8


And with the following query I get 159 ident's back:

select ident from geno_260k where a1=0 group by ident having
count(a1)25;

I want to delete all records containing those idents (about 26 per ident
so 159*26).
So I thought

delete from geno_260k where ident=(select ident from geno_260k where a1=0
group by ident having count(a1)25);

But mysql can not select and delete from the same table.

Any ideas?

Thanks
Olaf



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

Re: Delete query question

[Justin]

try

SELECT * from geno_260k WHERE  ident IN (SELECT ident FROM geno_260k WHERE 
a1=0

GROUP BY ident HAVING count(a1)25);

This will give you what you're deleting first.. then if that is good. do


DELETE FROM geno_260k WHERE  ident IN (SELECT ident FROM geno_260k WHERE 
a1=0

GROUP BY ident HAVING count(a1)25);

(note the change in case is just my way of seeing things.. it's not 
necessary that I know of)



- Original Message - 
From: Olaf Stein [EMAIL PROTECTED]

To: MySql mysql@lists.mysql.com
Sent: Wednesday, September 05, 2007 9:35 AM
Subject: Delete query question



Hey all

I am stuck here (thinking wise) and need some ideas:

I have this table:

CREATE TABLE `geno_260k` (
 `genotype_id` int(10) unsigned NOT NULL auto_increment,
 `ident` int(10) unsigned NOT NULL,
 `marker_id` int(10) unsigned NOT NULL,
 `a1` tinyint(3) unsigned NOT NULL,
 `a2` tinyint(3) unsigned NOT NULL default '0',
 PRIMARY KEY  (`genotype_id`),
 KEY `ident` (`ident`),
 KEY `marker_id` (`marker_id`),
 CONSTRAINT `geno_260k_ibfk_2` FOREIGN KEY (`marker_id`) REFERENCES
`markers` (`marker_id`),
 CONSTRAINT `geno_260k_ibfk_1` FOREIGN KEY (`ident`) REFERENCES
`individual` (`ident`)
) ENGINE=InnoDB AUTO_INCREMENT=91318273 DEFAULT CHARSET=utf8


And with the following query I get 159 ident's back:

select ident from geno_260k where a1=0 group by ident having
count(a1)25;

I want to delete all records containing those idents (about 26 per 
ident

so 159*26).
So I thought

delete from geno_260k where ident=(select ident from geno_260k where a1=0
group by ident having count(a1)25);

But mysql can not select and delete from the same table.

Any ideas?

Thanks
Olaf



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

AW: Delete query question

[it]

Perhaps not the most elegant way:

- Create a temporary table
- Select-insert into the temp-table
- Use the temp-table for a delete-join or a 'NOT IN'-statement or something
like that



Hey all

I am stuck here (thinking wise) and need some ideas:

I have this table:

CREATE TABLE `geno_260k` (
  `genotype_id` int(10) unsigned NOT NULL auto_increment,
  `ident` int(10) unsigned NOT NULL,
  `marker_id` int(10) unsigned NOT NULL,
  `a1` tinyint(3) unsigned NOT NULL,
  `a2` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`genotype_id`),
  KEY `ident` (`ident`),
  KEY `marker_id` (`marker_id`),
  CONSTRAINT `geno_260k_ibfk_2` FOREIGN KEY (`marker_id`) REFERENCES
`markers` (`marker_id`),
  CONSTRAINT `geno_260k_ibfk_1` FOREIGN KEY (`ident`) REFERENCES
`individual` (`ident`)
) ENGINE=InnoDB AUTO_INCREMENT=91318273 DEFAULT CHARSET=utf8


And with the following query I get 159 ident's back:

select ident from geno_260k where a1=0 group by ident having
count(a1)25;

I want to delete all records containing those idents (about 26 per ident
so 159*26).
So I thought

delete from geno_260k where ident=(select ident from geno_260k where a1=0
group by ident having count(a1)25);

But mysql can not select and delete from the same table.

Any ideas?

Thanks
Olaf


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

Re: Delete query question

[Alex Arul Lurthu]

reply inline

On 9/5/07, Olaf Stein [EMAIL PROTECTED] wrote:

 delete from geno_260k where ident=(select ident from geno_260k where a1=0
 group by ident having count(a1)25);


When a sub query returns more than one row in a where clause, then =
should be replaced by  the  in .

-- 
Thanks
Alex
http://alexlurthu.wordpress.com

Re: Delete query question

[Olaf Stein]

Thanks,

This seems to work but that IN seems to be really slow...


On 9/5/07 9:41 AM, Justin [EMAIL PROTECTED] wrote:

 try
 
 SELECT * from geno_260k WHERE  ident IN (SELECT ident FROM geno_260k WHERE
 a1=0
  GROUP BY ident HAVING count(a1)25);
 
 This will give you what you're deleting first.. then if that is good. do
 
 
 DELETE FROM geno_260k WHERE  ident IN (SELECT ident FROM geno_260k WHERE
 a1=0
  GROUP BY ident HAVING count(a1)25);
 
 (note the change in case is just my way of seeing things.. it's not
 necessary that I know of)
 
 
 - Original Message -
 From: Olaf Stein [EMAIL PROTECTED]
 To: MySql mysql@lists.mysql.com
 Sent: Wednesday, September 05, 2007 9:35 AM
 Subject: Delete query question
 
 
 Hey all
 
 I am stuck here (thinking wise) and need some ideas:
 
 I have this table:
 
 CREATE TABLE `geno_260k` (
  `genotype_id` int(10) unsigned NOT NULL auto_increment,
  `ident` int(10) unsigned NOT NULL,
  `marker_id` int(10) unsigned NOT NULL,
  `a1` tinyint(3) unsigned NOT NULL,
  `a2` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`genotype_id`),
  KEY `ident` (`ident`),
  KEY `marker_id` (`marker_id`),
  CONSTRAINT `geno_260k_ibfk_2` FOREIGN KEY (`marker_id`) REFERENCES
 `markers` (`marker_id`),
  CONSTRAINT `geno_260k_ibfk_1` FOREIGN KEY (`ident`) REFERENCES
 `individual` (`ident`)
 ) ENGINE=InnoDB AUTO_INCREMENT=91318273 DEFAULT CHARSET=utf8
 
 
 And with the following query I get 159 ident's back:
 
 select ident from geno_260k where a1=0 group by ident having
 count(a1)25;
 
 I want to delete all records containing those idents (about 26 per
 ident
 so 159*26).
 So I thought
 
 delete from geno_260k where ident=(select ident from geno_260k where a1=0
 group by ident having count(a1)25);
 
 But mysql can not select and delete from the same table.
 
 Any ideas?
 
 Thanks
 Olaf
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 





-
Olaf Stein
DBA
Center for Quantitative and Computational Biology
Columbus Children's Research Institute
700 Children's Drive
phone: 1-614-355-5685
cell: 1-614-843-0432
email: [EMAIL PROTECTED]


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

Re: Delete query question

[Olaf Stein]

Thanks baron,

I will try this just for test purposes as I already wrote a script, which is
slow but not as bad as using IN()

Olaf


On 9/5/07 3:29 PM, Baron Schwartz [EMAIL PROTECTED] wrote:

 IN() subqueries in MySQL are badly optimized.  It's usually better to
 use a JOIN, even though it's non-standard:
 
 DELETE geno_260k.*
 FROM geno_260k
 INNER JOIN (
SELECT ident FROM geno_260k
WHERE a1=0
GROUP BY ident HAVING count(*)25
 ) AS der USING(ident);
 
 Try profiling this and see if it's faster.  It probably will be on any
 reasonably large data set, as long as the table has an index on ident.
 
 Note I changed the COUNT(a1) to COUNT(*) for efficiency.  Counting a
 column counts the number of values (e.g. non-null).  Counting * just
 counts the number of rows and can be faster.  COUNT(*) is what you want
 to use 99% of the time.
 
 Regards
 Baron
 
 Olaf Stein wrote:
 Thanks,
 
 This seems to work but that IN seems to be really slow...
 
 
 On 9/5/07 9:41 AM, Justin [EMAIL PROTECTED] wrote:
 
 try
 
 SELECT * from geno_260k WHERE  ident IN (SELECT ident FROM geno_260k WHERE
 a1=0
  GROUP BY ident HAVING count(a1)25);
 
 This will give you what you're deleting first.. then if that is good. do
 
 
 DELETE FROM geno_260k WHERE  ident IN (SELECT ident FROM geno_260k WHERE
 a1=0
  GROUP BY ident HAVING count(a1)25);
 
 (note the change in case is just my way of seeing things.. it's not
 necessary that I know of)
 
 
 - Original Message -
 From: Olaf Stein [EMAIL PROTECTED]
 To: MySql mysql@lists.mysql.com
 Sent: Wednesday, September 05, 2007 9:35 AM
 Subject: Delete query question
 
 
 Hey all
 
 I am stuck here (thinking wise) and need some ideas:
 
 I have this table:
 
 CREATE TABLE `geno_260k` (
  `genotype_id` int(10) unsigned NOT NULL auto_increment,
  `ident` int(10) unsigned NOT NULL,
  `marker_id` int(10) unsigned NOT NULL,
  `a1` tinyint(3) unsigned NOT NULL,
  `a2` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`genotype_id`),
  KEY `ident` (`ident`),
  KEY `marker_id` (`marker_id`),
  CONSTRAINT `geno_260k_ibfk_2` FOREIGN KEY (`marker_id`) REFERENCES
 `markers` (`marker_id`),
  CONSTRAINT `geno_260k_ibfk_1` FOREIGN KEY (`ident`) REFERENCES
 `individual` (`ident`)
 ) ENGINE=InnoDB AUTO_INCREMENT=91318273 DEFAULT CHARSET=utf8
 
 
 And with the following query I get 159 ident's back:
 
 select ident from geno_260k where a1=0 group by ident having
 count(a1)25;
 
 I want to delete all records containing those idents (about 26 per
 ident
 so 159*26).
 So I thought
 
 delete from geno_260k where ident=(select ident from geno_260k where a1=0
 group by ident having count(a1)25);
 
 But mysql can not select and delete from the same table.
 
 Any ideas?
 
 Thanks
 Olaf
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
 
 
 
 
 -
 Olaf Stein
 DBA
 Center for Quantitative and Computational Biology
 Columbus Children's Research Institute
 700 Children's Drive
 phone: 1-614-355-5685
 cell: 1-614-843-0432
 email: [EMAIL PROTECTED]
 
 


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

Re: Delete query question

[Baron Schwartz]

IN() subqueries in MySQL are badly optimized.  It's usually better to 
use a JOIN, even though it's non-standard:


DELETE geno_260k.*
FROM geno_260k
   INNER JOIN (
  SELECT ident FROM geno_260k
  WHERE a1=0
  GROUP BY ident HAVING count(*)25
   ) AS der USING(ident);

Try profiling this and see if it's faster.  It probably will be on any 
reasonably large data set, as long as the table has an index on ident.


Note I changed the COUNT(a1) to COUNT(*) for efficiency.  Counting a 
column counts the number of values (e.g. non-null).  Counting * just 
counts the number of rows and can be faster.  COUNT(*) is what you want 
to use 99% of the time.


Regards
Baron

Olaf Stein wrote:

Thanks,

This seems to work but that IN seems to be really slow...


On 9/5/07 9:41 AM, Justin [EMAIL PROTECTED] wrote:


try

SELECT * from geno_260k WHERE  ident IN (SELECT ident FROM geno_260k WHERE
a1=0
 GROUP BY ident HAVING count(a1)25);

This will give you what you're deleting first.. then if that is good. do


DELETE FROM geno_260k WHERE  ident IN (SELECT ident FROM geno_260k WHERE
a1=0
 GROUP BY ident HAVING count(a1)25);

(note the change in case is just my way of seeing things.. it's not
necessary that I know of)


- Original Message -
From: Olaf Stein [EMAIL PROTECTED]
To: MySql mysql@lists.mysql.com
Sent: Wednesday, September 05, 2007 9:35 AM
Subject: Delete query question



Hey all

I am stuck here (thinking wise) and need some ideas:

I have this table:

CREATE TABLE `geno_260k` (
 `genotype_id` int(10) unsigned NOT NULL auto_increment,
 `ident` int(10) unsigned NOT NULL,
 `marker_id` int(10) unsigned NOT NULL,
 `a1` tinyint(3) unsigned NOT NULL,
 `a2` tinyint(3) unsigned NOT NULL default '0',
 PRIMARY KEY  (`genotype_id`),
 KEY `ident` (`ident`),
 KEY `marker_id` (`marker_id`),
 CONSTRAINT `geno_260k_ibfk_2` FOREIGN KEY (`marker_id`) REFERENCES
`markers` (`marker_id`),
 CONSTRAINT `geno_260k_ibfk_1` FOREIGN KEY (`ident`) REFERENCES
`individual` (`ident`)
) ENGINE=InnoDB AUTO_INCREMENT=91318273 DEFAULT CHARSET=utf8


And with the following query I get 159 ident's back:

select ident from geno_260k where a1=0 group by ident having
count(a1)25;

I want to delete all records containing those idents (about 26 per
ident
so 159*26).
So I thought

delete from geno_260k where ident=(select ident from geno_260k where a1=0
group by ident having count(a1)25);

But mysql can not select and delete from the same table.

Any ideas?

Thanks
Olaf



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








-
Olaf Stein
DBA
Center for Quantitative and Computational Biology
Columbus Children's Research Institute
700 Children's Drive
phone: 1-614-355-5685
cell: 1-614-843-0432
email: [EMAIL PROTECTED]




--
Baron Schwartz
Xaprb LLC
http://www.xaprb.com/

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

Re: Query question

[Anoop kumar V]

Can you post your table definitions and some sample data.

Also what is the end requirement - how should the end result look like?

Anoop

On 4/23/07, Clyde Lewis [EMAIL PROTECTED] wrote:


Guys,

I have the following table that contains some information about a
cars. I'm trying to write a query to determine:
the number of make(name of car), number of models per make(name of
car) and the average number of models/make(name of car) sold in a
particular period.

The two queries below can provide me with what I need, but am stumped
on how to combine the results.

Any help would be greatly appreciated.

table name cars:
columns:
make
model
sold_date

select make,count(make) from cars where make in(Nissan, Toyota,
Honda) group by make

select model,count(model) from cars where make in(Nissan, Toyota,
Honda) group by model



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

Query question

[Clyde Lewis]

Guys,

I have the following table that contains some information about a 
cars. I'm trying to write a query to determine:
the number of make(name of car), number of models per make(name of 
car) and the average number of models/make(name of car) sold in a 
particular period.


The two queries below can provide me with what I need, but am stumped 
on how to combine the results.


Any help would be greatly appreciated.

table name cars:
columns:
make
model
sold_date

select make,count(make) from cars where make in(Nissan, Toyota, 
Honda) group by make


select model,count(model) from cars where make in(Nissan, Toyota, 
Honda) group by model




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

Query Question

[Aaron Clausen]

I have a couple of very simple tables to handle a client signin site:

The client table has the following fields:
 client_id int(11) primary key auto_increment
 first_name char(90)
 last_name char(90)

The signin table has the following fields
 record_id int primary key auto_increment
 client_id int
 date datetime

Essentially, the client enters his id and it creates a record in the
signin table.

I need a query that can identify all the clients who signed in for the
first time during a specific month.  I have fought this one for a
couple of days now and just can't seem to get it.

--
Aaron Clausen   [EMAIL PROTECTED]

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

Re: Query Question

[Baron Schwartz]

Hi Aaron,

Aaron Clausen wrote:

I have a couple of very simple tables to handle a client signin site:

The client table has the following fields:
 client_id int(11) primary key auto_increment
 first_name char(90)
 last_name char(90)

The signin table has the following fields
 record_id int primary key auto_increment
 client_id int
 date datetime

Essentially, the client enters his id and it creates a record in the
signin table.

I need a query that can identify all the clients who signed in for the
first time during a specific month.  I have fought this one for a
couple of days now and just can't seem to get it.


I think you can break the problem into a couple of steps:

1) find the first login for each client.
2) eliminate all but the ones in the month.

You can't do step 2 first because that would destroy your knowledge of 
whether a signin record is a client's first.


   select client_id, min(date) from signin group by client_id;

Now you know the first time each client signed in.  From here you can go 
several ways.  One is to just add a HAVING clause.


   select client_id, min(date) from signin
   group by client_id
   having min(date) between ? and ?;

I hope that helps,
Baron

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

Query Question

[Behrang Saeedzadeh]

Hi,

Suppose that there are two tables book and author:

book

id
title
author_id

author
-
od
title

I want a query that returns all the books, but if there are more than
3 books with the same author_id, only 3 should be returned. For
example if this is the contents of the book table:

(1, Book 1, 10)
(2, Book 2, 10)
(3, Book 3, 10)
(4, Book 4, 10)
(5, Book 1, 11)

these rows should be returned:

(1, Book 1, 10)
(2, Book 2, 10)
(3, Book 3, 10)
(5, Book 1, 11)

Any ideas how can I query this?

Thanks in advance,
Behrang Saeedzadeh


--

Behrang Saeedzadeh
http://www.jroller.com/page/behrangsa
http://my.opera.com/behrangsa

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