What Is The Query to Get "Related Tags" Like in

what is the query to get related tags like in stack overflow

Try:

  SELECT t.tagname
    FROM TAGS t
    JOIN TAGS_BRIDGE tb ON tb.tagid = t.id
    JOIN (SELECT li.id
            FROM LINKS li
            JOIN TAGS_BRIDGE tb ON tb.linkid = li.id
            JOIN TAGS t ON t.id = tb.tagid
           WHERE t.tagname = 'XYZ') x ON x.id = tb.linkid
GROUP BY t.tagname

How can I query related tags from a relationship table?

SELECT
  data_tag_rel.data
FROM
  data_tag_rel
WHERE
  data_tag_rel.tag IN ((SELECT id FROM tags WHERE tags.tag IN ('silver', 'gold')))
GROUP BY
  data_tag_rel.data
HAVING
  COUNT(*) = 2

If it's possible for a data item to have the same tag multiple times, that changes slightly...

HAVING
  COUNT(DISTINCT data_tag_rel.tag) = 2

The nasty nested IN() can also be replaced if you feel like it...

FROM
  data_tag_rel
INNER JOIN
  tags
    ON tags.id = data_tag_rel.tag
WHERE
  tags.tag IN ('silver', 'gold')

The crux of it is; filter normally, then use the HAVING clause to make sure you have two mathces, not just one.

EDIT:

To be explicit about how to get from there to the associated tags, just join back on to the data_tag_rel table...

SELECT DISTINCT
  tags.tag
FROM
(
  SELECT
    data_tag_rel.data  AS id
  FROM
    data_tag_rel
  WHERE
    data_tag_rel.tag IN ((SELECT id FROM tags WHERE tags.tag IN ('silver', 'gold')))
  GROUP BY
    data_tag_rel.data
  HAVING
    COUNT(*) = 2
)
  AS data
INNER JOIN
  data_tag_rel
    ON data_tag_rel.data = data.id
INNER JOIN
  tags
    ON tags.id = data_tag_rel.tag

How to create SubDomains and redirect to them in ASP.NET?

Depending on what you are actually trying to accomplish with these redirects, this may be useful. If you are trying simply to have multiple sub-domains handled from a single web application, you can fake this sub-domain behavior outside of ASP.NET entirely using an IIS-based server hosting a single web application with an ISAPI rewrite tool such as Ionics Isapi Rewrite Filter (http://www.codeplex.com/IIRF), assuming you have the ability to add an ISAPI filter to your hosting environment.

Since the ISAPI filter is handled before ASP.NET even knows about the request, you can host a number of sub-domains (and full domains) from just one web application. Unfortunately, emulating this type of redirect in the ASP.NET built-in web server (Cassini) is not possible because it doesn't use ISAPI. This method does allow you to test the functionality of your final sub-domains, but only at the final redirect location; you can't really test the original domain map in Cassini either since the Windows hosts file doesn't allow rules with ports.

If you set up your single application to have pages to handle the functionality of all of your desired sub-domains, you can simply redirect the request for the sub-domain into your single application at the desired location. This can be as simple as a folder of pages within your single application that handles all the logic for your new sub-domain. Here are some example rewrite rules for Ionics that will send requests for the various sub-domains to the final location in your single web project:

RewriteCond %{HTTP_Host} ^(?~new-sub-domain.yourdomain.com).*$ [I]
RewriteRule ^(.*)$ http://www.yourdomain.com/new-sub-domain$1 [I,R=302]
RewriteCond %{HTTP_Host} ^(?~another-new-sub-domain.yourdomain.com).*$ [I]
RewriteRule ^(.*)$ http://www.yourdomain.com/another-new-sub-domain$1 [I,R=302]
# '#' is a comment marker in the rewrite files
# [I] at the end means case-insensitive
# [L] at the end  means last rule (sending the request on but hiding the forward from the end user)
# [R] at the end  means an official redirect and can be used instead of [L] (the end user will then see a new request, such as a 301 redirect using [R=301])

This will cause all requests to the new sub-domains to go to a directory within your existing project (/new-sub-domain/ and /another-new-sub-domain/, respectively); this can be modified to send them wherever you want within that project.

While this approach allows you to host several sub-domains (and potentially several full domains) from a single application, the restriction on redirect/rewrites within the ASP.NET web server (Cassini) will leave you restricted to testing the functionality of these final location (e.g., "/new-sub-domain/").

Rails 3 - which sql query for related tags

I think the best way to solve this is to have a many to many relation between tags, so a tag can have many tags. Then in the relation between two tags, you store the count of how many instances they occur together.

You could also simply create a new tag-to-tag connection each time the tags occur in the same article. This will however create some redundancy in the database.

If you do not want to introduce another table, you can get this to work the way you started, except it might be very slow with even a fairly small amount of tags. But here is how I would have done this, if you can not make a Tag-to-tag connection:

hash_storage = Hash.new(0) #0 is the default value
tag.articles.each do |article|
   if article.tags.each do |t|
      #we now know that this tag "t" is in the same article as our original tag
      if t!=tag #we don't care if t actually the same as our original tag
          hash_storage[t]+=1
      end
   end
end
#Now, this is a bit messy, but we need to sort the hash.
ordered_tags = hash_storage.map{|k,v| [v,k]}.sort.reverse.map{|a,b| b} #there might be a smarter way of doing this. 

ordered_tags.each do |t|
  #do whatever. the tags should now  be ordered by their relative frequence of occurrance together with the initial tag.
end    

Hope this helps :)

MySQL query to find related posts by tag and sort by popular?

Try this:

SELECT at.article_id,count(*) AS q 
FROM article_tags at
INNER JOIN article_info ai ON at.article_id = ai.article_id
WHERE at.id_tag IN (
    SELECT id_tag 
    FROM article_tags 
    WHERE article_id=41
) 
AND at.article_id!=41 
GROUP BY at.article_id 
ORDER BY q DESC, ai.article_view DESC

SQL Query for retrieving Tags from

select distinct t.name
from ObjectTag ot join
     tags t
     on ot.tagId = t.id
where exists (select 1
              from ObjectTag ot2 join
                   Tags t2
                   on ot2.tagid = t2.id
              where ot.objectId = ot2.objectId and
                    t2.name = 'html' and t2.name <> t.name
             ) ;

Retrieving a list of blog posts with related tags with less query

Here's a simple outer join. Is that what you are after?

SELECT
  It.ItemID,
  It.Title [ItemTitle],
  Tg.TagID,
  Tg.Title [TagTitle]
FROM Item It
LEFT OUTER JOIN Tag Tg
ON It.ItemID = Tg.ItemID

Get item and all related tags with one query

You are searching for the GROUP_CONCAT function. Just tried it on my database with

SELECT o.orderno,
       GROUP_CONCAT(d.itemno ORDER BY d.itemno ASC SEPARATOR ', ') as items
FROM order o
LEFT JOIN order_detail d ON o.id = d.order_id
GROUP BY d.order_id
ORDER BY o.id ASC

returns a result of order numbers with a comma seperated list of ordered items:

orderno   | items
----------------------------------
201010001 | 100123, 100456, 100987
201010002 | 123456, 123457

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

But I suppose this is not ANSI SQL so it won't be available for SQL-Server ;-), but a quick search here at stackoverflow returned this question: Simulating group_concat MySQL function in Microsoft SQL Server 2005?

The corresponding command in sql server should be CROSS APPLY

Another good article about CROSS APPLY

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

For usage with subsonic you should use an InlineQuery (aka CodingHorror) to execute raw sql with subsonic.

---

Related Topics