[thelist] Multi-level data storage
Simon Coggins
ppxsjc1 at unix.ccc.nottingham.ac.uk
Wed Feb 28 07:42:53 CST 2001
> 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
More information about the thelist
mailing list