paging SQL experts

[ralph4100]

Hello!

I have to tables that are similar in important respects but different enough that I want them independent of each other ([b]flag_requests[/b] and [b]tour_requests[/b]). The tables [b]flag_notes[/b] and [b]tour_notes[/b] keep track of user submitted notes regarding the rows in [b]flag_requests[/b] and [b]tour_requests[/b], respectively.

What I want is to select the rows in [b]flag_requests[/b] and [b]tour_requests[/b] that have zero rows in their respective [b]flag_notes[/b] and [b]tour_notes[/b] tables, and order the results by a column [b]added[/b] (datetime) which exists in both [b]flag_requests[/b] and [b]tour_requests[/b]. In other words, the unaddressed requests, as per their having no notes about them.

My pathetic attempt:

[code]SELECT * FROM flag_requests, tour_requests
WHERE COUNT(
            SELECT * FROM flag_notes, tour_notes
            WHERE flag_notes_request_id=flag_requests.id OR tour_notes.request_id=tour_requests.id
)=0
ORDER BY added[/code]

the tables:

[code]CREATE TABLE `flag_notes` (
  `id` int(11) NOT NULL auto_increment,
  `office_id` int(11) default NULL,
  `user_id` int(11) default NULL,
  `party_id` int(11) default NULL,
  `request_id` int(11) default NULL,
  `added` datetime default NULL,
  `body` text,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

CREATE TABLE `flag_requests` (
  `id` int(11) NOT NULL auto_increment,
  `office_id` int(11) default NULL,
  `party_id` int(11) default NULL,
  `date_to_be_flown` date default NULL,
  `date_needed_by` date default NULL,
  `flown_for` varchar(150) default NULL,
  `occassion` varchar(250) default NULL,
  `special_instructions` text,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

CREATE TABLE `tour_notes` (
  `id` int(11) NOT NULL auto_increment,
  `office_id` int(11) default NULL,
  `user_id` int(11) default NULL,
  `party_id` int(11) default NULL,
  `request_id` int(11) default NULL,
  `added` datetime default NULL,
  `body` text,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=52 ;

CREATE TABLE `tour_requests` (
  `id` int(11) NOT NULL auto_increment,
  `party_id` int(11) NOT NULL,
  `type` int(11) default NULL,
  `confirmed` datetime default NULL,
  `cancelled` varchar(200) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;[/code]
                     

[fenway]

Well, you can LEFT JOIN ... iS NULL each pair of tables... you then want to combine them how?