Right Join not showing entries with "incomplete" data-CodePudding

I'm trying to show a table with information gathered from 4 different tables.

orden_trabajo_pk	concepto	descripcion	importe	ing_responsable_id
159460	concept example1	description	18000	1
159461	concept example2	description	8000	1

cotizacion_pk	concepto	descripcion	importe	orden_trabajo_id
2010633	concept example1	description	20000	159460

factura_cliente_pk	fecha	importe	orden_trabajo_id
A812	2022-10-15	19000	159460

ing_responsable_pk	nombre	tel	planta_id
1	Newdary	123	1

I'm using 2 Right joins and 1 left join in the next way:

SELECT orden_trabajo.orden_trabajo_pk, orden_trabajo.concepto, orden_trabajo.importe, cotizacion.cotizacion_pk, cotizacion.importe, factura_cliente.factura_cliente_pk,factura_cliente.importe, ing_responsable.nombre, ing_responsable.telefono, factura_cliente.fecha
    FROM orden_trabajo 
    RIGHT JOIN cotizacion
    ON cotizacion.orden_trabajo_id = orden_trabajo.orden_trabajo_pk
    RIGHT JOIN factura_cliente
    ON factura_cliente.orden_trabajo_id = orden_trabajo.orden_trabajo_pk
    LEFT JOIN ing_responsable
    ON orden_trabajo.ing_responsable_id = ing_responsable.ing_responsable_pk

And it shows the table i'm trying to look at, however i'd like that it creates another row with the second "orden_trabajo_pk" (159461) with the next cells empty

Here's an example of the table I get:

rowh1	rowh2	rowh3	rowh4	rowh5	rowh6	rowh7	rowh8	rowh9	rowh10
data1	data2	data3	data4	data5	data6	data7	data8	data9	data10

And this is the table i want

rowh1	rowh2	rowh3	rowh4	rowh5	rowh6	rowh7	rowh8	rowh9	rowh10
data1	data2	data3	data4	data5	data6	data7	data8	data9	data10
data1.1	data2.1	data3.1

Is there a way around it so it shows "incomplete" rows?

CodePudding user response：

Because of the right joins, the query you are using is semantically equivalent to this:

SELECT  orden_trabajo_pk,
        orden_trabajo.concepto,
        orden_trabajo.importe,
        cotizacion_pk,
        cotizacion.importe,
        factura_cliente_pk,
        factura_cliente.importe,
        nombre,
        ing_responsable.telefono,
        fecha
FROM    factura_cliente
        LEFT JOIN orden_trabajo
            ON factura_cliente.orden_trabajo_id = orden_trabajo_pk
        LEFT JOIN cotizacion
            ON cotizacion.orden_trabajo_id = orden_trabajo_pk
        LEFT JOIN ing_responsable
            ON ing_responsable_id = ing_responsable_pk;

So you are starting with the records from factura_cliente which only contains one of the values for orden_trabajo_id (159460) the other record from orden_trabajo will not be returned.

You can get the result you need simply by changing your right joins to left joins:

SELECT  orden_trabajo_pk,
        orden_trabajo.concepto,
        orden_trabajo.importe,
        cotizacion_pk,
        cotizacion.importe,
        factura_cliente_pk,
        factura_cliente.importe,
        nombre,
        ing_responsable.telefono,
        fecha
FROM    orden_trabajo
        LEFT JOIN cotizacion
            ON cotizacion.orden_trabajo_id = orden_trabajo_pk
        LEFT JOIN factura_cliente
            ON factura_cliente.orden_trabajo_id = orden_trabajo_pk
        LEFT JOIN ing_responsable
            ON ing_responsable_id = ing_responsable_pk;

Demo on db<>fiddle

As noted in a comment, my honest advice would be to steer clear of RIGHT JOINs. They are the cause of this issue for you, and ultimately any time you use a RIGHT JOIN the query can be written more logically (at least in my opinion) by changing the order of the tables and using LEFT JOIN.

As a final aside, I'd also recommend using aliases, this can be somewhat subjective, but I think most people would agree that it makes your queries faster to type, and easier to read, e.g.

SELECT  o.orden_trabajo_pk,
        o.concepto,
        o.importe,
        c.cotizacion_pk,
        c.importe,
        f.factura_cliente_pk,
        f.importe,
        i.nombre,
        i.telefono,
        f.fecha
FROM    orden_trabajo AS o
        LEFT JOIN cotizacion AS c
            ON c.orden_trabajo_id = o.orden_trabajo_pk
        LEFT JOIN factura_cliente AS f
            ON f.orden_trabajo_id = o.orden_trabajo_pk
        LEFT JOIN ing_responsable AS i
            ON i.ing_responsable_pk = o.ing_responsable_id;

CodePudding user response：

It seems that this should work for you as a solution of your desired output.

SELECT
    ot.orden_trabajo_pk,
    ot.concepto,
    ot.importe,
    c.cotizacion_pk,
    c.importe,
    fc.factura_cliente_pk,
    fc.importe,
    ir.nombre,
    ir.tel,
    fc.fecha
From orden_trabajo ot
LEFT JOIN cotizacion c
    ON ot.orden_trabajo_pk = c.orden_trabajo_id
LEFT JOIN factura_cliente fc
    ON ot.orden_trabajo_pk = fc.orden_trabajo_id
LEFT JOIN ing_responsable ir
    ON ot.ing_responsable_id = ir.ing_responsable_pk
ORDER BY ot.orden_trabajo_pk ASC