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 |
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
 Posted - 2006-04-27 : 11:27:47
|
| Hi i've 2 products ProductsLU and ProductsCode tables ProductsLU has 20k records and the ProductsCode has 1300, the ProductsLU which has 20k holds all products details the ProductsCode with 1300 records holds the unique product id called CCOACOde, this unique product id is also in the ProductsLU table.I want to join both tables up but i only want to show the data from the ProductsLU table where the CCOACOde, is in the ProductsCode table Here is my query i expect to get back 1300 but i keep getting back the 20kselect *from tbl_Voy_ProductCodeLU pcinner join tlb_Voy_ProductsLU p on p.CCOACode = pc.CCOACode |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-04-27 : 17:57:46
|
Yes. It will give you 20K records as for each record in ProductsCode there are more than one records in ProductsLU.What do you want to achieve ?
KH |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-04-28 : 01:37:41
|
| select * from productsCodewhere ccoacode in (select ccoacode from productsLu)atleast until you define what you need...--------------------keeping it simple... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-28 : 01:44:16
|
| select pc.*from tbl_Voy_ProductCodeLU pcinner join tlb_Voy_ProductsLU p on p.CCOACode = pc.CCOACodeMadhivananFailing to plan is Planning to fail |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2006-04-28 : 03:48:23
|
quote:
Originally posted by khtan
Yes. It will give you 20K records as for each record in ProductsCode there are more than one records in ProductsLU.What do you want to achieve ?I would like to shows some of the column details in the ProductsLU where i've a matching CCOACOde in the ProductsCode, its a many to 1 relationship but i only want to see a single line from the ProductsLU, so my result should only show the 1300 rows My database is down at the moment so i cannot test the code i've been given i will post a reply when am live again..thanks
KH
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-04-28 : 03:56:31
|
ProductsCode to ProductsLU is one to many relationship.so one line of CCOACode will have many lines of CCOACode in ProductsLU."but i only want to see a single line from the ProductsLU,"So which lines in ProductsLU you want to see ?Can you post the simplified structure of ProductsCode & ProductsLU, few recors from ProductsCode and the corresponding few records of ProdutsLU and also what is the result that you want. It will be clearer this way.
KH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-28 : 03:58:06
|
| "i only want to see a single line from the ProductsLU"IIUC: You only want to see ONE ProductsLU record for each ProductsCode record - but there are potentially MANY ProductsLU record for each ProductsCode record?If that's the case then you will need to define which, or the potentially many, ProductsLU records you would want to see for any given ProductsCode record.To use a JOIN you will need to be sure that there can only be ONE ProductsCode record for a given ProductsLU record, otherwise you will need to use EXISTS instead, or a DISTINCT (EXISTS would probably be better!)Kristen |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2006-04-28 : 06:59:08
|
| Hi ok, i made a mistake as i was not including my 3rd table into the query there is my final query it works just fine now also its based on the 3 key fields in the rca table join up with the joining up tabproductslu which in turn it join on the ccoacode to the tabproductcodelu table.Thanks for you help.select rca.prodid, rca.countrycd3, rca.servicelegacySys, p.ccoacode, pc.productgroup, pc.productorg, pc.productsuborgfrom tabrcatransfer as rca join tabproductslu as p on rca.prodid=p.prodid and rca.countrycd3=p.countrycd3 and rca.servicelegacysys=p.serviceprovidername join tabproductcodelu as pc on p.ccoacode=pc.ccoacode |
|
|
|
|
|
|
|
|
Join