MySQL Query Complex Relationship of One Way Linking

I've been pondering over this for a while. Basically I have a list of websites in a table. Each one of these websites has several posts. Each post can link to at least one other website. I keep a record of what links where. I need to generate a query that gives me a list of all websites that a certain post COULD link to. There are, however, some rules to this:

• Each post can link to multiple websites
• Each post can only link to websites that do not have a post linking back to it
• Each post can link to the same website multiple times (so if it's been linked in the past, that's fine)
• A website can link to it's own posts

Take below, a representation of my websites, each has a number of posts. http://img140.imageshack.us/img140/815/emptyd.jpg

Now, if I add all of A's possible links:

http://img834.imageshack.us/img834/4931/alinks.jpg

But now, no other site is able to link back to A, because A is linking to them (rule 2). See below to what C can link to:

http://img39.imageshack.us/img39/273/clinks.jpg

So, now anything that A or C links to can't link back to it, this lowers the options for each of the other sites. It's important to remember that not e开发者_开发百科very website will link to all possible combinations, I just need these combinations returning from the query. If you look below I've added links from G and D, these aren't every possible link that they produce, just some that are possible: D and G linking http://img689.imageshack.us/img689/7140/dglinks.jpg

It's starting to take shape now. You'll notice that a lot of websites now link to H, so H's linking options are very limited. In fact it can only ever link to F, B, E and itself (rule 4).

I'm not being lazy here and just asking your to write a query for me. I've spent a long time trying to work this out and not sure where to start.

Here's a link to some sample data and to the table structure: http://pastie.org/1506715

I have been able to produce something close to all outcomes by using a cross join:

SELECT t1.* , t2.* 
FROM test_posts t1, test_posts as t2
WHERE
t1.post_id != t2.post_id
ORDER BY
t1.post_id, t2.post_id;

I feel like it's quite close, but I'm just not there. I think I need to use a NOT EXISTS clause with a subquery, but I'm unsure as to exactly what it is I need.

---

I spent about an hour on this and I can tell you one thing, this was difficult. I tried many different ways to get the answer and what I came up with doesn't seem to be too efficient but yet the only way.

You are looking for a difference, which means I select all the possible combinations where a combination is not yet set. This seems weird to say, but that is what's happening.

SELECT post.post_id, website.website_id
FROM test_posts post
  JOIN test_posts website ON website.website_id NOT IN
(
  SELECT sl.website_id
  FROM test_posts f
    INNER JOIN test_smartlink_to_websites sl ON f.post_id = sl.post_id
  WHERE f.post_id = post.post_id
)
ORDER BY post_id