0
VOUCHER table: code, subproject Table SUBPROJECT: code, name
my select:
select a.codigo, q.nome as nomesubprojeto, a.subprojeto from comprovante a, subprojeto q where a.subprojeto = q.codigo and a.codigo = 3
works normally when in the subproject fieldsay I have something registered but some of these fields are NULL
when I give that select and the foreign key is null, returns nothing
as I would to return but saying that nomesuprojeto is null?
2
The way you rode your SELECT you’re making a INNER JOIN.
Replace with a LEFT OUTER JOIN:
SELECT a.codigo, q.nome as nomesubprojeto, a.subprojeto
FROM comprovante a LEFT OUTER JOIN subprojeto q ON a.subprojeto = q.codigo
WHERE a.codigo = 3;
1
Use something like that:
SELECT a.codigo,
ISNULL(q.nome, '') AS nomesubprojeto,
a.subprojeto
FROM comprovante a
LEFT JOIN subprojeto q ON a.subprojeto = q.codigo
WHERE a.codigo = 3
1
There is no secret - the SQL language was made to look English and only following the logic of its "Where" is it easy to understand that there will be no record for which the "a. subproject" is NULL and has a "NULL" equivalent in q.code. This query should work:
select a.codigo, q.nome as nomesubprojeto, a.subprojeto from
comprovante a, subprojeto q
where (a.subprojeto = q.codigo OR a.subprojeto IS NULL) AND a.codigo = 3
A more specific way, and that will be much more familiar to those who are already accustomed to SQL is to use the intermediate clause of "Join" to indicate the junction of tables before the clause "Where". In this case, since you want results from the table on the left even when there is no corresponding record, the correct is the LEFT JOIN:
SELECT a.codigo, q.nome as nomesubprojeto, a.subprojeto
FROM comprovante as a
LEFT JOIN subprojeto as q
ON a.subprojeto = q.codigo
where a.codigo = 3
I need something like this because actually my query is giant, I just put a little bit. only need one line, like this example: Where (a.subproject = q.codigo OR a.subproject IS NULL) , only in this example it returned all subprojects to me, then at the time of displaying the chosen name, instead of returning null it displays the first of the list
with LEFT JOIN, the subproject should be simply "NULL".
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.
Why not use INNER JOIN or LEFT JOIN?
– Setimo Muteca Jr.
You want to make a
LEFT OUTTER JOINbut is making aINNER JOINimplicit. There is a p/ notation to do this implicitly but it is much better to get used to writing SQL in explicit notation– jean