mysql – How to get a record by date from 1 table and update another table in Postgresql?

I have two tables. In a table (order_produt) have multiple records per
The date and another table (Transfer Product) are also multiple records by date.
The order_product table has the correct record. I want to update my
Product transfer table with product order table by date range.

order_product_table
-------------------------

id | date | Product_id | value
-------------------------------------------
1 | 2017-07-01 | 2 | 53
2 | 2017-08-05 | 2 | 67
3 | 2017-10-02 | 2 | 83
4 | 2018-01-20 | 5 | 32
5 | 2018-05-01 | 5 | 53
6 | 2008-08-05 | 6 | 67


Transfer_product_table
----------------------------

id | date | Product_id | value
--------------------------------------------
1 | 2017-08-01 | 2 | 10
2 | 2017-10-06 | 2 | twenty
3 | 2017-12-12 | 2 | 31
4 | 2018-06-25 | 5 | 5

Result (Transfer_product_table)
--------------------------------
id | date | Product_id | value
--------------------------------------------
1 | 2017-08-01 | 2 | 53
2 | 2017-10-06 | 2 | 83
3 | 2017-12-12 | 2 | 83
4 | 2018-06-25 | 5 | 53

I want a value update by date as you can see in the results table.

I use the query partition, but this is not what I want.

                UPDATE Transfer_product_table imp
SET value = sub.value
FROM (SELECT product_ID, value
, ROW_NUMBER () OVER (PARTITION BY product_id ORDERED by orderdate DESC) AS Rno
FROM order_product_table
where you order between & # 39; 2017-07-01 & # 39; and & # 39; 2019-10-31 & # 39;) sub

WHERE imp.product_id = sub.product_id
and sub.Rno = 1
and impdate between & # 39; 2017-07-01 & # 39; and & # 39; 2019-10-31 & # 39;

api – Android + MySQL + PHP

I do not often ask for help but here I am stuck.
I need to access my remote database from an Android application.
For that I chose a MySQL database and a PHP script.

PHP code

setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);

$ sql = "SELECT * FROM score";

if ($ rs = $ conn-> query ($ sql)) {
$ answer["data"] = array ();
while ($ row = $ rs-> fetch (PDO :: FETCH_ASSOC)) {
// temporary user matrix
$ ligne = array ();
$ ligne["key"] = $ row["key"];
$ ligne["date"] = $ row["date"];
$ ligne["heure"] = $ row["heure"];
$ ligne["score"] = $ row["score"];
$ ligne["theme"] = $ row["theme"];

// insert a row in the final response matrix
array_push ($ answer["data"], $ ligne);
}
}

}
capture (PDOException $ e)
{
echo $ sql "
". $ e-> getMessage (); $ answer["success"] = 1; } // echoing the JSON response echo json_encode ($ response); $ conn = null; ?>

Java code

com.raz.appliandroid package;

import android.content.Context;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import okhttp3.OkHttpClient;
import okhttp3.Request;
import okhttp3.Response;

Public class NoticeActivity extends AppCompatActivity {

Client OkHttpClient = new OkHttpClient ();
// public String url = "https://reqres.in/api/users";
public String url = "http: //localhost/appliandroid/get_all_scores.php";

@Cancel
void protege onCreate (Bundle savedInstanceState) {
context of final context = this;
super.onCreate (savedInstanceState);
setContentView (R.layout.activity_notice);

OkHttpHandler okHttpHandler = new OkHttpHandler ();
okHttpHandler.execute (url);
}


public class OkHttpHandler extends AsyncTask {
String protected doInBackground (String ... params) {

Request.Builder generator = new Request.Builder ();
builder.url (params[0]);
Application request = builder.build ();

try {
Response response = client.newCall (request) .execute ();
return response.body (). string ();

} catch (exception e) {
e.printStackTrace ();
}
zero return
}

void protected onPostExecute (String s) {
super.onPostExecute (s);
Log.i ("API Resultat", s);
}
}
}

If I call an API that is available on the web to allow developers to test their code and that I have not created, my Android application works well and retrieves the data in JSON format.
In the JAVA code, it is the API "https://reqres.in/api/users" what is in comments and it works.

If I call my PHP script, then it does not work, I always go into the "catch".

However, when I look at the results of the 2 APIs in an Internet browser, the structures are similar.

With the API "https://reqres.in/api/users"

{"page": 1, "per_page": 3, "total": 12, "total_pages": 4, "data":[{"id":1,"email":"george.bluth@reqres.in","first_name":"George","last_name":"Bluth","avatar":"https://s3.amazonaws.com/uifaces/faces/twitter/calebogden/128.jpg"},{"id":2,"email":"janet.weaver@reqres.in","first_name":"Janet","last_name":"Weaver","avatar":"https://s3.amazonaws.com/uifaces/faces/twitter/josephstein/128.jpg"},{"id":3,"email":"emma.wong@reqres.in","first_name":"Emma","last_name":"Wong","avatar":"https://s3.amazonaws.com/uifaces/faces/twitter/olegpogodaev/128.jpg"}]}

With my PHP API

{"data":[{"key":"1","date":"toto","heure":"tata","score":"152","theme":"2"},{"key":"2","date":"toto","heure":"tata","score":"152","theme":"2"},{"key":"3","date":"toto","heure":"tata","score":"1516","theme":"2"},{"key":"4","date":"toto","heure":"tata","score":"5458458","theme":"5"}]}

Can you help me solve this problem? It's been several days since I surfed the net and I change my code in all directions without success.

Thanks in advance.

mysql – What can I do if my query adds me again and again the same data?

The thing is that I want to make a query to calculate how much it costs the realization of a product in my c # program, but in the mysql query I want to add the totals of the expenses for products and use the query:

use bd_production;
select detain.DateIngresoDetInv, inv.PK_codInv, inv.DescriptionInv, detailQuantityDetOrdMedida, detailCodUnMed, med.DescriptionAnMed, detailQuantityDetOrd, sum (cosG.TotalCosGas), sum (work.HoursHoHoWork), sum (work.CostoHoHo), COUNT (trab.CostoHoHo) from tbl_ordenproduction prod
 INNER JOIN tbl_detalleordeproduction deta ON prod.PK_CodOrd = deta.CodOrd
 INNER JOIN Tbl_Detail Inventory detainv ON detainv.FechaIngresoDetInv = prod.DateFinalizationOrd
 INNER JOIN tbl_Inventory inv ON detainv.CodInv = inv.PK_CodInv
 INNER JOIN tbl_unidadmedida med ON med.PK_CodUnMed = deta.CodUnMed
 INNER JOIN tbl_receta rec ON inv.PK_CodInv = rec.CodInv
 INNER JOIN tbl_costosgastosfabrication cosG ON rec.PK_CodRec = cosG.CodRec
 INNER JOIN tbl_personalordenproduction by ON deta.CodOrd = por.CodOrd
 INNER JOIN tbl_costoshorahombre trab ON por.PK_CodPerOrd = trab.CodPerOrd
 WHERE month (detainv.FechaIngresoDetInv) = 03 and year (detainv.FechaIngresoDetInv) = 2019
 AND deta.CodInv = inv.PK_CodInv
 AND inv.PK_CodInv = 9
 AND month (FechaCosGas) = ​​03 and year (FechaCosGas) = ​​2019
 AND prod.PK_CodOrd = 10; 

The values ​​I give to the date, order and product are to test if the query works since I will send it from the program, but the problem is that it adds several times the same data, use a> count to see how many rows I took since there are only two rows that have the data but take them as 6, so I do not know what to do so that I do not take the same row several times and add those values ​​every time I do the query. It takes the correct data but adds them several times.

These are the tables that I use:

create table TBL_Bodega (
PK_CodBod int,
NameBod varchar (45),
DirectionBod varchar (60),
int status,
PRIMARY KEY (PK_CodBod)
);
DROP TABLE IF EXISTS TBL_Measure Unit CASCADE;
create table TBL_UnityMeasure (
PK_CodUnMed int,
DescriptionUnMed varchar (25),
int status,
PRIMARY KEY (PK_CodUnMed)
);

DROP TABLE IF EXISTS TBL_Inventario CASCADE;
create table TBL_Inventario (
PK_CodInv int,
DescriptionInv varchar (45),
CostoInv float,
CodUnMed int,
int status,
PRIMARY KEY (PK_CodInv),
CONSTRAINT FK_CodUnMed_Inv
FOREIGN KEY (CodUnMed)
REFERENCES TBL_Measurement Unit (PK_CodUnMed)
);

DROP TABLE IF EXISTS TBL_Classification CASCADE;
create table TBL_Classification (
PK_CodClas int,
DescriptionVarchar beads (45),
int status,
PRIMARY KEY (PK_CodClas)
);

DROP TABLE IF EXISTS TBL_Department Inventory CASCADE;
create table TBL_DetailInventory (
CodInv int,
Int CodClas,
CodBod int,
DateIncomeDetInv date,
DateVentureDetInv date,
AmountDetInv int,
PRIMARY KEY (CodInv, CodClas, CodBod),
CONSTRAINT FK_CodInv_DetInv
FOREIGN KEY (CodInv)
REFERENCES TBL_Inventario (PK_CodInv),
CONSTRAINT FK_CodClas_DetInv
FOREIGN KEY (CodClas)
REFERENCES TBL_Classification (PK_CodClas),
CONSTRAINT FK_CodBod_DetInv
FOREIGN KEY (CodBod)
REFERENCES TBL_Bodega (PK_CodBod)
);
DROP TABLE IF EXISTS TBL_AuthorizationOrderProduction CASCADE;
create table TBL_AuthorizationOrderProduction (
PK_CodAut int,
DescriptionAut varchar (20),
int status,
PRIMARY KEY (PK_CodAut)
);

DROP TABLE IF EXISTS TBL_Avance CASCADE;
create table TBL_Avance (
PK_CodAva int,
DescriptionAva varchar (20),
int status,
PRIMARY KEY (PK_CodAva)
);

DROP TABLE IF EXISTS TBL_Priority CASCADE;
create table TBL_Priority (
PK_CodPrio int,
Description varchar (45),
int status,
PRIMARY KEY (PK_CodPrio)
);

DROP TABLE IF EXISTS TBL_OrderProduction CASCADE;
create table TBL_OrdenProduction (
PK_CodOrd int,
Start DateOrd date,
End DateOrd date,
CodAva int,
CodAut int,
CodPrio int,
int status,
PRIMARY KEY (PK_CodOrd),
CONSTRAINT FK_CodPrio_Ord
FOREIGN KEY (CodPrio)
REFERENCES TBL_Priority (PK_CodPrio),
CONSTRAINT FK_CodAut_Ord
FOREIGN KEY (CodAut)
REFERENCES TBL_AuthorizationOrderProduction (PK_CodAut),
CONSTRAINT FK_CodAva_Ord
FOREIGN KEY (CodAva)
REFERENCES TBL_Avance (PK_CodAva)
);

DROP TABLE IF EXISTS TBL_DetalleOrdenProduction CASCADE;
create table TBL_DetalleOrdenProduction (
CodOrd int,
CodInv int,
AmountDetOrd int,
AmountDetOrd float,
CodUnMed int,
PRIMARY KEY (CodOrd, CodInv),
CONSTRAINT FK_CodOrd_DetOrd
FOREIGN KEY (CodOrd)
REFERENCES TBL_OrderProduction (PK_CodOrd),
CONSTRAINT FK_CodInv_DetOrd
FOREIGN KEY (CodInv)
REFERENCES TBL_Inventario (PK_CodInv),
CONSTRAINT FK_CodUnMed_DetOrd
FOREIGN KEY (CodUnMed)
REFERENCES TBL_Measurement Unit (PK_CodUnMed)
);

DROP TABLE IF EXISTS TBL_Employee CASCADE;
create table TBL_Employee (
PK_CodEmp int,
NameEmp varchar (45),
SurnameEmp varchar (45),
SalaryEmp float,
NitEmp varchar (9),
DirectionEmp varchar (60),
Int phonephone,
MailEmp varchar (40),
int status,
PRIMARY KEY (PK_CodEmp)
);

DROP TABLE IF EXISTS TBL_PersonalOrderProduction CASCADE;
create table TBL_PersonalOrdenProduction (
PK_CodPerOrd int,
CodOrd int,
CodEmp int,
Hours workedPerOrd int,
AreaPerOrd varchar (20),
int status,
PRIMARY KEY (PK_CodPerOrd),
CONSTRAINT FK_CodOrd_PerOrd
FOREIGN KEY (CodOrd)
REFERENCES TBL_OrderProduction (PK_CodOrd),
CONSTRAINT FK_CodEmp_PerOrd
FOREIGN KEY (CodEmp)
REFERENCES TBL_Employee (PK_CodEmp)
);

DROP TABLE IF EXISTS TBL_Receta CASCADE;
create table TBL_Receta (
PK_CodRec int,
CodInv int, - Description of what product will be produced
AmountPersonalRec int,
HoursRequiredRec int,
int status,
PRIMARY KEY (PK_CodRec),
CONSTRAINT FK_CodInv_Rec
FOREIGN KEY (CodInv)
REFERENCES TBL_Inventario (PK_CodInv)
);

DROP TABLE IF EXISTS TBL_DetalleReceta CASCADE;
create table TBL_DetalleReceta (
CodRec int,
CodUnMed int,
CodInv int,
AmountDetRec int,
PRIMARY KEY (CodRec, CodInv),
CONSTRAINT FK_CodInv_DetRec
FOREIGN KEY (CodInv)
REFERENCES TBL_Inventario (PK_CodInv),
CONSTRAINT FK_CodRec_DetRec
FOREIGN KEY (CodRec)
REFERENCES TBL_Receta (PK_CodRec),
CONSTRAINT FK_CodUnMed_DetRec
FOREIGN KEY (CodUnMed)
REFERENCES TBL_Measurement Unit (PK_CodUnMed)
);
DROP TABLE IF EXISTS TBL_GastosFabrication CASCADE;
create table TBL_GastosFabricacion (
PK_CodGas int,
DescriptionGas varchar (30),
CodUnMed int,
int status,
PRIMARY KEY (PK_CodGas),
CONSTRAINT FK_CodUnMed_Gas
FOREIGN KEY (CodUnMed)
REFERENCES TBL_Measurement Unit (PK_CodUnMed)
);

DROP TABLE IF EXISTS TBL_CostosGastosFabrication CASCADE;
create table TBL_CostosGastosFabricacion (
PK_CodCosGas int,
CodRec int,
DateCosGas date,
TotalCosGas float,
int status,
PRIMARY KEY (PK_CodCosGas),
CONSTRAINT FK_CodRec_CosGas
FOREIGN KEY (CodRec)
REFERENCES TBL_Receta (PK_CodRec)
);
DROP TABLE IF EXISTS TBL_CostosHoraHombre CASCADE;
create table TBL_CostosHoraHombre (
PK_CodHoHo int,
CodPerOrd int,
CodEmp int,
Hours WorkedHoHo float,

CostHoHo float,
int status,
PRIMARY KEY (PK_CodHoHo),
CONSTRAINT FK_CodPerOrd_HoHo
FOREIGN KEY (CodPerOrd)
REFERENCES TBL_PersonalOrderProduction (PK_CodPerOrd),
CONSTRAINT FK_CodEmp_HoHo
FOREIGN KEY (CodEmp)
REFERENCES TBL_Employee (PK_CodEmp)
);

How to divide and insert integer value data post in mysql two column using php?

CGST 9% AND SGST 9%:
If the value is 300; You want to split and insert into mysql two column after using the previous line. but it does not work

mysql – Total sum of values ​​of a hierarchical tree formed by a Foreach with IF conditions in PHP

I go to this community to see if they can guide me, I have been fighting with this function and I would like to know how I could obtain the total sum of a variable that runs through the array (hierarchical tree of 5 levels) formed by a foreach and if conditions of a query in the DB, I tried to do it but it only returns the values ​​assigned to me in the penultimate level of the tree.

Here the function:

function show_points ($ id, $ limit, $ level) {
    $ list = array ();
    global $ pdo;

    $ sql = $ pdo-> prepare ("SELECT (@rownum: = @ rownum + 1) AS No, users.id, users.id_pai, users.patent, users.nome, patents.nome as p_nome
                          FROM (SELECT @rownum: = 0) r, users
                          LEFT JOIN patents ON patente.id = usuarios.patente
                          WHERE users.id_pai =: id ");
    $ sql-> bindValue (": id", $ id);
    $ sql-> execute ();

    if ($ sql-> rowCount ()> 0) {
        $ list = $ sql-> fetchAll (PDO :: FETCH_ASSOC);
        $ puntosum = 0;
        foreach ($ list as $ chave => $ user) {
            $ list[$chave]['filhos']    = array ();
            if ($ limit == 0) {
                $ list[$chave]['filhos']    = show_points ($ user['id'], $ limit -1, $ level-0, $ puntosum + = $ level);
                echo $ user['nome']. ' -Points: '. $ Level.'
';             }             elseif ($ limit == 1) {                 $ list[$chave]['filhos'] = show_points ($ user['id'], $ limit -1, $ level - 5, $ puntosum + = $ level);                 echo $ user['nome']. ' -Points: '. $ Level.'
';             }             elseif ($ limit == 2) {                 $ list[$chave]['filhos'] = show_points ($ user['id'], $ limit -1, $ level - 10, $ puntosum + = $ level);                 echo $ user['nome']. ' -Points: '. $ Level.'
';             }             elseif ($ limit == 3) {                 $ list[$chave]['filhos'] = show_points ($ user['id'], $ limit -1, $ level - 25, $ puntosum + = $ level);                 echo $ user['nome']. ' -Points: '. $ Level.'
';             }             elseif ($ limit == 4) {                 $ list[$chave]['filhos'] = show_points ($ user['id'], $ limit -1, $ level - 50, $ puntosum + = $ level);                 echo $ user['nome']. ' -Points: '. $ Level.'
';             }             elseif ($ limit == 5) {                 $ list[$chave]['filhos'] = show_points ($ user['id'], $ limit -1, $ level - 100, $ puntosum + = $ level);                 echo $ user['nome']. ' -Points: '. $ Level.'
';             }         }     }     echo $ puntosum. ' Total
';     return $ puntosum; }

As you may notice level 1 is assigned -100 pts until you reach level 6, but at the time of doing the summation only takes the values ​​of the last cent that is met, in this case when $ limit == 5.

Beforehand thank you very much!

c # – How can I find out the root cause of the error "Out of range value for column" in MySQL?

I have a console application written with DO# On top of .Net Core 2.2 Frame with EntityFrameworkCore as ORM. My application executes several tasks that run every 15 minutes. Each task calls an external API server and updates my internal MySQL database (MySQL v8.16.) If the job fails for any reason, I receive an email with the error.

I received the error email with the following error

Error message: Value out of range for column & # 39; Value & # 39; in row 1

I'm trying to figure out that Value The column is causing this error and tries to find out which query caused this problem.

Because of its appearance, MySQL does not register invalid queries anywhere, which sends me to a wild search of the problem column.

I searched for all the columns that are named & # 39; Value & # 39; in my database using the following query

SELECT table_name, Is_nullable, data_type, column_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Table_Schema = & # 39; my_table_name & # 39; and Column_Name = & # 39; Value & # 39;

The previous query gave me the following results.

table_name Is_nullable data_type column_type
field_values ​​DO NOT varchar varchar (128)
boolean_fields YES tinyint tinyint (1)
lookup_fields YES text text
numeric_fields YES decimal decimal (30.6)
text_fields YES text long text long
time_fields YES datetime datetime (6)
user_tokens YES long text long text

I think that if the error were caused by a column with the varchar / longtext type, it would get the error "Text truncated" and not "Value out of range". With that in mind, I'm left with the following columns as a suspect

table_name Is_nullable data_type column_type
boolean_fields YES tinyint tinyint (1)
numeric_fields YES decimal decimal (30.6)
time_fields YES datetime datetime (6)

Also, as my request is written in DO# and EntityFrameworkCore is used as my ORM to interact with the database, I trust that both boolean_fields.Value Y time_fields.Value They are not causing the problem. My conclusion is because the type of property is DataTime? at TimeField entity model where the type of property is bool for me Boolean field entity model.

With the above assumption in mind, I am in a field that could be causing my problem.

table_name Is_nullable data_type column_type
numeric_fields YES decimal decimal (30.6)

I keep all the numerical values ​​in numeric_fields.Value column (that is, int, decimal and long)

The API is expected to return numerical data using the following

data_type MinValue MaxValue
& # 39; & # 39 ;, & # 39; -2147483648.0000 & # 39 ;, & # 39; 2147483647.0000 & # 39;
& # 39; Decimal & # 39 ;, & # 39999999999999.00.00, & # 39; 999999999999.0000 & # 39;
& # 39; Long & # 39 ;, NULL, & # 39; 9223372036854775807.0000 & # 39;

It should be able to store all the previous numeric in decimal (30.6) Column without problem.

How can I identify what could be causing this problem?

mysql: What will be the order of execution of the declaration in this use case?

Say execute Select * from users in t1 that ends in t3. In t2 (between t1 and t3), the update arrives for some of the records in the user table, for example:
update users set address = & # 39; xyz & # 39; where name = & # 39; abc & # 39 ;. The shared lock acquisition instruction will be selected in all rows in advance or will be acquired for each row
independently when you start reading that row?

My understanding is that anyone (between selecting or updating) acquires the shared or exclusive block before, will execute it, another will wait.

I'm using innodb Mysql.

mysql – Master master replication of Mariadb compatible with HAPROXY for automatic failover

We have implemented the master-master configuration in maria-db. Master A is used for read / write and Master B is used for backups and MIS operations. We have created this configuration so that we do not have to promote the slave in case of failure.

Now we want to implement HA PROXY for automatic failover. In case the Master A is dropped, the application should automatically change to Master B.

Now, my question is that if Master A stops working and applications start to serve from Master B. But when Master A connects and is not synchronized with Master B, what will HA PROXY do? check the status of the synchronization and allow Master A to synchronize with Master B and then allow applications to connect to Master A.

What parameters must be added to achieve this.

Configuration of HA PROXY:

enter the description of the image here

bitcoind – add dependency on mysql configure.ac bitcoin

I'm working on the bitcoin code, my goal is to move the data from the block chain to relational db, but I can not solve the mysql dependency for different platforms, the platform includes mac and ubuntu. I tried to give static and dynamic dependence, but both things do not work at all.

    [--with-mysql=[--with-mysql=[--with-mysql=[--with-mysql=     path of the root directory of the MySQL installation],
    [MYSQL_lib_check="$with_mysql/lib/mysql $with_mysql/lib"
MYSQL_inc_check="$with_mysql/include/mysql"],
    [MYSQL_lib_check="/usr/local/mysql/lib/mysql /usr/local/lib/mysql /opt/mysql/lib/mysql /usr/lib/mysql /usr/local/mysql/lib /usr/local/lib /opt/mysql/lib /usr/lib"
MYSQL_inc_check="/usr/local/mysql/include/mysql /usr/local/include/mysql /opt/mysql/include/mysql /usr/include/mysql"])

AC_ARG_WITH (mysql-lib,[--with-mysql-lib =[--with-mysql-lib =
    [--with-mysql-lib= directory path of the MySQL library installation],
    [MYSQL_lib_check=$with_mysql/lib/mysql])

AC_ARG_WITH (mysql-include,[--with-mysql-include =[--with-mysql-include =
    [--with-mysql-include=
                          directory path of the MySQL header installation],
    [MYSQL_inc_check=$with_mysql/include/mysql])

AC_MSG_CHECKING ([for MySQL library directory])
MYSQL_libdir =
for m in $ MYSQL_lib_check; do
if you try -d "$ m" && 
(test -f "$ m / libmysqlclient.so" || test -f "$ m / libmysqlclient.a")
so
MYSQL_libdir = $ m
break
fi
fact

while I was configuring, I was trying to give a path to the installation directory, ie ./configure –with-mysql = / path configured correctly, but when I run the make command, the exception "_get_driver_instance", referenced from: makeConnection () in libbitcoin_wallet.a (libbitcoin_wallet_a-walletdb.o)

I may not be on the right track, help me if you have any ideas on how to make a file system.

Any help is appreciable, thanks.

mysql – How do I calculate the average weight of runners by competition?

I have a task in which I need to calculate the average weight of runners by competition. The task has many different parts, but I got stuck in this one, so please, help me if you can 🙂

So if I have the following tables:

CREATE TABLE Rider (
name VARCHAR (30),
Actual weight
PRIMARY KEY (name)
MOTOR = innodb;

CREATE TABLE Participants (
contest VARCHAR (30) NOT NULL,
rider VARCHAR (30) NOT NULL,
PRIMARY KEY (competition, rider),
FOREIGN KEY (competence) REFERENCES Competence (name),
FOREIGN KEY (rider) REFERENCES Rider (name)
MOTOR = innodb;

CREATE MESA contest (
name VARCHAR (30),
PRIMARY KEY (name)
MOTOR = innodb;

With the following data:

:: Riders ::
(Name: Weight)
Alan: 55
David: 60
Pedro: 65
Stan: 60


::Competition::
(First name)
Derby
York


::Participants::
(Contest: Rider)
Derby: Alan
Derby: David
York: Alan
York: Peter
York: Stan

Therefore, the results that I should obtain from my SQL query for the different competences should be:

  • Derby: 57.5 (Alan and David average)
  • York: 60 (average of Alan, Peter and Stan)

How should I approach this SQL query?