Reputation: 59
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.
Upvotes: 0
Views: 1646
Reputation: 24970
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.
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);
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 |
+------+--------+-------------+----------+
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 |
+------+--------+-------+
Upvotes: 2