All geek questions in one place

SQL Stored Procedure

I have 3 tables:

  • tbl_Image , from which a list of all images will be obtained
  • A user table from which the user ID will be obtained
  • and an Image and Member association table named tbl_MemberAssociation .

My workflow is that the user can upload the image and it will be stored in the image table. Then all users can view this image and select one of the three options presented with the image. If the user selects a parameter, he will be added to the Association table. The user cannot view the same image more than once. Thus, there will be no multiple entries.

Now I want to find a% match by getting a list of participants, choosing the same option and another option that matches all the regular images for which they have provided their option.

those. let's say if 3 users say that A, B and C are viewing a tajmahal image. If A and B chose a great choice, and C - "Not bad." For another image, it is said that the Indian flag AB and C were chosen in the same way as the salute. Then for user A: B there is a 100% match (since they selected the same option both times). For A: C, 50% correspond to one of two.

So, this is my scenario in which I need to find all the correspondence corresponding to the registered user.

Please help me .... I am completely concerned about this procedure.

+4
source share
1 answer

I made some assumptions about the actual structure of your tables, but if I understand what you are looking for, I think this query will get the desired results. You may need to make a few changes to suit your table structures.

  SELECT matches.UserName, CAST(matches.SameRatings AS FLOAT) / CAST(ratings.UserRatingCount AS FLOAT) AS MatchPercent FROM tbl_User CROSS APPLY ( SELECT COUNT(*) UserRatingCount FROM tbl_MemberAssociation WHERE UserId = tbl_User.UserId ) ratings CROSS APPLY ( SELECT u1.UserId, u1.UserName, COUNT(*) AS SameRatings FROM tbl_MemberAssociation ma INNER JOIN tbl_MemberAssociation ma1 ON ma.ImageId = ma1.ImageId AND ma.Rating = ma1.Rating AND ma.UserId <> ma1.UserId INNER JOIN tbl_User u1 ON ma1.userId = u1.UserId WHERE ma.UserId = tbl_User.UserId GROUP BY u1.UserId, u1.UserName ) matches WHERE tbl_User.UserId = @UserId ORDER BY MatchPercent DESC

@UserId can be passed as input to a stored procedure.

The 1st CROSS APPLY rating receives an invoice for the total number of ratings for a registered user.

The second CROSS APPLY "match" gets the number of similar ratings for other users in the database.

The result set uses calculations calculated by two CROSS APPLY queries to calculate the percent match between the registered user and other users who rated the same images as the registered user.

0
source

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

More articles:


All Articles