MySQL - Conditional Join

Question

I have an MySQL that is working correctly:

SELECT
      tblprocedimento.idProcedimento,
      tblprocedimento.tituloProcedimento,
      tblprocedimento.tipoProximoS,
      tblprocedimento.numeroProximoS,
      tblprocedimento.tipoProximoN,
      tblprocedimento.numeroProximoN,
      ap1.nomeProximo AS nomeProximoS,
      ap2.nomeProximo AS nomeProximoN,
      tblauxtextoprocedimento.textoProcedimento,
      tblauxprocedimento.sTipificacao,
      tblNivel1.nivel1,
      tblNivel2.nivel2,
      tblNivel3.nivel3,
      tblNivel4.nivel4   
    FROM
    tblAuxTextoProcedimento
    LEFT JOIN 
    tblAuxProcedimento 
    ON 
    tblAuxTextoProcedimento.idTextoProcedimento = tblAuxProcedimento.sTextoProcedimento,
    tblNivel4 INNER JOIN (tblNivel3
    INNER JOIN (tblNivel2
    INNER JOIN (tblNivel1
    INNER JOIN tblAuxTipificacao
    ON tblNivel1.idNivel1 = tblAuxTipificacao.sNivel1)
    ON tblNivel2.idNivel2 = tblAuxTipificacao.sNivel2)
    ON tblNivel3.idNivel3 = tblAuxTipificacao.sNivel3)
    ON tblNivel4.idNivel4 = tblAuxTipificacao.sNivel4,
      tblprocedimento,
      tblauxproximo As ap1,
      tblauxproximo As ap2
    WHERE
      ap2.idProximo = tblprocedimento.sProxN AND
      ap1.idProximo = tblprocedimento.sProxS AND
      tblprocedimento.idProcedimento = 1 AND
      tblAuxProcedimento.sProcedimento = 1 AND
      tblAuxTipificacao.idTipificacao = 130

I would like to know If It's possible to only query tblNivel1, tblNivel2, tblNivel3 and tblNivel4 IF tblAuxTipificacao.idTipificacao <> value inside this query.

What's the best practice to do so or should I rewrite the query.

Answer 1

These are conditional joins. When you see the need for one, think Left Join. In the following example, the type and proof columns show the concept works.

It is understandable than many think case when ... when the question is asked. But that is clearly not what we are talking about.

So, to reiterate, the proof columns below prove that the correct table can be accessed. If you wanted, you could further wrap it in another nested derived table, using a case when or if, and limit the row output.

I might have time to show that as an edit in a little bit.

Schema

create table items
(   sku varchar(20) primary key,
    type varchar(20) not null
);

insert items(sku,type) values ('0101','pencil'),('0292','pen'),('0294','pen');


create table pencils
(   sku varchar(20) primary key,
    leadType varchar(20) not null,
    color int not null,
    proof int not null
    -- FK not shown
);

insert pencils(sku,leadType,color,proof) values ('0101','No2',7,100);

create table pens
(   sku varchar(20) primary key,
    inkType varchar(20) not null,
    color int not null,
    erasable bool not null,
    proof int not null
    -- FK not shown
);

insert pens(sku,inkType,color,erasable,proof) values 
('0292','Squid',2,false,200),
('0294','Shrimp',33,true,300);

Condition Join query

select i.sku,i.type,p1.proof as PencilProof,p2.proof as PenProof  
FROM items i 
LEFT JOIN pencils p1 on p1.sku = i.sku and i.type = 'pencil' 
LEFT JOIN pens p2 on p2.sku = i.sku and i.type = 'pen';

+------+--------+-------------+----------+
| sku  | type   | PencilProof | PenProof |
+------+--------+-------------+----------+
| 0101 | pencil |         100 |     NULL |
| 0292 | pen    |        NULL |      200 |
| 0294 | pen    |        NULL |      300 |
+------+--------+-------------+----------+

Use of derived table

This simplifies the output with an outer wrapper, including a CASE/WHEN, and using the above query:

select sku,type, 
CASE type 
    WHEN 'pencil' THEN PencilProof 
    WHEN 'pen' THEN PenProof 
    ELSE -1 
END as Proof 
from 
(   select i.sku,i.type,p1.proof as PencilProof,p2.proof as PenProof 
    FROM items i 
    LEFT JOIN pencils p1 on p1.sku = i.sku and i.type = 'pencil' 
    LEFT JOIN pens p2 on p2.sku = i.sku and i.type = 'pen' 
) xDerived; -- every derived table needs a name (even if not used explicitly)

+------+--------+-------+
| sku  | type   | Proof |
+------+--------+-------+
| 0101 | pencil |   100 |
| 0292 | pen    |   200 |
| 0294 | pen    |   300 |
+------+--------+-------+