Re: Two tables

[Christian Mack]

Hi Ben-Nes

What you have here is a classical N:M relation.
This means you have multiple products which belong to the same category and multiple
categories which belong to the same product.

You can use two tables (with redundancy), but I would use three tables.
1) Product table
2) Category table
3) Link table which consists only of Product.id (prod_id) and Category.id (cat_id)

And then get the desired product with category name with:
SELECT 
	Product.id
	, Product.name
	, Product.prize
	, ...
	, Category.name
FROM
	Product
	, Link
	, Category
WHERE
	Product.id = Link.prod_id
	AND Link.cat_id = Category.id
	AND Product.name = 'desired'

Tschau
Christian

PS: Sorry for the late answer, I was really busy.

Ben-Nes Michael wrote:
> 
> Ok
> 
> so if i want to make product table and category table where product can be in 1
> category to 10 (product can have more then 1 category).
> So i need to do 2 tables one is product with all the detail and second table for
> category with two columns 1 product ID and second category ?
> Is it possible to make some connection between them hard/soft link or something ?
> 
> Philip Hallstrom wrote:
> 
> > In article <37BC63EE.560F0278@stripped> you write:
> > >Hi
> > >
> > >I want two tables that will share the same key and if i will delete a
> > >product from one table the second table will be affected too.
> > >
> > >is it possible ?
> > >where can i read more on it ?
> >
> > I'm pretty sure this isn't possible with mysqql since it doesn't support
> > FORIEGN KEYS or triggers.  The manual actually has a section on why this is the
> > case for F.keys... I think they might add triggers at some point.
> >
> > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"
> 
> --
> Ben-Nes Michael - Manager