Question 113140 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Right Join not showing entries with "incomplete" data

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search