Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Current Situation
We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product.
Example:

id | competitors | some infos about competitor        | product  
---------------------------------------------------------------
1  | C_1         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_1  
2  | C_2         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_1  
3  | C_3         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_1  
4  | C_1         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_2  
5  | C_4         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_2  

Every 30 minutes we read in the new competitors. To do so we first delete everyone with the specific product (P_1) and insert afterwards the new ones. So there will never be a competitor in our DB, which isnt at the moment listed for this product.

When we got the new infos, we do some kind of research with the products.
(We also have a table called p with ca. 100,000 rows. In that table we store the infos about the products.)

So back to the research process. We run through every product an have to get the competitors with their infos. For this purpose we have do do a mysql request like SELECT * FROM c WHERE product LIKE "P_1";. Not to say that this is not very performant.

My Goal
I want to achieve, that with only one query i got the product information from p an also the competitor ones from c where the product id is the same. So that these infos get stored in an array like this:

[0]['id'] => '2'  
[0]['product_identifier'] => 'P_1'  
[0]['c'][0]['competitors'] => 'C_1'  
[0]['c'][1]['competitors'] => 'C_2'  
[0]['c'][3]['competitors'] => 'C_3'

Possible Solution
To get the needed information with just one query, i came up with this solution.

In table c the product row contains a foreign key which links to the p table. That would be very easy to realise. But I am asking myself if it would be possible to delete c or p rows easaly. I never worked that intensive with foreign keys - seemed to me always a bit error generating.

And I also wonder if I could make a join that easy, cause I want from p the related competitors and not the other way around. You know what I mean? Not sure if I can do that this way. And if I could, how to tread this foreign keys. What do I have looking for in order to not get messed up with an unclean database.

Example
We will use our table c from above, where c.product stores the foreign key to our table p.product_identifier. Like this:

id | product_identifier | some infos about the product          
---------------------------------------------------------------
1  | P_1                | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  
2  | P_2                | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  
3  | P_3                | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  

I am not sure how this works out or if it's even a good idea, but how has my query look like? If i want to SELECT every product from p and also their competitors from c?

share|improve this question
    
can you post the exact table information? This way people can help you better. –  Ruud van de Beeten Dec 7 '12 at 13:27
    
Well there is no foreign key relationship between this two tables yet. c.product has an index, but no foreign key. –  Michael Hirn Dec 7 '12 at 13:32
add comment

2 Answers

up vote 2 down vote accepted

First, if your tables are InnoDB, and column c.P_ID is the reference of column p.P_ID (note actual column names may be different), then you absolutely should use foreign keys to avoid orphans, but know that you will have to explicitly state ON DELETE CASCADE, since the default is ON DELETE SET NULL, retaining orphans.

But this doesn't really have anything to do with the 'single query' to select the products and information about the competitors.

To achieve:

I want to achieve, that with only one query i got the product information from p an also the competitor ones from c where the product id is the same.

your query would use a join:

SELECT products.P_ID, products.product_name, competitors.info
 FROM products
 LEFT JOIN competitors ON competitors.P_ID=products.P_ID

This will get you repeat information for the products (competitor info will be different). And then you will use your application to parse that info into the array you need.

If you want a single row for the products, you can use the GROUP_CONCAT function for the competitors:

SELECT products.P_ID, products.product_name, 
  GROUP_CONCAT(competitors.info SEPARATOR ',') as competitor_info
 FROM products
 LEFT JOIN competitors ON competitors.P_ID=products.P_ID
 GROUP BY products.P_ID;

A major caveat you should be aware of: ON UPDATE|DELETE CASCADE does not fire triggers on the rows that were deleted/updated by the cascade:

Note

Currently, cascaded foreign key actions do not activate triggers.

This shouldn't stop you from using CASCADE if you want to remove orphan rows. But you should be aware of it.

share|improve this answer
    
Thanks a lot. Query works like i expected. Ruud van de Beeten posted similar. Both works. Only Problem i got, which is not very handy for my application, is that for every copmetitor there will be a single row. So the product_identifier is redundant, which he should not. I think i will need the result, like i posted under "my goal". Is this possible? –  Michael Hirn Dec 7 '12 at 14:27
    
@MichaelHirn added a query that uses GROUP_CONCAT. That should get you in the right direction. –  Derek Downey Dec 7 '12 at 14:42
    
Thanks. Yeah GROUP_CONCAT goes in the right direction. I guess there is no other solution to get such a multi dimensional array? –  Michael Hirn Dec 7 '12 at 14:48
1  
No, you need to use your application to generate the multi-dimensional array from the resultset. –  Derek Downey Dec 7 '12 at 14:50
add comment

Don't be scared to create a foreign key in a database. One of the biggest reasons why you should use a foreign key is to enforce referential integrity. Depending on your foreign key it can be possible to use the CASCADE option to delete all competitors for a product when you delete a product (so it makes it easy to manage).

If you set your tables and foreign key up properly the DELETE statement to delete a product and its competitors should something like:

DELETE FROM Product WHERE Product.Product_identifier = [insert your identifier here];

For an example about the DELETE CASCASE option of a foreign key I suggest you read the following article: http://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/

To list a product and all competitors you can use:

SELECT Product.product_identifier, Competitors.[some infos about competitor]
FROM Product
LEFT JOIN Competitors 
   ON Product.Product_identifier = Competitors.Product 
WHERE Product.Product_identifier = [insert your identifier here];
share|improve this answer
    
So when i delete in p a product, every row in c gets also deleted where the product_identifier is the same? I only have to define the c.product as CASCADE? Ok, sounds great. But how about the query to do this. What kind of join do i need? Looks to me, that this is no a kind of default join, because i came from the other table. –  Michael Hirn Dec 7 '12 at 13:29
    
@MichaelHirn I updated my answer. –  Ruud van de Beeten Dec 7 '12 at 13:37
    
Thank you. I also updaed, the question, because i think my overall topic got a little bit lost. I added the goals to see what my real question is about. But thanks for this infos, i think i will also need them. –  Michael Hirn Dec 7 '12 at 13:42
    
No problem ;). I like helping where I can. I've read your edited question. What are you trying to accomplish with the array? –  Ruud van de Beeten Dec 7 '12 at 13:46
    
Thanks ;) With the array i just wanted to show what my output should look like, for better understanding. I will make two test tabels and heck this out, if it works like i expected it and come back and tell how it worked out. So far thanks and i will vote up. –  Michael Hirn Dec 7 '12 at 13:59
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.