join tables problem

[janim]

hi guys

i have trouble in joining tables together

lets say i have two tables : singers and songs .

********

singers table :

id  name

1  eminem

2  akon

3  shaggy

 

********

songs table:

id  song

1  smack that

2  sexy

3  lose your self

4  till i collapse

5  lonely

 

etc....

 

so how can i join those tables ????

every singer has a lot of songs and every song has a lot of singers too

let's take example here

 

smack that by eminem  and akon so i can't put another row in the songs table for the singer name

please help this is gonna kill me

 

 

[revraz]

You either need a singer field in the songlist or a song field in the singer list.  Then you can join them by ID.  Singer list in the songlist would probably be better.

 

Or a third table that has song and singers in the fields.

[janim]

thank you revraz for your replay

but i don't think it could be done this way

couse there are alot of songs for every singer

and i want to link this entries later

 

 

[revraz]

That's how you link them.  You need to make a connection somehow.

 

How else are you going to determine what singer sang what song?

[janim]

yes

that's what i'm asking about !!!!

[wayne]

As Revraz has said you need to add further details

 

singers table :

id  name

1  eminem

2  akon

3  shaggy

 

********

songs table:

id  song    name

1  smack that  eminem

2  smack that  akon

3  sexy

4  lose your self

5  till i collapse

6  lonely

 

 

select * from singers a, songs b

where name a = name b

 

etc....

[revraz]

There is no magic wand that will help you.  Link them either in your existing table or make a new one to link them.

 

yes

that's what i'm asking about !!!!

[janim]

yes i'm little confused about this

let;s say i will put another filed in songs table for the singers 

in some row there is 3 singers then how can i link it ? ???

 

[revraz]

You would either have to list it 3 times or use a third table:

 

songs Table

id  |  name

 

artist Table

id  |  name

 

Listing Table

song.id  |  artist.id

[fenway]

You would either have to list it 3 times or use a third table:

 

songs Table

id   |   name

 

artist Table

id   |   name

 

Listing Table

song.id   |   artist.id

3 times? No!  Use a third many-to-many table as suggested.