SQL JOIN - Build according to the base

Question

SQL JOIN - Build according to the base

Navigation

#1 by (1 votes)

0

I would like to create a SQL using LEFT JOIN. I did this SQL, but it did not work:

SELECT 
    *, 
    p.parametro AS cidade, 
    cpr.codTipo AS tipoAcompanhante 
FROM 
    cadastroperfil AS cp, 
    cadastroprofissional AS cpr, 
    parametro AS p 
WHERE 
    cp.verificado = '1' 
        AND 
    cp.codCadastroPerfil = '11'
        AND 
    cpr.codCadastroPerfil = '11'
        AND 
    cp.codCidade = p.idParametro

The return is zero, but has records in the database. Can anyone suggest anything to me? Thankful

All these tables have relationship with each other, through the CodeProfile

sql

asked by anonymous 09.08.2015 / 02:24

1 answer

How to reset form state with model data? JavaScript check if Windows user is enrolled in MS Access Bank

score 1 · Answer 1

Based on the question and comment, I believe the question is in using INNER JOIN with LEFT JOIN in a Query only.

This is possible and common. Below is a possible SQL-based model that uses both types of Joins:

SELECT
*
FROM
CadastroPerfil CP
INNER JOIN CadastroProfissional CPS
ON (CP.CodCadastroPerfil = CPS.CodCadastroPerfil)
LEFT JOIN CadastroFotos CF
ON (CP.CodCadastroPerfil = CF.CodCadastroPerfil)
LEFT JOIN CadastroLocal CL
ON (CP.CodCadastroPerfil = CL.CodCadastroPerfil)
INNER JOIN CadastroPessoal CPE
ON (CP.CodCadastroPerfil = CPE.CodCadastroPerfil)
INNER JOIN Parametro P
ON (CP.CodCadastroPerfil = P.CodCadastroPerfil)
WHERE
cp.verificado = '1' 
AND cp.codCadastroPerfil = '11'

The above SQL will return records from the CP Profiler table even though there is no corresponding (even ProfilerCode) in the CadastroFotos and CadastroLocal tables. If any field of these two tables is placed in the SELECT, then they will come with NULL value.