[thelist] Multi-level data storage

[Simon Coggins]

> there's only one primary key in the second table, but it's a composite key,
> consisting of two columns
> are all unique, even though taken individually, each column has duplicates
> 
> because it's a primary key, no set of any two numbers can be duplicated

Okay, so you can use a composite primary key to link two tables together
in a many-to-many situation. But how do you construct a query when the
data is stored in two tables.

For example, I have these two tables:

    create table Links (
           LinkID bigint(21) NOT NULL auto_increment,
           Url varchar(255) NOT NULL,
           LinkName varchar(64) NOT NULL,
           Description varchar(255) NOT NULL,
           PRIMARY KEY (LinkID),
           UNIQUE (Url)
           );

    create table LinkCats (
           LinkID bigint(21) NOT NULL,
           CatID bigint(21) NOT NULL,
           PRIMARY KEY (LinkID, CatID),
           );


With some sample contents:

LinkID	Url		LinkName	Description
1	www.url.com	URL.com		description 1
2	www.url2.com	URL2.com	description 2
3	www.url3.com	URL3.com	description 3

LinkID	CatID
1	1
2	1
2	2
3	2

So in this case url2.com appears in both Categories, right?

Now to get all the links in a category I would usually use something like
this:

SELECT Url,LinkName,Description FROM Links WHERE CatID $CatID

but now the information is split between two tables.

I could see how this could be achived with two queries (get the linkID by
a query on table 2, then feed the matching linkID's into a query on table
1).

Does MySQL syntax include an easier or more efficient way to do this?


Simon