Databases
StackOverflow2013
Postsdbo
POMySQL Query Complex Relationship of One Way Linking
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL Query Complex Relationship of One Way Linking
    primarykey
    data
    text
    <p>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:</p> <ul><li>Each post can link to multiple websites</li><li>Each post can only link to websites that do not have a post linking back to it</li><li>Each post can link to the same website multiple times (so if it's been linked in the past, that's fine)</li><li>A website can link to it's own posts</li></ul> <p>Take below, a representation of my websites, each has a number of posts. <a href="http://img140.imageshack.us/img140/815/emptyd.jpg" rel="nofollow noreferrer">http://img140.imageshack.us/img140/815/emptyd.jpg</a></p> <p>Now, if I add all of A's possible links:<br /> <a href="http://img834.imageshack.us/img834/4931/alinks.jpg" rel="nofollow noreferrer">http://img834.imageshack.us/img834/4931/alinks.jpg</a></p> <p>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:<br /> <a href="http://img39.imageshack.us/img39/273/clinks.jpg" rel="nofollow noreferrer">http://img39.imageshack.us/img39/273/clinks.jpg</a></p> <p>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 every 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: <a href="http://img689.imageshack.us/img689/7140/dglinks.jpg" rel="nofollow noreferrer">D and G linking http://img689.imageshack.us/img689/7140/dglinks.jpg</a></p> <p>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).</p> <p>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.</p> <p>Here's a link to some sample data and to the table structure: <a href="http://pastie.org/1506715" rel="nofollow noreferrer">http://pastie.org/1506715</a></p> <p>I have been able to produce something close to all outcomes by using a cross join:</p> <pre><code>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; </code></pre> <p>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. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USMike
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL Query Complex Relationship of One Way Linking
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL Query Complex Relationship of One Way Linking
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL Query Complex Relationship of One Way Linking
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload