Select all customers and check if you are carrier and supplier

Question

Select all customers and check if you are carrier and supplier

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I want to make a select that selects all the clients and next to it create two columns a carrier call and another provider and in each of them to 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 table:

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

- Vendor table

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

- customer table

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

sql sql-server

asked by anonymous 06.09.2018 / 22:35

2 answers

How to move an array in the correct way How to kill the process automatically with Popen?

score 2 · Answer 1

The problem description states that the connection between the carrier and client tables is through the {TraCod, CliCod} pair, and that the connection between the customer and supplier tables is through the {ForCod, CliCod} pair.

Considering this, here is 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;

score 0 · 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