I use the DISTINCT keyword to avoid returning duplicates from the query
below (multivariable search for plants). However the result does contain
duplicates and I just can't figure out why. Please help!

TIA

CREATE PROCEDURE dbo.usp_MultivarSearch_Plantes
(
@Value   varchar(256) ,
@CategoryID    int  ,
@DescPlantHerb varchar(64) ,
@AcclimatCanada varchar (64) ,
@EnvCult  varchar (145) ,
@RareMenacee  varchar (64) ,
@PaysOrig  int  ,
@PropTherapeut int  ,
@PropAutres  int  ,
@Ingredients  int
)
AS

set nocount on

SELECT DISTINCT
pTax.PlanteID   ,
pTax.nomlatinscientifique   ,
pTaxCat.CategoryID   ,
pTax.descriptiontechniquebotanique ,
pCul.AcclimatationauCanada  ,
pCul.environnementdeculture  ,
pTax.raretemenacee   ,
pPays.PaysID    ,
pOPTH.ValueID    ,
pOPA.ValueID    ,
pOI.ValueID

FROM
PlantesTaxonomie pTax
  left join PlantesTaxonomieCategory pTaxCat on pTax.PlanteID =
pTaxCat.PlanteID
  left join PlantesCulture pCul on pCul.PlanteID = pTax.PlanteID
  left join PlantesBiochimieChimiotype pbChType on pbChType.PlanteID =
pTax.PlanteID
  left join PlantesBiochimieChimiotypePays pPays on pPays.ChimiotypeID =
pbChType.ID
  left join PlantesOpp pOpp on pOpp.PlanteID = pTax.PlanteID
  left join PlantesOppPropTherapeutique pOPTH on pOPTH.PlanteOppID = pOpp.ID
  left join PlantesOppPropAutres pOPA on pOPA.PlanteOppID = pOpp.ID
  left join PlantesOppIngredients pOI on pOI.PlanteOppID = pOpp.ID

WHERE
pTax.nomlatinscientifique like '%'+ @Value +'%'
or pTaxCat.CategoryID = @CategoryID
or pTax.descriptiontechniquebotanique like '%'+ @DescPlantHerb +'%'
or pCul.AcclimatationauCanada like '%'+ @AcclimatCanada +'%'
--and pCul.environnementdeculture= @EnvCult
or  pTax.raretemenacee  like '%'+ @RareMenacee +'%'
or pPays.PaysID = @PaysOrig
or pOPTH.ValueID = @PropTherapeut
or pOPA.ValueID = @PropAutres
or pOI.ValueID = @Ingredients

set nocount off
GO

Without sample data, it's difficult to tell; however, I'd check to make
sure that the values you think are duplicates are actually duplicates.
Look for extra spaces or carriage returns at the end of your varchar
values.

HTH,
Stu

Ahhhh... I misunderstood the question.  I thought the OP was trying to
remove dupes from the original query so that distinct would not be
necessary.

Show quoteHide quote

You need to include your DDL.
http://www.aspfaq.com/etiquette.asp?id=5006

Without knowing your keys we cannot determine why you are getting
duplicates.

My guess is that you are not joining on your full keys for at least one
join.

Show quoteHide quote

Here:

DDL
============================

CREATE TABLE [PlantesTaxonomie] (
[PlanteID] [int] NOT NULL ,
[nomlatinusite] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[nomlatinscientifique] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[famille] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[groupe] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[auteur] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[nomcommunanglais] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[nomcommercial] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[nomcommunfrançais] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[cledeclassification] [varchar] (140) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[cledeclassificationimage] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[nomscommunsautres] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[synonymes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[formescitees] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[nomautresinformation] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[referencedeFloras] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[PFAFRating] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[epithete] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Nombredechromosomes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[identificationherbier] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[descriptiontechniquebotanique] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[taxonssemblables] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[Cultivars] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[referenceautresinformation] [text] COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[nomcycledevie] [varchar] (192) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[codealphabetique] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[Typeplantes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[descriptiondelafleur] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[couleurdelafleur] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[fleursramet] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[inflorescence] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[desriptiondesfeuilles] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[cotyledons] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Moisdefloraisondebut] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Moisdefloraisonfin] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Moisdefloraisonrecurrence] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Floraisondusemis] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[fruitdescription] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[vigueurdesemences] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[longevitedesemences] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[descriptionracines] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[descriptiontronctiges] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[habitatbiotypepHmin] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[habitatbiotypepHmax] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[habitatbiotypepHmoy] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[habitatbiotypeprecipitationmin] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[habitatbiotypeprecipitationmax] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[habitatbiotypeprecipitationmoy] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[habitatbiotypetemperature] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[plantehauteurmaximale] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[hauteurminimale] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[plantelargeur] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Morphologieousilhouette] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[plantephysiologie] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[plantevigueur] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[descriptionautresinformation] [text] COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[ensoleillementremarques] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[sourcegenetique] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[distributiongeographiqueabondance] [varchar] (144) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[pollenbiologieconservation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[semencessporebiologie] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[raretemenacee] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[particulariteautresinformation] [text] COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[datelastmodified] [datetime] NULL ,
[lastmodifiedby] [int] NULL ,
[IsDirty] [bit] NULL CONSTRAINT [DF_PlantesTaxonomie_IsDirty] DEFAULT (1),
[ApprovedBy] [int] NULL ,
CONSTRAINT [PK_PlantesTaxonomie] PRIMARY KEY  CLUSTERED
(
  [PlanteID]
)  ON [PRIMARY] ,
CONSTRAINT [FK_PlantesTaxonomie_Plantes] FOREIGN KEY
(
  [PlanteID]
) REFERENCES [Plantes] (
  [ID]
) ON DELETE CASCADE  ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

---

CREATE TABLE [PlantesCulture] (
[PlanteID] [int] NOT NULL ,
[Paysregionsdeculture] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Paysfournisseurs] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[origineautres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Climatprefere] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[AcclimatationauCanada] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[climatautres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[maladiesinsectes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[Exigencesdefertilisation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Preparationduterrain] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[rotation] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[tauxdesemis] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[transplantation] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[fertilite] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[agroautres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[besoineneauarrosage] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[arrosagefrequence] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[humiditedusol] [varchar] (155) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[collectionourecoltesauvage] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[culturecomplet] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[cultureduree] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[engrais] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Exigencesdefertilisationculturale] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[essaisdeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[espacement] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[distancedeplantation] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[plantationenpot] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[profondeurdeplantation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[cultureinvitro] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[autresnotesdeculture] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[modedeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[habitatendroit] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[pH] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[regiondeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[sol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[qualitedusol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[texturedusol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[drainagedusol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[structuredusol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[niveauensoleillementrequis] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[zonederusticite] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[fichetechniqueagricole] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Valeurculturale] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[besoinenmatiereorganique] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[environnementdeculture] [varchar] (145) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[controledeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[conditionsautres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[cultureenforet] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[regiedeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[regiephytosanitaire] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Invasionparametrescontrole] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Restorationactivites] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Entretien] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Environnementurbainadaptation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Programmedeconservation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Problemesdeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[fichetechniquehorti] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Cultureautresnotes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[culturedesemisennature] [varchar] (145) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Maintenanceentretien] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[autresconditionsculture] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[datelastmodified] [datetime] NULL ,
[lastmodifiedby] [int] NULL ,
[IsDirty] [bit] NULL CONSTRAINT [DF_PlantesCulture_IsDirty] DEFAULT (1),
[ApprovedBy] [int] NULL ,
CONSTRAINT [PK_PlantesCulture] PRIMARY KEY  CLUSTERED
(
  [PlanteID]
)  ON [PRIMARY] ,
CONSTRAINT [FK_PlantesCulture_Plantes] FOREIGN KEY
(
  [PlanteID]
) REFERENCES [Plantes] (
  [ID]
) ON DELETE CASCADE  ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

---

CREATE TABLE [PlantesBiochimieChimiotype] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PlanteID] [int] NOT NULL ,
[constituants] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[teneurpourcentage] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[partieplante] [int] NULL ,
[partieplanteautre] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[descriptiondrogue] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[structuremoleculaire] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[usagespotentiels] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[extrait] [int] NULL ,
[indicederefraction] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[pouvoirrotatoire] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[teneurenproduitspourcentage] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[odeuretsaveur] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[pointebullition] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[pointdefusion] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[pointdecongelation] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[pointdevaporisation] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[solubilite] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[autres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[testsmicroorganismes] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[testsanimauxlaboratoire] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[essaiscliniques] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[essaistoxicologiques] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
CONSTRAINT [PK_PlantesBiochimieChimiotype] PRIMARY KEY  CLUSTERED
(
  [ID]
)  ON [PRIMARY] ,
CONSTRAINT [FK_PlantesBiochimieChimiotype_Plantes] FOREIGN KEY
(
  [PlanteID]
) REFERENCES [Plantes] (
  [ID]
) ON DELETE CASCADE  ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

---

CREATE TABLE [PlantesBiochimieChimiotypePays] (
[ChimiotypeID] [int] NOT NULL ,
[PaysID] [int] NOT NULL ,
CONSTRAINT [PK_PlantesBiochimieChimiotypePays] PRIMARY KEY  CLUSTERED
(
  [ChimiotypeID],
  [PaysID]
)  ON [PRIMARY]
) ON [PRIMARY]
GO

---

CREATE TABLE [PlantesOpp] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PlanteID] [int] NOT NULL ,
[OppType] [int] NULL ,
[OppName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[UsageType] [int] NULL ,
[UsageTypeID] [int] NULL ,
[UsageOther] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[description] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[autresinfo] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[aspecteconomiques] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[propintellectuelle] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[DateLastModified] [datetime] NULL CONSTRAINT
[DF_PlantesOpp_DateLastModified] DEFAULT (getdate()),
[LastModifiedBy] [int] NULL ,
[IsDirty] [bit] NULL CONSTRAINT [DF_PlantesOpp_IsDirty] DEFAULT (1),
[ApprovedBy] [int] NULL ,
CONSTRAINT [PK_PlantesOpp] PRIMARY KEY  CLUSTERED
(
  [ID]
)  ON [PRIMARY] ,
CONSTRAINT [FK_PlantesOpp_Plantes] FOREIGN KEY
(
  [PlanteID]
) REFERENCES [Plantes] (
  [ID]
) ON DELETE CASCADE  ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

---

CREATE TABLE [PlantesOppPropTherapeutique] (
[PlanteOppID] [int] NOT NULL ,
[ValueID] [int] NOT NULL ,
CONSTRAINT [PK_PlantesOppPropTherapeutique] PRIMARY KEY  CLUSTERED
(
  [PlanteOppID],
  [ValueID]
)  ON [PRIMARY]
) ON [PRIMARY]
GO

---

CREATE TABLE [PlantesOppPropAutres] (
[PlanteOppID] [int] NOT NULL ,
[ValueID] [int] NOT NULL ,
CONSTRAINT [PK_PlantesOppPropAutres] PRIMARY KEY  CLUSTERED
(
  [PlanteOppID],
  [ValueID]
)  ON [PRIMARY]
) ON [PRIMARY]
GO

---

CREATE TABLE [PlantesOppIngredients] (
[PlanteOppID] [int] NOT NULL ,
[ValueID] [int] NOT NULL ,
CONSTRAINT [PK_PlantesOppIngredients] PRIMARY KEY  CLUSTERED
(
  [PlanteOppID],
  [ValueID]
)  ON [PRIMARY]
) ON [PRIMARY]
GO



DATA
==============================

INSERT INTO [PlantesTaxonomie] VALUES(1,'Narthecium americanum','Abama
americana (Ker-Gawl.) Morong','Liliaceae','','','','','','',NULL,'','','','
','','','','','','','','','
','','','','','','','','','','8','8','8','','','','','','','','','','','','','','

INSERT INTO [PlantesTaxonomie] VALUES(79144,'Trillium cernuum L.','Trillium
cernuum L.','Liliaceae','','','whip-poor-will
flower','','','',NULL,'','','',' ','','','','','','','','','
','','','','','','','','','','8','8','8','','','','','','','','','','',''

INSERT INTO [PlantesTaxonomie] VALUES(82756,'planta testae','planta
testae','','','','test plant','','plante de test','',NULL,'','','','
','','','','','','Description plante - herbier','','','
','','','','','','','','','','8','8','8','','','','','','','','

---

INSERT INTO [PlantesCulture] VALUES(1,'','',' ','','','
','','','','','','','',' ','','','','','','','','','','','','','','','
','3','','','','1','1','','','2','','1','','','','2','','
','','','','','3','','3','','','','','','',' ','Nov 18 2005 11:54:36:19

INSERT INTO [PlantesCulture] VALUES(82756,'','',' ','','bien acclimatée','
','','','','','','','',' ','','','','','','','','','','','','','','','
','3','','','','1','1','','','2','','1','','','','2','','
','','','','','3','','3','','','','','','',' ','Nov

---

INSERT INTO [PlantesBiochimieChimiotype]
VALUES(1,1,'','',1,'','','','',1,'','','','','','','','','',' ','Le
méthyleugénol a des propriétés antimicrobiennes (Grifin et al., 1998).
Propriétés nutritives      L'élémicine et le méthyleugénol possèdent des p

INSERT INTO [PlantesBiochimieChimiotype]
VALUES(80,79142,'','',9,'ynhryeberyb','','','',3,'','','','','','','','','','
',' ',' ',' ',' ')

INSERT INTO [PlantesBiochimieChimiotype]
VALUES(81,82756,'constituants...','10',9,'','description -
drogue...','','Usages potentiels...',3,'','','','','','','','','',' ',' ','
',' ',' ')

---

INSERT INTO [PlantesBiochimieChimiotypePays] VALUES(1,2)
INSERT INTO [PlantesBiochimieChimiotypePays] VALUES(1,19)
INSERT INTO [PlantesBiochimieChimiotypePays] VALUES(80,4)
INSERT INTO [PlantesBiochimieChimiotypePays] VALUES(81,36)

---

INSERT INTO [PlantesOpp] VALUES(18,79142,2,'fruit',-1,-1,'',' ',' ',' ','
','Nov 18 2005  3:01:55:547PM',48,1,NULL)
INSERT INTO [PlantesOpp] VALUES(19,82756,3,'opp test',-1,-1,'',' ',' ',' ','
','Nov 18 2005  4:28:34:680PM',2,1,NULL)

---

INSERT INTO [PlantesOppPropTherapeutique] VALUES(1,1)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(1,7)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(5,1)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(13,3)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(13,7)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(13,11)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(14,5)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(14,9)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(14,12)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(19,3)

---


INSERT INTO [PlantesOppPropAutres] VALUES(1,2)
INSERT INTO [PlantesOppPropAutres] VALUES(5,3)
INSERT INTO [PlantesOppPropAutres] VALUES(13,2)
INSERT INTO [PlantesOppPropAutres] VALUES(19,2)

---

INSERT INTO [PlantesOppIngredients] VALUES(1,2)
INSERT INTO [PlantesOppIngredients] VALUES(5,3)
INSERT INTO [PlantesOppIngredients] VALUES(13,4)
INSERT INTO [PlantesOppIngredients] VALUES(19,3)







Show quoteHide quote

For the Primary Key(s) that you are seeing more than once, you will find
that you can (and do) have more than one entry in some of the following
tables:

---
PlantesTaxonomie, PlantesCulture, PlantesBiochimieChimiotype, and PlantesOpp
Can have more than one row for every Plantes.ID
---
PlantesOppPropTherapeutique, PlantesOppPropAutres, and PlantesOppIngredients
Can have more than one row for every PlantesOpp.ID

Because these tables have more than one row that is tied to the PK on
Plantes and PlantesOpp, you are going to get more than one row for each
Plantes.ID in your query results.  This is correct, and is what you are
supposed to get, based on your data structure.

If you want to get only one row for each Plants.ID, then only select columns
from that table.  As long as you are selecting data from these other tables,
you will get multiple rows per plants.ID and PlantesOpp.ID.

Show quoteHide quoteMorong','Liliaceae','','','','','','',NULL,'','','','
','','','','','','','','','','8','8','8','','','','','','','','','','','',''
,'','
Show quoteHide quoteVALUES(80,79142,'','',9,'ynhryeberyb','','','',3,'','','','','','','','','',
'
Show quoteHide quote

Are you sure for two rows that look like duplicates, that EVERY SINGLE
COLUMN is the same?  Distinct applies to *every* column, not just the first
one.

A

Absolutely, I can see the same PK several times in the resultset in Query
Analyzer.


Show quoteHide quote

You should expect to get the same primary key multiple times because you are
joining many different tables.  Some of these tables have more than one row
with that PK, because they are using it as a foreign key in a one to many
relationship.

As Aaron explained, look at all the other columns in your results, one of
them is different, which is what you are supposed to get.

Show quoteHide quote

Yes, is the PK the only column in your resultset?  NO!  DISTINCT applies to
ALL COLUMNS, not just the PK.

A

Thanks Aaron, Jim, Stu and Roy,

you were right, I can see different values in other columns. But then, how
to restrict DISTINCT to only some of the columns? To my knowledge, this is
not possible.

---

Show quoteHide quote

alto wrote:
You can use GROUP BY for that. Exactly how you deal with the other
non-grouped columns depends on your requirements (Min? Max? Something
else?).

Yes, but w/ exactly the same effect as DISTINCT, rigth?
I can't exclude a column from the GROUP BY clause and have it in the SELECT
list.


Show quoteHide quote

Unless you use an aggregate function, such as MIN or MAX.

Roy

alto wrote:
Depends what you mean. If you don't want to GROUP BY column X then
which value of the many potential values in column X do you want to
display?

You need to define what you want in the rest of the columns (if you need
them at all).  For example, let's say you have this:

NAME    ACTION    DATE
J.Snirek    Hired    2006-02-25
J.Snirek    Fired    2006-04-17
A.Tnarg    Hired    2006-01-12

If you only want one row for the name J.Snirek, what should appear in Action
and Date?

You're right again, only the the first 2 columns from the SELECT list list
are actually needed. Should have discovered it myself. Sorry, think I wasted
your time for something obvious...

Thank you all guys!

Show quoteHide quote

The question comes down to this: for a given value of the column(s)
that you want to be distinct, what do you expect to see in the other
columns?  There are multiple rows, and you apparently do not want to
see all the rows, but what DO you want to see?  Any row? The "first"
row?  (Warning: there is no "first" row!)  Any value from among all
the values in a column?  You have to know what you want, and explain
it, then someone can, perhaps, explain how to achieve it.

Roy Harvey
Beacon Falls, CT

Do you want results like this?

Current Results:
Customer, Fruit, Quantity
Joe        ,Orange,  30
Joe        ,Apple,    95
Joe        ,Grape,    10
Mary     ,Apple,    23
Mary     ,Orange,  4
Tom      ,Apple,    5

Desired Results:
Customer, Fruit, Quantity
Joe        ,Orange,  30
             ,Apple,    95
             ,Grape,    10
Mary     ,Apple,    23
             ,Orange,  4
Tom      ,Apple,    5

Show quoteHide quote

Jim,

as I already replied Aaron, my approach was wrong from the very beginning.
Only the first 2 columns from the SELECT list are actually needed. I'm sorry
for wasting so many persons' time w/ something obvious... Apologies!

Many thanks again!

Show quoteHide quote

That result looks very familiar.Are you using Rac?

www.rac4sql.net



Show quoteHide quote

DISTINCT removes duplicates.  If it appears that the query is
returning duplicates despite the DISTINCT there are two possibilities.
Most likely is that there is at least one difference that is not
immediately apparent.  The other is a bug in SQL Server.

One issue that can't be ignored is that sometimes questions here that
involve DISTINCT (or GROUP BY) and "duplicates" are the result of a
misunderstanding of what DISTINCT does.  DISTINCT operates on the
entire row.  ANY difference in ANY column will result in seperate
rows.

One approach to narrowing down the problem is to play with the SELECT
list.  Run the query - including the DISTINCT - specifying just one
column at a time in the SELECT list.  Inspect the results closely,
looking for what appear to be duplicates.  Apparent duplicates should
then be inspected with such tools as DATALENGTH and character by
character ASCII(substring()).

Roy Harvey
Beacon Falls, CT


Show quoteHide quote

Local MDF Files--can I make this work?
Shrink database?
Problem with Grouping
Top 10 clients per case type & country
Which query will be faster ?