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.

Possibly using a case statement you could check the condition and if <> then display value for tblNivel1... otherwise NULL... but you can't eliminate the column as a whole unless you use dynamic SQL. I'm not sure what you mean by inside this query for the value otherwise i might be able to pseudo code the case better. In addition I can't believe this is working as it appears to be mixing `inner join` (ANSII-92) and `,` (Non-ANSII-92) join notation which to my knowledge fails in most RDBMS systems. — xQbert, Dec 15 '15 at 22:44
You could also create a stored procedure that will extract tblAuxTipificacao.idTipificacao and then either execute a dynamic query or execute the query you wrote or execute a subset of the query you wrote. — zedfoxus, Dec 15 '15 at 22:47
@tiagoMatos I'm not saying one standard is better than the other; they both have their place. I'm saying I'm surprised the mySQL engine was capable of parsing the query when both are present. (Inner join notation along with `,` notation fails on the DB's I generally work with and I thought in mySQL as well... but maybe I just learned something! — xQbert, Dec 15 '15 at 22:54
@xQbert I think [This Link](http://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89) talks about the inner join and , part. I will try the stored procedure alternative provided by zedfoxus I was trying to only execute the tblNivelx part of the query if a certain condition was met. — Tiago Matos, Dec 15 '15 at 22:56

Drew · Accepted Answer · 2015-12-16T00:48:31.793

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 |
+------+--------+-------+

I understood your example. But i can't replicate it for what I'm trying to achieve. I've created this fiddle to try and make it work: [Fiddle](http://sqlfiddle.com/#!9/56cf7/4) — Tiago Matos, Dec 16 '15 at 01:35
feel free to join chat anytime after documenting all of it. Sample data, expected results ... http://chat.stackoverflow.com/rooms/95290/random-musings-drew — Drew, Dec 16 '15 at 02:01
I think you can do CASE ... WHEN without derived table, simply write `WHEN 'pencli' THEN p1.proof`, `WHEN 'pen' THEN p2.proof` — ierdna, Jan 26 '17 at 18:48

MySQL - Conditional Join

1 Answers1

Schema

Condition Join query

Use of derived table