选择两个表并在表中查看

Question

I’ve a BD with the next tables.

TABLE detalle_contrato

TABLE detalle_tradicional

There is a relation with ID_CONTRATO and i need to view the table with the next data.

SELECT
    ID_CONTRATO,
    TRADICIONAL,
    NOM_VARIEDAD,
    SUM(CANTIDAD)
FROM detalle_contrato
WHERE ID_CONTRATO = '$ID' AND TIPO_VARIEDAD = 'TRADICIONAL';

SELECT
    SUM(CANTIDAD_D)
FROM detalle_tradicional
WHERE ID_CONTRATO = '$ID'
GROUP BY NOM_VARIEDAD ";

There are a filter different in this two select and I need this in a table but i don't know together.

The idea is this:

ID_CONTRATO, 
NOM_VARIEDAD, 
CANTIDAD
    ( THIS IS THE SUM THE ALL CANTIDAD DUKE AND 
      LEGACY IN GROUP THE TABLE DETALLE_CONTRATO) ,
CANTIDAD_D
    (TABLE DETALLE_TRADICIONAL THIS IS SUM 
    THE ALL DUKE AND LEGACY SEPARATE THE CANTIDAD_D

I need exactly this using the data the photos

Answer 1

You can use LEFT JOIN. Left join your second table with id_contrato and  detalle_contrato id_contrato.



SELECT
    dc.ID_CONTRATO,
    dc.TRADICIONAL,
    dc.NOM_VARIEDAD,
    dc.IFNULL(SUM(CANTIDAD),0) AS CANTIDAD,
    dc.IFNULL(SUM(CANTIDAD_D),0) AS CANTIDAD_D
FROM
    detalle_contrato dc
LEFT JOIN TABLE_NAME t2 ON t2.ID_CONTRATO = dc.ID_CONTRATO
WHERE dc.ID_CONTRATO = '$ID' AND t2.TIPO_VARIEDAD = 'TRADICIONAL'