joining tables

CrusaderSean

right now i'm having problems with sql query. I'm trying to join two tables. the first table has studio1, studio2, studio3, studio4 columns. and the values are the id of another table. the other table has id and studio name. when i try to join by

select * from table1, table2 where table1.studio1=table2.id and table1.studio2=table2.id and table1.studio3=table2.id and table1.studio4=table2.id;

it doesn't work because most entries in table1 don't have four values, so most of them only have studio1 value while the other three are null. so when joining it won't return any results unless it's an entry from table1 that has all four studio values that can be matched up with the id of table2. I tried doing or instead of and but that returns wrong results. so what type of query command should I use? is there an easier way to join? I think I'm doing it the wrong way, or perhaps I didn't setup my table correctly? Originally I was going to go with SET column for the studio and just select the values instead of using two tables, but I found out there were way too many values to be inserted into the SET so it won't let me.

Lars_Berg

Yes, your tables are not set up optimally.

Use three tables

table1 -- don't know what it contains
id -- primary key
other columns

studio

studioId -- primary key
name
other columns

crossRefTable

id -- foreign key to table1
studioID -- foreign key to studio

In this way there can be as many studios as you like whereas you are limited to 4 with your model.

(you can get it to work with your present design using outer join and multiple instances of the studio table but that is not recommended.)

CrusaderSean

how does this foreign key thing work? I looked at the manual and they gave a simple example. but their example is basically

table1

id
name

table2

stuff...
owner references to table1(id)

but for mine, (if my column was called owner), i would need to reference to multiple owners and you can't do that with the manuals example.

as for a separate cross reference table, i'm not sure if my mysql supports it... what types of table do they have to be? all of my tables are MyISAM and i don't see any options to reference something in phpMyAdmin. (i inputted the manual's example through console so it worked).

if i can make those tables, i'm still don't understand how the third table will fix the problem. and isn't it the same thing as this?

table1

id
studioID foreign key to studio(id)

studio

id
studioname

and in this one, you can only have one studio per table1.id

but you said if i did it with your setup i can have multiple studio per table1.id. please explain in a little more detail how it works, sorry for the trouble.

Lars_Berg

A cross reference table is just an ordinary table it is just a term used in this context.

If you need a cross reference table depends on whether you have a one to many relationship or a many to many relationship.

table1

id
studioID foreign key to studio(id)

studio

id
studioname

With this type of table setup you have a one to many relationship.

An entity in the first table can only use one studio but one studio can be used by different entities. (A bit bothersome using the term entity, let's call it person henceforth.)

A many to many relationship is where one person can use many studios and one studio can be used by many persons.

In the many to many example, each column in the primary key of the cross reference table is a foreign key to the tables that are part of the relation.

create table user(id int auto_increment ,name varcahr(10), primary key(id))

create table studio(id int auto_increment ,name varcahr(10), primary key(id))

create table studioUsage(userid int references user on delete cascade, studioid int references studio on delete cascade, primary key(userid,studioid))

The Mysql support for foreign keys is very weak. It is only supported if you use INNODB as table handler and features such as cascading deletes, is not supported at all.