Logic SELECT CASE WHEN

Asked

Viewed 725 times

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"

main table: Tabela principal:

table A tabela A

table B Tabela B

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

exemplo

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

exemplo2

mysql sql sql-server postgresql logic

  • If id=6 does not exist in table B why would it be Type 2? Or its description is wrong?

  • id=6 exists in table B, and in table A it is only filled with ',' so it should be type 2

  • 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?

  • If id=6 exists in table B then the table listed is wrong or incomplete.

  • 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.

1 answer

0

I don’t know if that’s exactly what you want but the query below reaches the result expected by you:

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 = ',' AND (A.de_inf is not null or A.de_inf != '')
            AND NOT EXISTS (select tit_inf from B where  B.ID_inf = A.ID_INF
        )  
            THEN 'Tipo 1'
ELSE

CASE WHEN  A.de_inf = ',' 
    AND EXISTS (select tit_inf from B where tit_inf != '') 
    THEN 'Tipo 2'

            end 
            end
            end

) as tipo,A.ID_INF

from
principal as p

left join A on p.id_inf = A.id_inf

Browser other questions tagged mysql sql sql-server postgresql logic

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