Miscellany

Let's say I have a database like this:

table items
  item_id
  item_title

table swaps
  swap_id
  swap_item_id

table swap_fors
  swap_fors_swap_id
  swap_fors_item_id

swaps relates to swap_for like swaps.swap_id = swap_fors.swap_fors_swap_id
items relates to swaps like swaps.swap_item_id = items.item_id
items relates to swap_fors like swap_fors.swap_fors_item_id = items.item_id


I want a query to return ALL the relevant items. Example:
  I ask for all the data relating to swap_id 1.
  I would expect to get:
    item id 2, Call of Duty 4 (this was specified at swaps.swap_item_id)
    item id 4, Mass Effect (this was specified at swap_fors.swap_fors_item_id)
    item id 5, Forza Motorsport (this was specified at swap_fors.swap_fors_item_id)



Here's a dump if you want to play around


CREATE DATABASE `test`;
USE `test`;

CREATE TABLE `items` (
  `item_id` int(11) NOT NULL auto_increment,
  `item_title` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`item_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ;


INSERT INTO `items` VALUES (1, 'Halo 3');
INSERT INTO `items` VALUES (2, 'Call of Duty 4');
INSERT INTO `items` VALUES (3, 'Guitar Hero III');
INSERT INTO `items` VALUES (4, 'Mass Effect');
INSERT INTO `items` VALUES (5, 'Forza Motorsport');


CREATE TABLE `swaps` (
  `swap_id` int(11) NOT NULL auto_increment,
  `swap_item_id` int(11) NOT NULL,
  PRIMARY KEY  (`swap_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

INSERT INTO `swaps` VALUES (1, 2);

CREATE TABLE `swap_fors` (
  `swap_for_swap_id` int(11) NOT NULL,
  `swap_for_item_id` int(11) NOT NULL,
  KEY `swap_for_swap_id` (`swap_for_swap_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


INSERT INTO `swap_fors` VALUES (1, 4);
INSERT INTO `swap_fors` VALUES (1, 5);




select i.* from items i
inner join swaps s on s.swap_item_i = i.item_id
where s.swap_id=1
union all
select isf.* from items i
inner join swaps s ON s.swap_item_i = i.item_id
inner join swap_fors sf on s.swap_id = sf.swap_for_swap_id
inner join items isf on sf.swap_for_item_id = isf.item_id
where s.swap_id = 1



select distinct isf.* from items i
inner join swaps s ON s.swap_item_i = i.item_id
inner join swap_fors sf on s.swap_id = sf.swap_for_swap_id
inner join items isf on sf.swap_for_item_id = isf.item_id or isf.item_id=i.item_id
where s.swap_id = 1