-1

This is the data structure:

stores          { id, name, city_id, owner_id }
cities          { id, name }
store_managers  { user_id, store_id }
products        { id, name, store_id }
users           { id, name }
_______________________________________
#note: many users can manage the same store, and the same user can manage 
 many stores.

I need to get, in one query:

  1. The stores that owned by the user that its id I will provide. [from stores, when owner_id = some_number]
  2. For each store, the city that is attached too. [from cities]
  3. The users that have manage privileges to the store. [from store_managers]
  4. the COUNT of products that is assigned to each store.

I need it to be in one query. The DB is MYSQL

fixed, added the missing foreign key my mistake, thanks

yossi
  • 3,090
  • 7
  • 45
  • 65
  • 7
    What have you tried? What _exactly_ are you having difficulties with? We will not write your queries for you, not without you showing us your efforts so far. – Oded Jan 15 '12 at 21:29
  • +1 Oded. Is this homework? we can give you guidance if it is... – Michael Durrant Jan 15 '12 at 21:31
  • 1
    I think there's a connection missing between the cities and the stores... – Quasdunk Jan 15 '12 at 21:34
  • 1
    draw it out on paper. use a square (or whatever) for each of the 5 entites, e.g. 'stores' then draw arrow between using the information you have about which "has many" of which. this is a start towards understanding the foreign keys you'll be using. – Michael Durrant Jan 15 '12 at 21:34
  • 1
    agree with Quasdunk. Missing foreign key at least. That's where drawing it ot visually might help too ;) – Michael Durrant Jan 15 '12 at 21:36
  • (#) Well, thanks for those of you who actually answered to the matter. @Quasdunk @Durrant @Nail (##) This is a basic structure that I am using with cakePHP, For this query I want to avoid using the built-in association mechanism. (###) Simple join gave me nothing that i can use without coding.. and GROUP CONCAT, as I understood - will return a string of `store_managers` instead of an array as I need. (####) hope it's clearer now. – yossi Jan 15 '12 at 21:53

1 Answers1

1

Assuming that each store is only attached to one city and you forgot the foreign key, it might look something like this (MySQL specific as I learned before):

SELECT s.id, GROUP_CONCAT(u.name SEPARATOR ', ') as users,
COUNT(p.*) AS product_count
FROM stores s
JOIN city c ON s.city_id = c.id
JOIN store_managers sm ON sm.store_id = s.id
JOIN users u ON u.id = sm.user_id
JOIN products p ON p.store_id = s.id
WHERE s.owner_id = ?
GROUP BY s.id

However, you already see that it’s not good fetching all things together, as you can only retrieve the names within one string or you will get several rows containing the same store_id. Unless you really want the names in a concatenated list, you have to explode the again which will lead to problems as soon as a name contains a comma. If you do not group, you have to iterate over all rows with PHP later and manipulate them. Sometimes it’s better to make two queries, but that depends on how you need the names of the users.

Often when putting to much stuff into one single query you will suffer from problems later. In this case everything is still manageable, but as soon as you want mulitple counts at once it can get dangerous.

Can still contain some mistakes, just written directly without testing.

aufziehvogel
  • 7,167
  • 5
  • 34
  • 56
  • THANKS! I agree with the concept of two queries instead of one, but I can't see how can I get this done when I have 100 stores from the first query and than i need to query each one of them for the store_managers. have I got it wrong? – yossi Jan 15 '12 at 22:27
  • 1
    @yossi: Yeah, if I think correctly now, you would have 100 queries for the names of the owners then. You could also do one query with `WHERE store_id IN (1,2,3,4,5)`, but then you would have to split the result for each store_id again (several rows with the same store_id). So it would be the same. – aufziehvogel Jan 15 '12 at 22:33