SQL Query to find recommendations

Need a SQL query to return data from the following three tables: (see attached spreadsheet for sample data)

**Users (fields: userid,name,active,rating)

User_connections (fields: userid,connectinguserid,status)

user_recommendations (fields: userid,suppliertype,active)

****

Problem**: Find a connected user(s) who can make recommendation(s) for a specific supplier type.

**Conditions**: Users must be active, User_Connections must be of status “Confirmed??, user_recommendations must be active

**Return**: UserIDs that can make recommendations. Optionally, returns their rating from the users table (this is a nice to have) and optionally also returns their ‘distance’ away (this is a desirable feature), sorted by distance (ascending) and then rating (descending)

**Variables**: the userID making the request, the supplier type being requested

**Limits:** there will only ever be one supplier type requested and only one userid making the request.

**Environment:** Needs to work in Access 2000 and in SQL Server 2000 (two separate versions/methods can be supported)

*Key Issues

*

Distance

The problem involves the ‘distance’ idea. For example, using the above data, if the UserID = 1 requests a mechanic, then they do not know one (their only entry in the user_recommendations table is for a supplier of type ‘Plumber’. However, they are _connected_ to userid = 2 via the user_connections table entry and userid = 2 does know a mechanic. This may be stated as follows:

Userid 1 asks: “Do I know a mechanic??

Query replies: “No. But you know userid 2, who does know a mechanic. They are 1 step away??

Scale

This query will need to work where the number of steps is up to 5.

This query will need to work where the number of connections per userid is 10.

This query will need to work where there are in total 10,000 (interconnected) userids (and therefore 100,000 records in the user_connections table) and 30 possible categories of supplier with each user having an average of 5 supplier types (and therefore 50,000 records in the recommendations table).

The attached excel file provides example data.

## Deliverables

1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.

2) Installation package that will install the software (in ready-to-run condition) on the platform(s) specified in this bid request.

3) Exclusive and complete copyrights to all work purchased. (No GPL, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site).

## Platform

Access 2000 & SQL 2000

Kejuruteraan Microsoft Access MySQL PHP Kejuruteraan Perisian Ujian Perisian SQL

ID Projek: #2993290

Tentang projek