Join only if field is not null, then sort

I am not sure if this is even possible, I’ve tried and can’t get it to work at all…

I will try to explain by examples as I think I can illustrate my problem better that way…

The db table:


CREATE TABLE `content` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) NOT NULL,
  `master_id` int(12) NOT NULL,
  `sort` int(3) NOT NULL,
  `title` varchar(128) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Example data…


INSERT INTO `content` VALUES (1, 0, 0, 1, 'Page 1');
INSERT INTO `content` VALUES (2, 0, 0, 2, 'Page 2');
INSERT INTO `content` VALUES (3, 2, 0, 1, 'Page 2a');
INSERT INTO `content` VALUES (4, 2, 0, 2, 'Page 2b');
INSERT INTO `content` VALUES (5, 2, 0, 3, 'Page 2c');
INSERT INTO `content` VALUES (6, 0, 0, 3, 'Page 3');
INSERT INTO `content` VALUES (7, 6, 5, 1, '');
INSERT INTO `content` VALUES (8, 6, 4, 2, '');
INSERT INTO `content` VALUES (9, 6, 0, 2, 'Page 4');

id | parent_id | master_id | sort | title

1 | 0 | - | 1 | Page 1
2 | 0 | - | 2 | Page 2
3 | 2 | - | 1 | Page 2a
4 | 2 | - | 2 | Page 2b
5 | 2 | - | 3 | Page 2c
6 | 0 | - | 3 | Page 3
7 | 6 | 5 | 1 | -
8 | 6 | 4 | 3 | -
9 | 6 | - | 2 | -

The sql should output:

Page 1
Page 2

  • Page 2a
  • Page 2b (master page)
  • Page 2c (master page)
    Page 3
  • Page 2c (cloning Page Page 2c)
  • Page 4
  • Page 2b (cloning Page Page 2b)

So,

A ‘cloned’ page basically takes the information from the master’s page, I’ve done this as the same page can fall under several other pages throughout the site, in each ‘parent’ page, the order will be different too.

If i haven’t explained this clearly, please shoot me some questions and I will try clarify

thanks for the quick reply…

yes, its php :slight_smile:

don’t even ~think~ of trying to produce this output with SQL :slight_smile:

use your application language (php?) – that’s what it’s there for