1 to 1 relationship - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

1 to 1 relationship

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-26 : 05:49:51

Hi i've 2 products table Prod_1 table that holds information about products its unique ID is ProdID i've another table Prod_2 that holds more information about the products groups etc.. the only thing that relate it to Prod_1 is a CCcode

This is a 1 to many relationship is there any way possible for me to get a 1 to 1 relationship i've tried a right outer join on the on the Prod_2 table but still no luck ?

Prod_1 Prod_2
1235 0947
1235V 0947
1235VR 0947

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 05:52:56

You have to provide more information for us to help you better.
Please post the structure of Prod_1 and Prod_2, some sample data and the expected result

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-26 : 06:16:14

CREATE TABLE [dbo].[ProductsLU] (
[ProductGUID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CountryCD3] [varchar] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[EHS] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[SNOFENOF] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[EscSoftwareProviderName] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[EscHardwareProviderName] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[ContractProviderName] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[ServiceProviderName] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[CCOACode] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[EndOfLife] [datetime] NULL ,
[KBParameterName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[IntKBParameter] [varchar] (130) COLLATE Latin1_General_CI_AS NULL ,
[ExtKBParameter] [varchar] (130) COLLATE Latin1_General_CI_AS NULL ,
[ComplaintsProdStr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[UpdtBy] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[UpdtDT] [datetime] NULL ,
[ProductSkillGroup] [int] NULL ,
[ProdID] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[EscSoftwareProdID] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ConsumableSysName] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[KBName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[SNOFSN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[EscSoftwareOrg] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[EscSoftwareOpco] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[mroc] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ProductCodeLU] (
[CCOAGUID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CCOACode] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ProductGroup] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ProductSubOrg] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ProductOrg] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ProductBusinessGrp] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[CRUOrder] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductSkillGroup] [int] NULL
) ON [PRIMARY]
GO

See the 2 tables above i join them on the CCOACode The join from ProductsLU to tabProductCodeLU is many to one, i want to try and get a 1 to 1 relationship.

Current Results
ProductsLU tabProductCodeLU
1235 0947
1235V 0947
1235VR 0947

Expected Results
ProductsLU tabProductCodeLU
1235 0947
1234 0124
1233 0417

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-26 : 07:18:09

there's no such thing as 1 to 1 relationship in sql server.
you could force it with triggers or in a stored procedure by checking the count of child records.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-26 : 07:18:29

can you post what is the records in the CCOACODE ???

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-26 : 09:27:38

I've figured out my first problem, the p.prodid, p.serviceprovidername, p.countrycd3 are all unique in the productslu table. so this gives me a unique p.ccoacode to link to the pc.ccoacode in the productcodelu table. (See code below)

The reason am getting a many to 1 is that i've another table RCA where i link the prodid and to the p.prodid in the productslu table.

select p.prodid, p.serviceprovidername, p.countrycd3, p.ccoacode, pc.ccoacode, pc.productorg, pc.productgroup
from productslu as p join productcodelu as pc on p.ccoacode=pc.ccoacode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources