Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account

Most common tags amongst selected table items?

⇧
0
⇩
37 views

I have a table similar to below

Questions table:

Qid  Tags
---  ---------
1    SQL
1    DATABASE
1    ALGORITHM
2    ALGORITHM
2    TAGS
3    SQL
3    SYNTAX
3    DATABASE

The following

SELECT * FROM Question table WHERE TAGS='SQL'

will display:

Qid  Tags
---  ----
1    SQL
2    SQL
3    SQL

I'm looking for a query which will display the highest related question based on similar tags.

So if I looked up Question table - Qid=1. The result would be Qid 3, as Qid 1 and Qid 3 have 2 similar terms, but Qid 2 only as 1 similar tag to Qid 1. If i looked up Qid 2 it would return Qid 1 as it they share 1 tag and Qid 2 and 3 share no tags. And If i looked up Qid 3 it would return Qid 1 as it as Qid 1 has 2 tags that Qid 3 has, but Qid 2 has no tags that Qid 3 has.

Thanks for any help.

mysql sql database-queries
asked June 20, 2011 12:26 pm CDT
Sam Healey
posted via StackOverflow

2 Answers

⇧
0
⇩
 

Something along these lines maybe?

SELECT Qid, COUNT(*) AS TagsInCommon FROM Questions
WHERE Tags IN (
  SELECT DISTINCT Tags FROM Questions WHERE Qid=1
)
GROUP BY Qid
ORDER BY TagsInCommon DESC

I'm not sure about the field names but you get the idea...

answered June 20, 2011 1:23 pm CDT
Damp
⇧
0
⇩
 
 
declare @Qid int
set @Qid = 1

select top 1 Q2.Qid, COUNT(*) from Questions Q1
inner join Questions Q2 on Q1.Tags = Q2.Tags
where Q1.Qid = @Qid and Q2.Qid <> @Qid
group by Q2.Qid
order by COUNT(*) desc
answered June 20, 2011 1:23 pm CDT
amit_g

Your answer

Join with account you already have

Sign in with Twitter account
Sign in with Facebook account
Sign in with Google Friend Connect

Preview
Similar questions
Checking to see if a selected list of items are in another table.
June 14, 2011
Mysql: SELECT all items in common between two users
March 2, 2011
SELECT all items in common between two users on FOUR tables
March 3, 2011
SELECT all items in common between two users on three tables
March 3, 2011
Php/mySQL How do I get all the tags from the database for multiple items?
March 8, 2011
Dispalying table data through ajax,php table name to be selected from dropdown menu.
April 10, 2011
How do I perform an iterative calculation using a value from another table selected based on a YYYY-MM date and write it back to the first table?
April 6, 2011
Updating a table with fields selected from another table?
June 19, 2011
Limiting based on value selected from another table
January 19, 2011
Echo a selected id from MySQL table
January 28, 2011
PHP, select a random row from a mysql table which haven't been selected yet!
February 14, 2011
Selecting all items in one table and join with another table, allowing nulls
March 10, 2011
MYSQL: How do I select from a table and delete all other non-selected entries?
May 23, 2011
Mysql: select all items from table A if not exist in table B
January 11, 2011
Select from table where value in common with another table
April 4, 2011
What's the best way to find common rows in a table
March 7, 2011
Storing selected spinner item in database table - Android
March 15, 2011
Mysql - find common values within one table
May 11, 2011
Restricting certain users to only their data in a common table
June 8, 2011
Displaying most common correlations in a MySQL table
May 3, 2011
SQL command to copy selected content from selected rows into other rows?
February 21, 2011
Grab all items from MYSQL Table expired 1 day ago
February 22, 2011
How to get values from another table for concatenated array items?
February 19, 2011
How to sort items in mysql based on data from another table ?
May 22, 2011
Select items that have multiple matches in a normalized table
April 20, 2011
How can I order my items by date and time with this convoluted table structure?
May 31, 2011
MySQL query for getting cascading items count in categories table
May 9, 2011
MySql SELECT items of a list and their name item stored in another table?
February 14, 2011
Using MySQL to fetch data from two 'items' tables (both with different values), using a third 'itemlist' table
May 11, 2011
Changing data items in ListView and SQLite table update - is it to expensive?
February 5, 2011