3
would like help in building the following logic:
I have my main table and two other tables A and B, which have foreign key from the main.
When an X column is filled in table A I need to classify my information as "type 1" When there is any field in table B where B.id_foreign = P.id_main. I need to classify my information as "Type 2"
But in field X of table A there are some only filled with 'comma,' in these cases, even when I have filled in with a comma and there is nothing in table B then my information is still "type 1"
And if it is only filled with a ',' comma and there is a record in table B then it will only be of "type 2"
And last when column X of table A is filled and there is record in table B then it is information of "type 1 and type 2"
As you can see in this example the ID’s 1 and 3 of the main table should be classified as type 1
ID 6 should only be of type 2
and the other id being as type 1 and two
With the following query
select
(
CASE WHEN (A.de_inf is not null or A.de_inf != '') and A.de_inf != ','
AND EXISTS (select tit_inf from B where tit_inf IS NOT NULL OR tit_inf != '')
THEN 'Tipo 1 e Tipo 2'
ELSE
CASE WHEN A.de_inf = ',' or (A.de_inf is not null or A.de_inf != '')
AND NOT EXISTS (select tit_inf from B where tit_inf IS NOT NULL OR tit_inf != ''
)
THEN 'Tipo 1'
ELSE
CASE WHEN A.de_inf = ','
AND EXISTS (select tit_inf from B where tit_inf IS NOT NULL OR tit_inf != '')
THEN 'Tipo 2'
end
end
end
) as tipo
from
principal as p
left join A on p.id_inf = A.id_inf
I got this result
or also in this way:
SELECT
p.id_inf,
(
CASE WHEN A.de_inf != ',' THEN
CASE WHEN A.de_inf is not null or A.de_inf != '' THEN
CASE WHEN EXISTS (select tit_inf from B where tit_inf IS NOT NULL OR tit_inf != '')
THEN 'Tipo 1 e 2' ELSE 'Tipo 1'
END
end
else
'Tipo 2'
END
) AS tipo
from
principal as p
left join A on p.id_inf = A.id_inf
If id=6 does not exist in table B why would it be Type 2? Or its description is wrong?
– anonimo
id=6 exists in table B, and in table A it is only filled with ',' so it should be type 2
– Juan andrade
Didn’t I notice, with the queries you built no longer have your answer? If not, can you put an image or description of what would be the expected result?
– João Martins
If id=6 exists in table B then the table listed is wrong or incomplete.
– anonimo
The table is correct, but it is not ordered in the id_inf field, the 6 is before the 5. I see no problem in this, must have been lack of attention of the crowd.
– Giovanni Machado