Hi, I wanted to know if I'm doing the right thing with the following tables
NUMOBJ | IDIOMA
1 | E
1 | I
2 | E
2 | I
3 | E
3 | I
table1: each object with all possible languages (2)
NUMOBJ | IDIOMA
1 | E
1 | I
2 | I
3 | E
table2: each object with the languages it actually has
create view V as
select numobj,
case
when count(*) < 2 then 0
else 1
end as Tag
from Tabla1 as T1
left join Tabla2 as T2
on T1.numobj = T2.numobj
where T1.idioma = T2.idioma
GROUP BY T1.numobj, T2.numobj
This query adds a column Tag that will be 1 if the object of table 2 has 2 idiomas and will be 0 if it only has one, like this:
NUMOBJ | TAG
1 | 1
2 | 0
3 | 0
Now what I want to do is have another column that tells me what language the object is missing if it is 0, like this:
NUMOBJ | TAG | FALTA(N)
1 | 1 |
2 | 0 | E
3 | 0 | I
Does anyone know if this can be done? I have tried something but with the group by it does not let me use the columns that are not grouped ( IDIOMAS ).
I appreciate any help or hint, thank you very much.
R.
