Em Andamento

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

Habilidades: Engenharia, Microsoft Access, MySQL, PHP, Arquitetura de software, Teste de Software, SQL

Ver mais: types of data entry software, types of data entry fields, sql data entry form, spreadsheet engineering, sample recommendation, recommendation sample, n query, i find buyer, find package, find code, find a program, file status 22, data entry form excel 2003, buyer find, methods engineering, where to find supplier, SQL Query, recommendations, query table, plumber, find supplier, find buyer, find a name, find a buyer, excel with sql server

Acerca do Empregador:
( 12 comentários ) United Kingdom

ID do Projeto: #2993290

Premiar a:

ekam

See private message.

$21.25 USD em 4 dias
(111 Avaliações)
5.7

8 freelancers are bidding on average $89 for this job

aliviu2003

See private message.

$34 USD in 4 dias
(48 Comentários)
5.6
stolevw

See private message.

$25.5 USD in 4 dias
(28 Comentários)
2.6
messiahvw

See private message.

$8.5 USD in 4 dias
(0 Comentários)
0.0
manivannanuma

See private message.

$127.5 USD in 4 dias
(0 Comentários)
0.0
vignesh2002

See private message.

$425 USD in 4 dias
(0 Comentários)
0.0
kntcnrg

See private message.

$63.75 USD in 4 dias
(0 Comentários)
0.0
ikobosl

See private message.

$8.5 USD in 4 dias
(3 Comentários)
0.0