All geek questions in one place

MySQL Query Integrated One-way Binding Relationship

I thought about this for a while. Basically I have a list of sites in the table. There are several posts on each of these sites. Each message can link to at least one other site. I write down what links are. I need to generate a query that gives me a list of all the sites that a specific COULD link links to. However, there are some rules:

  • Each post can link to multiple websites.
  • Each post can only link to websites that do not have a link to the link.
  • Each post can link to the same site several times (so if it has been linked in the past, that’s fine)
  • A website may link to its own posts.

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

Now if I add all possible links:
http://img834.imageshack.us/img834/4931/alinks.jpg

But now no other site can link to A, because A links to them (rule 2). See below what C may refer to:
http://img39.imageshack.us/img39/273/clinks.jpg

So, now all that links A or C cannot link to it, this reduces the parameters for each of the other sites. It is important to remember that not every site will link to all possible combinations, I just need these combinations returned from the request. If you look below, I added links from G and D, this is not every possible link that they produce, but only some of the possible ones: Links D and G http://img689.imageshack.us/img689/7140/dglinks.jpg

Now it begins to take shape. You will notice that many sites now link to H, so links to H linking are very limited. In fact, he can only communicate with F, B, E and himself (rule 4).

I am not lazy here and just ask you to write a request for me. For a long time I tried to understand this and did not know where to start.

: http://pastie.org/1506715

- , -:

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;

, . , NOT EXISTS , , .

+3
1

, , . , , , , , .

, , , . , , .

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
+1
source

Source: https://habr.com/ru/post/1790309/

More articles:


All Articles