1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
| SELECT DISTINCT
cli.IDClient AS IDClient,
cli.Type_Client AS Type_Client,
cli.IdMaisonMere AS IdMaisonMere,
cli.RaisonSocial AS RaisonSocial,
cli.Nom_complet AS Nom_complet,
cli.AdresseGeo AS AdresseGeo,
cli.IDDepartementGeo AS IDDepartementGeo,
cli.IDRegionGeo AS IDRegionGeo,
cli.Telephone AS Telephone,
cli.Telecopie AS Telecopie,
cli.Email AS Email,
cli.SiteWeb AS SiteWeb,
cli.BClient AS BClient,
cli.BFournisseur AS BFournisseur,
cli.BPartenaire AS BPartenaire,
cli.BProspect AS BProspect,
cli.IDCategorie AS IDCategorie,
cli.Civilite AS Civilite,
cli.Nom AS Nom,
cli.Prenom AS Prenom,
cli.CAAnneActuel AS Ca,
cli.SaisieLe AS SaisieLe,
CODEPOSTAUX.CodePostal AS CodePostal,
CODEPOSTAUX.Ville AS Ville,
SecteurActivite.Libelle AS Libelle,
ContactPrincipal.Nom + ' ' + ContactPrincipal.Prenom AS contact_principal,
ContactPrincipal.IdContact AS IDcontact_principal,
ContactDirigeant.Nom + ' ' + ContactDirigeant.Prenom AS Contact_Dirigeant,
Pays.Libelle AS Libelle_Pa,
Departement.Libelle AS Departement,
Region.Libelle AS Region,
CategorieSociete.Libelle AS Categorie_libelle,
client_mere.Nom_complet AS client_mere_nom
FROM
Client AS cli LEFT OUTER JOIN Client AS client_Mere ON CLI.IdMaisonMere = client_mere.IDClient,
cli LEFT OUTER JOIN Pays ON cli.IDPaysGeo = Pays.IdPays,
cli LEFT OUTER JOIN Departement ON cli.IDDepartementGeo = Departement.IDDepartement,
cli LEFT OUTER JOIN Region ON cli.IDRegionGeo = Region.IDRegion,
cli LEFT OUTER JOIN CODEPOSTAUX ON cli.IdCodePostalVilleGeo = CODEPOSTAUX.IDCodePostaux,
cli LEFT OUTER JOIN Contact AS ContactPrincipal ON cli.IDContactprincipal = ContactPrincipal.IdContact,
cli LEFT OUTER JOIN Contact AS ContactDirigeant ON cli.IDDirigeant = ContactDirigeant.IdContact,
cli LEFT OUTER JOIN SecteurActivite ON cli.IDSecteurActivite = SecteurActivite.IDSecteurActivite,
cli LEFT OUTER JOIN CategorieSociete ON cli.IDCategorie = CategorieSociete.IDCategorieSociete
WHERE
cli.Archive=0 AND
cli.IDCategorie = 1 AND
(cli.Nom_complet LIKE 'Test' OR CODEPOSTAUX.Ville LIKE 'Test') AND
cli.IDRegionGeo = 12 AND
cli.IDDepartementGeo = 75 AND
cli.IdResponsable IN (2) AND
(( cli.Type_Client = 1 OR cli.Type_Client = 2 OR cli.Type_Client = 3) AND
(cli.Type_Client <> 4 AND cli.Type_Client <> 5 AND cli.Type_Client <> 6)) AND
( cli.BProspect = 1 OR cli.BClient = 1 OR cli.BFournisseur = 1 OR cli.BPartenaire = 1) |
Partager