Re: Need help with query

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=2

Re: Need help with query

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 COU

Re: Need help with query

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 distinc

Re: Need help with query

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 oth

Re: Need help with query

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

Re: Need help with query

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

Re: Need help with query

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

Re: Need help with query

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 instal

Need help with query

Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815

Re: Need help with query optimization

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 , M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType,

Re: Need help with query optimization

Can you please show us the indexes on both the tables. regards anandkl On Tue, Mar 16, 2010 at 11:47 PM, Jesse wrote: > I have the following query: > > SELECT TAP.ID , M.UID, TAP.FirstName, TAP.MI, > TAP.LastName, TAP.State, > TAP.SchoolName, TAP.StateApproved, TAP.DiplomatAppro

Need help with query optimization

I have the following query: SELECT TAP.ID, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Pts.TotPoints FROM TorchAwardParticipants TAP JOIN Members M On M.ID=TAP.CurrentMemberID JOIN Chapters

Re: need help with query...

From: Andy Shellam To: Lamp Lists 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) in select statement &

Re: need help with query...

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 abo

Re: need help with query...

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

Re: need help with query...

From: Andy Shellam To: Lamp Lists 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 p.first_name AS person_first_name

RE: need help with query...

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

RE: need help with query...

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

Re: need help with query...

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, o.org_depar

Re: need help with query...

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 say

need help with query...

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 say 3 tables people, organization, addresses. and they are

Re: Need help with query

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

Re: Need help with query

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

Re: Need help with query

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 Camp

Need help with query

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 CampCount.BusID=B.ID LEFT J

RE: Need help with query. Please!

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

Re: Need help with query. Please!

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:

Need help with query. Please!

I have the following 3 tables: table 1: companies fields 1: company_id, company name, status Sample data: 1 , company 1 , 0 2 , company 2 , 1 3 , company 3 , 1 4 , company 4 , 0 5 , company 5 , 1 table 2: groups fields 2: group_id, group_name Sample data: 1 , Group 1 2 , Group 2 3 , Group 3 4 ,

Recall: Need help with query result

Memon, Michele would like to recall the message, "Need help with query result". - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list ar

Need help with query result

Hello Everyone, I haven't used SQL in a long time and I am having trouble designing the query to get the result I need. I have 1 select query that looks like this: SELECT WC.FIRST_NAME & ' ' & WC.LAST_NAME AS NAME, C.COMM_NO AS PHONE FROM WRK_CNTRCTR AS WC, WRK_COMM_NUMBER AS C WHERE WC.PERS_ID =