Anyone know any SQL?

[Tigger]

Got me a bit of a problem with a site I'm doing...

I've got 2 tables in a MySQL database:

tbl_Course:
-course_ID
-course_Name
-course_Module1
-course_Module2
-course_Module3

tbl_Modules:
-module_ID
-module_Name
-module_Description

The course_Module[n] fields contains a number which refer to a module_ID in the other table. I need to construct an SQL query which returns the names of the modules for each of the modules in the course.

I know how to do this if there was only 1 module for each course but haven't a clue how to get it to look up 3 different names from the same table.

Any help would be greatly appreciated.

(I'm using PHP & MySQL in Dreamweaver MX2004)

 

[brixtonite]

select * from tbl_x where module_name = x

or summat like that

there's also a an embedded SQL query in most RDMSs that returns the table names in a given table... hmmmm.

 

[fugjostle]

tbh honest mate, that table structure your using sucks. I'd rethink the table structure and use an intermediate table, it will make your life a whole lot easier.

I'll have a ponder but my brains fried at the moment. I think your going to have to use sub queries like "SELECT * from (select * from tbl_courses) WHERE course LIKE '%$search%'" kind of thing. Not sure you can have mutliple joins? but I could be wrong?

Have a look at http://www.tomjewett.com/dbdesign/dbdesign.php?page=multijoin.php&imgsize=medium

 

[Tigger]

tbh honest mate, that table structure your using sucks. I'd rethink the table structure and use an intermediate table, it will make your life a whole lot easier.

I'll have a ponder but my brains fried at the moment. I think your going to have to use sub queries like "SELECT * from (select * from tbl_courses) WHERE course LIKE '%$search%'" kind of thing. Not sure you can have mutliple joins? but I could be wrong?

Have a look at http://www.tomjewett.com/dbdesign/dbdesign.php?page=multijoin.php&imgsize=medium

Cheers bud, i was hoping to be able to avoid using an intermediate table cos that buggers up the course creator admin system I built but its looking very much like thats gonna be the only way to get the damn thing working!