SQL - Complicated lookup query
I need help with a complicated query. I have a Venues
table, a Tag
table and a Venues_Tag_lookup
table. When I have the detail of a single venue on screen I would like to be able to show other venues that are similar to the current venue.
This will require a query that returns the t开发者_运维问答op 5 venues with the most amount of matching tags. (I'm using MSSQL)
Here is a simple layout of how my tables look.
Venues_tbl
----------
VenueId
Venue_name
Tag_tbl
---------
TagId
Tag_name
Venues_Tag_lookup
------------------
Venue_tagId
VenueId
TagId
If you have any question please ask.
Thanks in advance.
SELECT TOP 5
V.Venue_name
FROM
-- this = tags for this venue
Venues_Tag_lookup this
JOIN
-- others = tags for other venues
Venues_Tag_lookup others
--see what matches, there will be a big pile of them
ON this.TagId = others.TagId
JOIN
Venues_tbl V ON others.VenueID = V.VenueID
WHERE
--filter to this and others
this.VenueID = @TheOneOnScreen
AND
others.VenueID <> @TheOneOnScreen
GROUP BY
--collapse to other venues ...
V.Venue_name
ORDER BY
-- ... and simply COUNT matches
COUNT(*) DESC
following query may help:
-- params = @venueId, @tagId select venueId, venueName, (select count * from venues_tag_lookup vtl where vtl.venueid=v.venueid )tagCount from venues_tbl v where venueId in( select venueId from Venues_tag_lookup where tagId = @tagId ) and venueId @venueId order by 3 desc
精彩评论