SQL JOIN - Build according to base

Asked

Viewed 59 times

0

I would like to elaborate an SQL using LEFT JOIN. I wrote this SQL, but it didn’t 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 there are records in the database. Can anyone suggest anything to me? Thank you

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

inserir a descrição da imagem aqui

sql

  • are not which database engine you are using, but you wouldn’t have to specify that you want a LEFT JOIN? Example: FROM cadastre profile as cp LEFT JOIN cadastroprofissional as cpr on (cp.codCadastroPerfil = cpr.codCadastroPerfil)?

  • Maybe yes but since there are several tables I think it could be in an SQL so

  • the first sentence of my comment became meaningless. I asked which database engine you are using. Anyway, I posted an answer. I hope it helps you.

  • Hello, André. Could you be more specific about what results you would like to return? Your SELECT did not make it very clear what the intent of the query is.

1 answer

1

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

This is possible and common. Below is a possible SQL based on the model and that uses the two 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 of the CP Cadastre table even if there is no corresponding (even Codcadastroperfil) in the tables Cadastrofotos e Cadastrolocal. If any field of these two tables is placed in SELECT, then they will come with NULL value.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.