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.
| Author |
Topic |
|
ASP_DRUG_DEALER
Yak Posting Veteran
61 Posts |
 Posted - 2005-06-10 : 09:58:41
|
| Hello all-It's been a while since I've had to put on my db hat, but here goes. I am wanting to join a table back to itself, like a self join, only multiple times. This is what I came up with....CUSTOMERC_IDC_NAMECUST_VENDC_IDV_IDHere is the logic. A customer can really be a vendor to another customer and vise a verse. Another way to say this...A customer can be a vendor to all of the other customers or none.Is there a better way to do this? What would be the best way to enforce this relationsip (trigger?).Thanks,Doug |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-06-10 : 10:10:24
|
what do you mean by 'enforce this relationsip'??Corey Secret Service Agent: Mr. President, you're urinating on me.President Lyndon Johnson: I know I am. It's my prerogative. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-10 : 10:46:11
|
quote:
Originally posted by Seventhnight
what do you mean by 'enforce this relationsip'??
Maybe... |
|
|
|
mpetanovitch
Yak Posting Veteran
52 Posts |
Posted - 2005-06-10 : 13:21:38
|
| Not sure what you mean but from the table design:CUSTOMERC_IDC_NAMECUST_VENDC_IDV_IDYou could foreign key Customer.C_ID to Cust_Vend.C_IDand foreign key Customer.C_ID to Cust_Vend.V_IDor you could do:CUSTOMERC_ID V_ID C_NAME and foreign key it to itself. CUSTOMER.C_ID to CUSTOMER.V_IDMike Petanovitch |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-06-10 : 19:53:40
|
| Have you considered having one table for Company that holds both Vendor and Customer data and another table (CustomerVendor) that holds the relationship between two Companies? The foreign keys in the CutomerVendor table would reference the Company table.create table dbo.Company ( CompanyID blah, blah, blah)create table dbo.CustomerVendor ( CustomerID constraint foreign key references Company(CompanyID), VendorID constraint foreign key references Company(CompanyID), blah, blah, blah)Tables are used to represent either an entity or a relationship...HTH=================================================================All restraints upon man's natural liberty, not necessary for the simple maintenance of justice, are of the nature of slavery, and differ from each other only in degree. -Lysander Spooner, lawyer (1808-1887) |
|
|
|
|
|
|
|
|
Reciprocating Join?