SQL linking tables

help

[Andrew Thomas]

Ive created two Access tables
a type_table and a Category_table

There can be one type and many categories to that type

eg Type = Shops
Category = Sports, Clothes, Food etc

Ive created a SQL to display all the Types in a selection box, once clicked how do i display all the related categories of that type in another selection box

thanks

[DerOle]

select * from category_table where category like '%sport%'

would select all the rows where the category contains "sport"

[AlbinoRhyno]

You need to have a id in both tables, with the type id being unique and the category id being a reference to the type. e.g.

Type
1 Balls
2 Shoes
...

Category
Football 1
Baseball 1
Soccerball 1
Football Shoe 2
Bowling Shoe 2
...

Then, you can "SELECT Category_Name FROM Category Where id = variable_name", and pass in the id of the Type that the person clicked on.

[ergophobe]

Andrew,

If you really want to use the power of a relational DB, you should have three tables here, not two, as follows

=== 
table: type 
id type-name 
1  Shops 
2  Galleries 
table: category 
id cat-name 
1  Sports 
2  Clothes 
3  Paintings 
4  Shoes 
5  Frames 
table: cats-to-types 
type-id cat-id 
1   1 
1   2 
1   4 
2   3 
2   5 

Now

SELECT c.cat-name
FROM type t, category c, cats-to-types ctt
WHERE t.type-name LIKE 'Shops' AND t.id = ctt.type-id AND ctt.cat-id = c.cat-name

This lets you have as many or as few categories per type, expands indefinitely, can be normalized and so on and so on.

Tom

[Andrew Thomas]

Thanks for the advice, I wasnt sure if my tables were setup correcly. So i think i'll take your advice and change the table structure to yours