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
GOWithout 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|||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.
"alto" <altodorov@.hotmail.com> wrote in message
news:%23ew9spiaGHA.4612@.TK2MSFTNGP03.phx.gbl...
> 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
>|||>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!
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|||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.
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1146163975.702036.262020@.u72g2000cwu.googlegroups.com...
> 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
>|||Absolutely, I can see the same PK several times in the resultset in Query
Analyzer.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZa8fyiaGHA.4580@.TK2MSFTNGP03.phx.gbl...
> 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
>|||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.
"alto" <altodorov@.hotmail.com> wrote in message
news:uwRaO4iaGHA.4772@.TK2MSFTNGP05.phx.gbl...
> Absolutely, I can see the same PK several times in the resultset in Query
> Analyzer.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eZa8fyiaGHA.4580@.TK2MSFTNGP03.phx.gbl...
contain
>|||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
On Thu, 27 Apr 2006 14:47:40 -0400, "alto" <altodorov@.hotmail.com>
wrote:
>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.I
D
> 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
>|||> Absolutely, I can see the same PK several times in the resultset in Query
> Analyzer.
Yes, is the PK the only column in your resultset? NO! DISTINCT applies to
ALL COLUMNS, not just the PK.
A|||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 ,
[nomcommunfranais] [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','','','','','','',NU
LL,'','','','
','','','','','','','','','
','','','','','','','','','','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 acclimate','
','','','','','','','',' ','','','','','','','','','','','','',''
,'','
','3','','','','1','1','','','2','','1',
'','','','2','','
','','','','','3','','3','','','','','',
'',' ','Nov
INSERT INTO [PlantesBiochimieChimiotype]
VALUES(1,1,'','',1,'','','','',1,'','','
','','','','','','',' ','Le
mthyleugnol a des proprits antimicrobiennes (Grifin et al., 1998).
Proprits nutritives L'lmicine et le mthyleugnol possdent 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)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eJm8%23xiaGHA.1200@.TK2MSFTNGP03.phx.gbl...
> 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.
> "alto" <altodorov@.hotmail.com> wrote in message
> news:%23ew9spiaGHA.4612@.TK2MSFTNGP03.phx.gbl...
> pOpp.ID
>sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment