I have been pondering over this for some time. Essentially I've a listing of web sites inside a table. Every one of these websites has several posts. Each publish can link to a single other website. I keep track of the items links where. I have to produce a query that provides me a listing of websites that the certain publish COULD connect to. You will find, however, some rules for this:

  • Each publish can connect to multiple websites
  • Each publish are only able to connect to websites that don't possess a publish connecting to it
  • Each publish can connect to exactly the same website multiple occasions (therefore if it has been linked previously, that's fine)
  • An internet site can connect to it's own posts

Take below, a representation of my websites, each one has numerous posts.

Now, basically add all A's possible links:

However, not one other site has the capacity to link to A, just because a is connecting for them (rule 2). See below as to the C can connect to:

So, now something that A or C links to can't link to it, this reduces the variety of each one of the other sites. You need to keep in mind that its not all website will connect to all possible combinations, I simply need these combinations coming back in the query. Should you look below I have added links from G and D, these bankruptcies are not every possible link they produce, some which are possible: D and G linking

It's beginning to consider shape now. You will find that many websites now connect to H, so H's connecting choices are limited. Actually it may only ever connect to F, B, E and itself (rule 4).

I am not lazy here and merely asking your to create a question for me personally. I have spent a very long time attempting to do this and never sure how to start.

Here is a connect to some sample data and also to the table structure: http://pastie.org/1506715

I've been capable of producing something near to all final results using a mix 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 seem like the correct answer is close, but I am simply not there. I believe I have to make use of a NOT Is available clause having a subquery, but I am unsure regarding exactly what it's I want.

I spend around an hour about this and I will tell you one factor, it was difficult. I attempted a variety of method of getting the solution and things i emerged with does not appear to become to efficient and yet the only method.

You're searching for a positive change, meaning I choose all of the possible combinations in which a combination is not set. This appears strange to express, but that's what is 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