Select all customers and check if you are carrier and supplier

Question

I want to make a select that selects all customers and next create two columns a carrier call and another supplier and in each of them mark yes or no.

Ex:

     Código Nome   Fornecedor   Transportadora
     01     João    Sim          Não
     02     Maria   Não          Sim

I have the selects:

-- carrier schedule:

     select TraCod TR from T0055 TR, T0158 A where A.CliCod = TR.TraCod

-- supplier table

     select * from T0185 F, T0158 A WHERE A.CliCod = F.ForCod

-- client table

     select CliNom, CliReG, Clicgc, CliCgc2, CliNascCon, CliCid, CliCodUnf, 
     CliEnd, CliNum, CliBai, CliComple, CliCep, CliEmail, CliFon, CliFonCel  
      FROM T0158

Answer 1

In the description of the problem it is stated that the link between the tables of carriers and customers is through the pair {Tracod, Clicod} and that the link between the tables of suppliers and customers is through the pair {Forcod, Clicod}.

Considering this, here’s a suggestion:

-- código #1
SELECT C.CliCod, C.CliNom, 
       case when T.Forcod is not null then 'Sim' else 'Não' end as Fornecedor,
       case when F.Tracod is not null then 'Sim' else 'Não' end as Transportadora
  from T0158 as C
       left join T0185 as F on F.ForCod = C.CliCod
       left join T0055 as T on T.TraCod = C.CliCod;

Answer 2

--Fornecedores
Select C.CliCod As [Código], C.CliNom As [Nome],'Não' As Fornecedor,'Não' As Transportadora
From T0158

Union All
--Fornecedores
Select F.ForCod As [Código], F.ForNom As [Nome],'Sim' As Fornecedor,'Não' As Transportadora
From T0185 As F

Union All
--Transportadora
Select Tr.TraCod As [Código], Tr.TraNom As [Nome],'Não' As Fornecedor,'Sim' As Transportadora
From T0055 As Tr