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 20
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
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> sel
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 | NA
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 |
|
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, severi
> 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? Shou
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.
>
>
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 some
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 "201
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 t
ossamt;
Thanks ,
abhisehk choudhary
www.tech4urhelp.blogspot.com
From: Stefan Kuhn
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 w
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 t
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
> F300H
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)
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
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)
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
> Hi everyone and thanks in advance for the help. I have a query that I'd
>
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 eve
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
ifang
-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
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-0
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
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, 20
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 wrot
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` var
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,
`p
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 Quantity>0 bef
I'm trying to produce a report that will tell me how many products were
available with a Quantity>0 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 Ma
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
For the given table:
FIELD TYPE COLLATION NULL
KEY DEFAULT Extra PRIVILEGES COMMENT
-
-- -- --- -- ---
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
> Hi Ian,
>
> Why do you think something's wrong? Here is my test data and
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 |
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,
`vi
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 pr
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> S
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\
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
...@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
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 wrote:
> I am in need of some help for the
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 row
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:
¢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 Table
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.
-
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 Ma
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 item
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, 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
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 tb
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_ke
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
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)
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
>-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 loa
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
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
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
@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), brea
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
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 t
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
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
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 = t
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)?
DELE
> 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 D
> -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 ke
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..
>
> Tabl
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 table
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,
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
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
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
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, o
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
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,
th
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 i
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
r 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, (questi
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
--
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 |
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"
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
[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
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 ca
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
->
-> ;
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
o:[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 w
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 servic
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', host
;
(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"
Sent: Wednesday, September 05, 2007 9:35 AM
Subject: Delete query question
Hey
at 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
things.. it's not
> necessary that I know of)
>
>
> - Original Message -
> From: "Olaf Stein" <[EMAIL PROTECTED]>
> To: "MySql"
> Sent: Wednesday, September 05, 2007 9:35 AM
> Subject: Delete query question
>
>
>> Hey all
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" .
--
Th
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
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"
Sent: Wednesday, September 05, 2007 9:35 AM
Su
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
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
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 belo
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 primar
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 i
Hi Baron,
Please remember to reply to the list so others can read and benefit from
answers to
your questions. Also, though I don't care tremendously one way or another,
many people
think it's good form to place your response after the message instead of before
(I tend
to follow the pattern s
1 - 100 of 503 matches
Mail list logo