FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


need help with mysql joins





a_dubDesign
Alright everybody, need some help with joins in mysql. Here's a run down of what I have. The number in song1-5 of ylw_weeks table is the id from the yls_songs table

table 1: 'yls_weeks':
id | week | song1 | song2 | song3 | song4 | song 5
-------------------------------------------------------------------------
1 | 9-18-2006 | 1 | 2 | 3 | 4 | 5
-------------------------------------------------------------------------
2 | 9-11-2006 | 1 | 2 | 3 | 4 | 5


table 2 'yls_songs':
id | name | lyrics | audio
----------------------------------------------------------------
1 | song1Name | ~~~~~ | pathToAudioFile
----------------------------------------------------------------
2 | song2Name | ~~~~~ | pathToAudioFile
and so on...

what I need to get is the weeks.week, and for week.song1-5 I need to get the name and audio from the songs table while still having the id.

I'm pretty sure I'm suppose to use a type of join, but I'm having issues getting it right, please help.
yy1124
a_dubDesign wrote:
Alright everybody, need some help with joins in mysql. Here's a run down of what I have. The number in song1-5 of ylw_weeks table is the id from the yls_songs table

table 1: 'yls_weeks':
id | week | song1 | song2 | song3 | song4 | song 5
-------------------------------------------------------------------------
1 | 9-18-2006 | 1 | 2 | 3 | 4 | 5
-------------------------------------------------------------------------
2 | 9-11-2006 | 1 | 2 | 3 | 4 | 5


table 2 'yls_songs':
id | name | lyrics | audio
----------------------------------------------------------------
1 | song1Name | ~~~~~ | pathToAudioFile
----------------------------------------------------------------
2 | song2Name | ~~~~~ | pathToAudioFile
and so on...

what I need to get is the weeks.week, and for week.song1-5 I need to get the name and audio from the songs table while still having the id.

I'm pretty sure I'm suppose to use a type of join, but I'm having issues getting it right, please help.


In my opinion this is not a recommended table stucture, maybe you should change it?

maybe you can try these, thats all I can think of to work with the current structure, I am not sure if they work:

To get them in 1 row:
Quote:
SELECT w.week,
s1.id, s1.name, s1.audio,
s2.id, s2.name, s2.audio,
s3.id, s3.name, s3.audio,
s4.id, s4.name, s4.audio,
s5.id, s5.name, s5.audio,
FROM yls_weeks w,
yls_songs s1,
yls_songs s2,
yls_songs s3,
yls_songs s4,
yls_songs s5
WHERE w.song1 = s1.id
AND w.song2 = s2.id
AND w.song3 = s3.id
AND w.song4 = s4.id
AND w.song5 = s5.id


To get them in different row:

Quote:
SELECT w.week, s.id, s.name, s.audio
FROM yls_weeks w, yls_songs s
WHERE w.song1 = s.id
OR w.song2 = s.id
OR w.song3 = s.id
OR w.song4 = s.id
OR w.song5 = s.id;
a_dubDesign
yy1124 wrote:
In my opinion this is not a recommended table stucture, maybe you should change it?

care to give any suggestions?
yy1124
table 1: 'yls_weeks':
id | week
-------------------------------------------------------------------------
1 | 9-18-2006
-------------------------------------------------------------------------
2 | 9-11-2006
...

table 2 'yls_songs':
id | name | lyrics | audio
----------------------------------------------------------------
1 | song1Name | ~~~~~ | pathToAudioFile
----------------------------------------------------------------
2 | song2Name | ~~~~~ | pathToAudioFile
...

table3: yls_somename
row_id | week_id | songs_id
-----------------------------------------------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 1 | 4
5 | 2 | 4
6 | 2 | 3

In this case you can have n number of songs for one and any different week.

query will be something like:
SELECT w.week, s.id, s.name, s.audio
FROM yls_weeks w, yls_songs s, yls_somename newtbl
WHERE newtbl.week_id = w.id
AND newtnbl.song_id = s.id


Just a suggestion.
Related topics
mysql/php help please
Help Installing MySQL on SuSE Linux using ssh
Help on Backing up MySQL Databases
Help with mysql query
Need help understanding MySQL "Resource" type retu
Problem with MySQL Server Please Help!!
PHP/Mysql - beginner!
mySQL set-up on Frihost help please
Setup php,mysql and others...
What exactly does indexing mysql do?
MySQL HELP!!
I need help! MySQL version ploblem
Optimizing MySQL joins
[help]Mysql & php problem
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.