2011/03/15 17:51 -0500, LAMP
Let's say there is a table orders (simplified, of course)
CREATE TABLE orders (
item_id int,
org_id int,
) ENGINE=MyISAM
Need to select all (distinct) org_id they have item_id 34, 36, 58 and
63. All of them, not only some of them.
Result is org_id=2607 and
Hi!
I think that the query that you have proposed is the best possible for the
problem.
However, if there are duplicates in the orders table, then
HAVING COUNT(item_id) = 4
should be replaced with
HAVING COUNT(DISTINCT item_id) = 4
(I assume that you meant item_id and not org_id in the
On Mar 17, 2011, at 3:01 PM, Geert-Jan Brits wrote:
Indeed, I don't thing there is.
Just be sure that each record has an unique combination of org_id
and item_id, otherwise you might end up with an org_id that, for
example, references 4 times item_id 34 in 4 different records, but
no
2011/03/18 08:49 -0500, LAMP
Is here anybody from mysql development team, to suggest to build IN
ALL function?
There is a problem here: the basic operation is on the record, each record by
each record, all by itself. The solution to your problem entails acting on more
distinct records until
Yes, that was my question. Though, since English is not my first
language, let me try to post it again:
There is a list of all orgs and items org bough, from table called
orders
item_idorg_id
342607
342607
341520
362607
361520
368934
38
What I need is a list of orgs they bought all of items 34, 36, 58,
63. every of them.
Some solutions under What else did buyers of X buy at
http://www.artfulsoftware.com/infotree/queries.php.
PB
---
On 3/17/2011 12:00 PM, LAMP wrote:
Yes, that was my question. Though, since English is not
First I was thinking there is function IN ALL or something like that,
since there are functions IN and EXISTS. And I would be able to make a
query something like this
select distinct org_id, item_id
from orders
where item_id in all (34, 36, 58, 63)
order by org_id asc
But, there
On Mar 15, 2011, at 6:18 PM, Rhino wrote:
All you should need is this:
select distinct org_id
from orders
where item_id in (34, 36, 58, 63)
I'm assuming that the DISTINCT operator is available in the version
of MySQL that you are using. I don't currently have any version of
MySQL
It may only be returning 51 rows but its having to read significantly more.
Get rid of the derived table join if possible. Something like
SELECT TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI, TAP.LastName,
TAP.State,
TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType,
Can you please show us the indexes on both the tables.
regards
anandkl
On Tue, Mar 16, 2010 at 11:47 PM, Jesse j...@msdlg.com wrote:
I have the following query:
SELECT TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI,
TAP.LastName, TAP.State,
TAP.SchoolName, TAP.StateApproved,
On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:
I hate when somebody put in Subject line something like I just did
but after 15 minutes to try to be specific just with one short
sentence - I gave up. So, you can hate me - I understand (though,
help with my problem too) :-)
I have let
Hi Afan
Why not prefix your field names with the table name?
select
p.first_name AS person_first_name,
p.last_name AS person_last_name,
p.status AS person_status,
p.date_registered AS person_date_registered,
o.org_id AS organization_org_id,
o.org_name AS organization_org_name,
-Original Message-
From: Lamp Lists [mailto:lamp.li...@yahoo.com]
Sent: Wednesday, December 17, 2008 2:57 PM
To: mysql@lists.mysql.com
Subject: need help with query...
...snip...
I have let say 3 tables people, organization, addresses. and they are
linked to each other with column
-Original Message-
From: Andy Shellam [mailto:andy-li...@networkmail.eu]
Sent: Wednesday, December 17, 2008 3:29 PM
To: Lamp Lists
Cc: mysql@lists.mysql.com
Subject: Re: need help with query...
Hi Afan
Why not prefix your field names with the table name?
select
p.first_name
From: Andy Shellam andy-li...@networkmail.eu
To: Lamp Lists lamp.li...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:29:08 PM
Subject: Re: need help with query...
Hi Afan
Why not prefix your field names with the table name?
select
Jerry Schwartz wrote:
-Original Message-
From: Andy Shellam [mailto:andy-li...@networkmail.eu]
Sent: Wednesday, December 17, 2008 3:29 PM
To: Lamp Lists
Cc: mysql@lists.mysql.com
Subject: Re: need help with query...
Hi Afan
Why not prefix your field names with the table name?
select
Hi,
Hi Andy,
the reason I can't use this because fields (columns) in select
statement (p.first_name, p.last_name,...) are actually dynamically
created. In my project different client will select different fields
to be shown. 99% will select first_name, and last_name, but some don't
care
From: Andy Shellam andy-li...@networkmail.eu
To: Lamp Lists lamp.li...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:48:31 PM
Subject: Re: need help with query...
Hi,
Hi Andy,
the reason I can't use this because fields (columns
Hi Saul,
I need to use C++ and I'm not writing a web application.
Thanks anyway.
Kandy
I have done queries to the database in PHP with variables like month but
easily can select from a range of time and data to produce the same
results, the output goes directly to the web so if that is what
Hi Kandy,
this could be the query you are looking for. It should return record
with the closest timestamp to your required time:
(SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM
table1 t
WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column DESC LIMIT 1
)
Kandy Wong wrote:
Hi Saul,
I need to use C++ and I'm not writing a web application.
Thanks anyway.
you can do something like:
select min(abs(timediff(targettime,timestamp))) from table where
condition ;
if you use the libmysql you can get the result as strings back (the method i
Kandy Wong wrote:
And what is the good connector (C++ to MySQL) to use?
MySQL++ (http://tangentsoft.net/mysql++/) has native Date, Time, and
DateTime data types. You can convert to these types implicitly:
mysqlpp::DateTime dt = row[my_column];
Row::operator[] doesn't return
PROTECTED]
Sent: Friday, August 15, 2008 2:36 AM
To: Saul Bejarano
Cc: mysql@lists.mysql.com
Subject: Re: Need help to query with timestamp in C++
Hi Saul,
I need to use C++ and I'm not writing a web application.
Thanks anyway.
Kandy
I have done queries to the database in PHP with variables like month
Hi Dusan,
Thank you so much. It works!
Kandy
Hi Kandy,
this could be the query you are looking for. It should return record
with the closest timestamp to your required time:
(SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM
table1 t
WHERE timestamp_column =
I have done queries to the database in PHP with variables like month but
easily can select from a range of time and data to produce the same
results, the output goes directly to the web so if that is what you are
seeking for, I can help with PHP.
Saul
Kandy Wong wrote:
Hi,
I need to write
Guys, just wanted to thank you again for helping me with
the sql statement that I needed. I was able to sorted using
php and I was able to display the correct result.
Thanks again!!1
Nestor :-)
On Nov 6, 2007 7:37 AM, Néstor [EMAIL PROTECTED] wrote:
You guys are correct, that is exactly what
--- [EMAIL PROTECTED] wrote:
Néstor wrote:
I think you'd best begin by normalising your
database. Something along
these lines:
very true indeed, that would save you major headaches
when right after finishing the demo, someone would
say, can we add a 18 gallon pledge? what about a 25?
You guys are correct, that is exactly what happened.
I must thing of this in the future.
At this moment I have a lot of other projects to take care, that it is
eaiser for me to read the information into an associative array with
the columns and the values and sort the array and then print the
--- Enrique Sanchez Vela [EMAIL PROTECTED]
wrote:
Date: Mon, 5 Nov 2007 15:01:59 -0800 (PST)
From: Enrique Sanchez Vela [EMAIL PROTECTED]
Subject: Re: Need help creating query statement
To: Néstor [EMAIL PROTECTED]
--- Néstor [EMAIL PROTECTED] wrote:
I can do simple select
Enrique that is pretty good and close to what I need.
On top of what you have generously provide, I guess I can just put
each gallon field into an array an then sort the array to display the
first 5 gallon fields per agency. Is there an easier way?
Thanks,
Nestor :-)
On 11/5/07, Enrique
Néstor wrote:
I can do simple select statements but I need your brains to create query
statment.
I am using mysql 4.0 in my 1and1 site.
I have a table that has 8 fields, one is the agency field and the other 7
are
*tip* values on saving water and the value of this field is either 0 or an
Hi Jesse,
Jesse wrote:
The following query works just fine:
SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity,
COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity
FROM Buses B
LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID)
AS CampCount ON
COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT
Duuuh. Why didn't I think of that. What is MySQL's issue with referring to
the variables (As assignments, whatever you want to call them)? I've had
issues like this in situations like this one, when trying to use them in the
Jesse wrote:
COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT
Duuuh. Why didn't I think of that. What is MySQL's issue with
referring to the variables (As assignments, whatever you want to
call them)? I've had issues like this in situations like this one,
when trying to
Your original query (implicit INNER JOINs):
SELECT teu.name, eca.owner_id, ece.value
FROM typed_enterprise_unit teu,
e_contact_association eca,
e_contact_entry ece
WHERE teu.unit_id=eca.owner_id
and eca.entry_id=ece.entry_id
and eca.type_id=68
and
On 15 Jul 2004 at 6:27, Patrick Drouin [EMAIL PROTECTED]
wrote:
I'm having a hard time with the following query. It
retrieves about 3K rows from a few tables. One of them
contains over 40M rows. When run on a 3Ghz server with
1G of RAM it returns the rows in more than 1 mini. I
don't think
Bonjour Arnaud,
--- Arnaud [EMAIL PROTECTED] wrote:
On 15 Jul 2004 at 6:27, Patrick Drouin
Your indexes look good, but I see that you have some
varchar fields.
Maybe
you could run an optimize table on these tables?
I'm running it at the moment, I will follow-up on the
list when it's done.
What version of MySQL are you using? Have you checked the cardinality on
these tables?
-Original Message-
From: Patrick Drouin
To: [EMAIL PROTECTED]
Sent: 7/15/04 8:27 AM
Subject: Need help optimizing query
Hello everyone,
I'm having a hard time with the following query. It
retrieves
Hello Victor,
What version of MySQL are you using? Have you
checked the cardinality on
these tables?
Problem solved! Optimizing the table brought the query
time down to 17 secs Wow!
Thanks for the input Victor and merci to Arnaud for
the quick fix.
Patrick
Try:
SELECT C.company_id, C.company_name
FROM companies C
LEFT JOIN company_group_intersect CG
ON (C.company_id=CG.company_id AND CG.group_id='1')
WHERE C.status='1' AND CG.company_id IS NULL
--
Diana Soares
On Fri, 2003-12-05 at 15:08, Robert Hughes wrote:
I have the following 3 tables:
That worked perfectly!!! Thanks so much :-)
-Original Message-
From: Diana Soares [mailto:[EMAIL PROTECTED]
Sent: Friday, December 05, 2003 10:31 AM
To: Robert Hughes
Cc: [EMAIL PROTECTED]
Subject: Re: Need help with query. Please!
Try:
SELECT C.company_id, C.company_name
FROM
* John Kelly
I have a table of full URLs and IPs and am using the following
query to return
distinct web requests by domain. Using SUBSTRING_INDEX it only returns the
domain part of the URL:
SELECT SUBSTRING_INDEX(url, '/', 3) as topsites, count(distinct
ip) as count
from tablename WHERE
]
: Cc: [EMAIL PROTECTED]
: Subject: Re: Need help constructing query ...
:
:
: - Original Message -
: From: Daniel Clark [EMAIL PROTECTED]
: To: [EMAIL PROTECTED]
: Cc: [EMAIL PROTECTED]
: Sent: Tuesday, October 21, 2003 2:33 PM
: Subject: Re: Need help constructing query
Hi, I have a table full of logged urls and ip addresses. The following
query returns all the urls and the number of requests. How would I
modify it to return unique requests based on distinct ip addresses?
select url, count(*) as pageviews from table group by url order by
pageviews desc
- Original Message -
From: Daniel Clark [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 2:33 PM
Subject: Re: Need help constructing query ...
: Hi, I have a table full of logged urls and ip addresses. The following
: query returns all
Then I think you want
SELECT url, COUNT(DISTINCT ip_address)
FROM tablename
GROUP BY url;
-Original Message-
From: John Kelly [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 3:45 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Need help constructing query
: -Original Message-
: From: John Kelly [mailto:[EMAIL PROTECTED]
: Sent: Tuesday, October 21, 2003 3:45 PM
: To: [EMAIL PROTECTED]
: Cc: [EMAIL PROTECTED]
: Subject: Re: Need help constructing query ...
:
:
: - Original Message -
: From: Daniel Clark [EMAIL PROTECTED
Patrick Shoaf [EMAIL PROTECTED] wrote:
I have a table name product defined as follows:
Item_Code
Item_Size
Item_Color
Item_img
Description
Cost
Retail_Price
Category
and other non-essential items, such as qty based on code,size,color
The data is as follows:
j2400 S BLK
I loaded MySQL 4.1, but now the text is gibberish in the Group_Concat field
SELECT Cat_Item_Img,Cat_Price,Product.Item_Code,GROUP_CONCAT(Item_Size
SEPARATOR ;) as sizes,Item_Img,
Description,Category,Retail_Price,Short_Desc,Product.Item_Color
FROM Cat_Items,Product
WHERE cat_code =BoltTech and
Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html
Saqib Ali
-
http://www.xml-dev.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Absolutely! I have multiple indexes. I think it might be a problem with ODBC
Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html
Saqib Ali
-
http://www.xml-dev.com
-
Visit CARMEL MUSIC ENTERTAINMENT website
When following query is pulled up, it takes about whole 1-2 minutes to come up.
inquiries table has 17000 records, contacts has about 7000,
individual_contacts has about 16000.
It has gotten worse once I upgraded to 4.0 and latest MyODBC.
Clients are separate machines (mix of Win98 and WinXP).
: Wednesday, August 20, 2003 2:52 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Need help optimizing query, awfully slow on only
2 records
When following query is pulled up, it takes about whole 1-2
minutes to come up. inquiries table has 17000 records,
contacts
Query takes 3.4 seconds to run on the server, but it takes 1-2minutes to run via
MyODBC 3.51.06 using passthrough (Access97 is the front end, but it has query
type that allows bypass of Access interpretation.
Two Questions:
Is the same query running directly on the linux server thru mysql is
To: Michael S. Fischer
Cc: [EMAIL PROTECTED]
Subject: RE: Need help optimizing query, awfully slow on only
2 records
Result of EXPLAIN is:
table|type|possible_keys|key|key_len|ref|rows|Extra
inquiries|ALL|contact_id| | | |8253|Using filesort
individual_contacts|eq_ref
]
Sent: Wednesday, August 20, 2003 3:14 PM
To: Michael S. Fischer
Cc: [EMAIL PROTECTED]
Subject: RE: Need help optimizing query, awfully slow on only
2 records
Result of EXPLAIN is:
table|type|possible_keys|key|key_len|ref|rows|Extra
inquiries|ALL|contact_id| | | |8253
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Need help optimizing query, awfully slow on only
2 records
When following query is pulled up, it takes about whole 1-2
minutes to come up. inquiries table has 17000 records,
contacts has about 7000, individual_contacts has about
57 matches
Mail list logo
