How to get different information from many-to-many relation tables?

Question

2.00/5 (1 vote)

See more:

Hi there, i have a small database, and 3 tables there. Also there is a "many to many" connection. Look at this:

SQL

CREATE TABLE `categories` (
  `id` int(11) NOT NULL,
  `Name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SQL

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `Products` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

middle table...

SQL

CREATE TABLE `category_product` (
  `id_category` int(11) NOT NULL,
  `id_product` int(11) NOT NULL,
  PRIMARY KEY (`id_category`,`id_product`),
  KEY `fk_Category` (`id_category`),
  KEY `fk_Product` (`id_product`),
  CONSTRAINT `fk_Category` FOREIGN KEY (`id_category`) REFERENCES `categories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_Product` FOREIGN KEY (`id_product`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Please explain how to do queries like "Select all products of x category" or "Add new category and products there"...How to do that?

Posted 24-Mar-13 8:32am

Je7

Add a Solution

Comments

Sudhakar Shinde 25-Mar-13 7:00am

1) To select all products of x category you need to join category_product table with products table on and filter condition on id_category.

2) Insert statement using existing category and product since there are foreign keys defined on these.

Please mention your expectation as I am not convinced with your question.

Je7 25-Mar-13 9:10am

Thanks for answer. For example, i used this query: SELECT `Products` FROM `products` WHERE `id`=ANY(SELECT `id_product` FROM `category_product` WHERE `id_category` = 3); To select all the products of x category, and this query satisfy me. Another types of queries I don't try, I'll try them some later. But I have a little question...maybe there is another example how to create many-to-many relationships in tables...maybe without middle table. And there is my variant of query is good?

Sudhakar Shinde 25-Mar-13 22:02pm

This is the most common way to design M2M relationship. Another form of the query can be..
SELECT A.id_product, B.products
FROM category_products A, products B
WHERE A.id_products = B.id
AND A.id_category = '3';

Je7 26-Mar-13 12:05pm

I've never seen this syntax before. Can you explain how this query should work, becouse there are a lot of errors, when i trying to execute query. I mean that i don't know about using letters at table names in query.

Richard C Bishop 2-Apr-13 11:59am

The lettes are just an alias to shorten the amount you have to type.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)