Aggregate Query

Hi all, Could do with some help please. I have a query that grabs details of items that have been ordered from an ecommerce site. Order details are in tracking and ordered items in trackitem. The query works fine and generates a row for each item, including bits of info retrieved from

Totalling Counts done in Subqueries

Hi all, I'm hoping someone can help me with this please. Is there a way to total counts done in subqueries? So I want to do: -=-=-=-=-=-=-= SELECT `Notes`.`note_id`, `Notes`.`last_updated_datetime`, `Notes`.`event_date`, `Notes`.`subject`, `Notes`.`summary`, `Notes`.`content`, (SELECT

Re: Totalling Counts done in Subqueries

Thanks Nigel and Peter, I went for Nigel's solution below. Both very useful, learnt a lot, thank you. Cheers, Nigel nigel wood wrote: Is there a way to total counts done in subqueries? Never done this but my educated guess is: SELECT `Notes`.`note_id`, `Notes`.`last_updated_datetime`,

SELECT of records that have a matching record in a many to many table

Can someone please help me with this one? I'm trying to SELECT from a table only those records that have a record, matching a search term, in a table related by a many to many relationship. The many to many relationship is in a mapping/junction table. Here's an example of what I have so

Re: Two COUNTs in one query

Thanks to everyone who helped me out with this, just what I needed and this is now working for me. One further question... I'm using a subquery as suggested: SELECT `Organisations`.`organisation_id`, `Organisations`.`name`, (SELECT COUNT(*) FROM `Notes__Organisations` WHERE

Two COUNTs in one query

I think I probably can't do what I want, but am hoping I'm wrong. Please help :) I have three tables: Organisations - organisation_id - name - - 1 - Org A - - 2 - Org B -

Re: Getting single results per (left) record with INNER JOIN

that that will be more efficient. Cheers, Nigel On Fri, Mar 13, 2009 at 3:24 PM, Nigel Peck nigel.p...@miswebdesign.comwrote: Nigel Peck wrote: SELECT `People`.`person_id`, `People`.`name` FROM `People` INNER JOIN `Person_postal_addresses` ON `Person_postal_addresses`.`person_id` = `People

Re: Getting single results per (left) record with INNER JOIN

Johan De Meersman wrote: Either HAVING, or an additional GROUP BY field of person_postal_address.person_id should do, I think. That's great, thanks, just what I needed :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:

Getting single results per (left) record with INNER JOIN

I'm hoping someone can point me in the right direction for what I need, to save me trawling through books and Google when I don't know what I'm looking for. I'm using an INNER JOIN to query a table that has a one-to-many relationship with the table in my FROM clause, but I only want one

Re: Getting single results per (left) record with INNER JOIN

Nigel Peck wrote: ... My query is: SELECT `People`.`person_id`, `People`.`name`, FROM `People` INNER JOIN `Person_postal_addresses` ON `Person_postal_addresses`.`person_id` = `People`.`person_id` WHERE `People`.`name` REGEXP 'example' OR `Person_postal_addresses

JOIN - Which and how?

Hi all, I'm having trouble figuring out how to construct a query and am hoping someone can help... I have three tables: Raw_materials +++---+ | raw_mat_id | name | count | +++---+ | 1 |

Re: JOIN - Which and how?

Ananda Kumar wrote: did u try this select * from raw_materials where raw_mat_id not in (select raw_mat_id from raw_materials__Products); Hi Ananda, Thanks for this, I'm really looking to do this in a single SELECT, for efficiency. I'm sure it's possible with a join but just not sure how.

Re: JOIN - Which and how?

Nigel Peck wrote: Ananda Kumar wrote: did u try this select * from raw_materials where raw_mat_id not in (select raw_mat_id from raw_materials__Products); Hi Ananda, Thanks for this, I'm really looking to do this in a single SELECT, for efficiency. I'm sure it's possible with a join

Re: JOIN - Which and how?

Nigel Peck wrote: Sorry, make that: SELECT `Raw_materials`.`raw_mat_id`, `Raw_materials`.`name` FROM `Raw_materials` LEFT JOIN `Raw_materials__Products` ON `Raw_materials`.`raw_mat_id` = `Raw_materials__Products`.`raw_mat_id` WHERE