Working with multiple tables

[conquest]

Ok here is the scenario:

 

I have two tables,

1.charis_admin_module_allocation (id, allocation_id, id_module_title, id_page_name, sort_order)

2. charis_admin_module_content  (id, title, content, visible)

 

Module allocation is used to hold the page name that it will be placed on and the sort order it will appear in.

Module Content holds all information referring to the content that will be printed in that module.

 

I need to call all fields from module content and order by Module allocation.sort_order

 

Currently I have this sql which does call the content but does not sort by sort_order.

 

SQL INFO

CREATE TABLE IF NOT EXISTS `charis_admin_module_content` (
  `id` int(25) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `content` text NOT NULL,
  `visible` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Used to list modules' AUTO_INCREMENT=5 ;

 

CREATE TABLE IF NOT EXISTS `charis_admin_module_allocation` (
  `id` int(25) NOT NULL AUTO_INCREMENT,
  `allocation_id` int(25) NOT NULL,
  `id_module_title` text NOT NULL,
  `id_page_name` text NOT NULL,
  `sort_order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;

 

SELECT DISTINCT * FROM charis_admin_module_content WHERE charis_admin_module_content.title IN (SELECT charis_admin_module_allocation.id_module_title
FROM charis_admin_module_allocation WHERE id_page_name = '$current_page_name' OR id_page_name = 'ALL' ORDER BY sort_order IN (SELECT charis_admin_module_allocation.sort_order)ASC)

 

Any help is appreciated.

 

Many Thanks.

Robert

[Adam]

Why are you using a sub-query to select the field..? Also I find it more readble to break up and indent the query, and also assign aliases:

 

SELECT DISTINCT * FROM charis_admin_module_content c
WHERE c.title IN (
    SELECT a.id_module_title FROM charis_admin_module_allocation a
    WHERE a.id_page_name = '$current_page_name'
    OR a.id_page_name = 'ALL'
    ORDER BY a.sort_order ASC
)

[Adam]

I'm not actually sure why you're using a sub-query here. This can be done using a faster join, and without the need for the duplicate 'title' column:

 

SELECT c.* FROM charis_admin_module_content c
JOIN charis_admin_module_allocation a
ON (c.id = a.id);
ORDER BY a.sort_order;

 

Not tested that but it should work.

 

You can remove the `id_module_title` column from the content table now. Also I'd suggest removing the auto increment from the content table's ID column, in-case the data ever gets out of sync or you wish to change the ID to a specific value.

[Adam]

Whoops, missed off your where clause:

 

SELECT c.* FROM charis_admin_module_content c
JOIN charis_admin_module_allocation a
ON (c.id = a.id)
WHERE a.id_page_name = '$current_page_name'
OR a.id_page_name = 'ALL'
ORDER BY a.sort_order;

 

That was a little syntax error too.