| Author |
 Topic  |
|
|
NewCents
Starting Member
19 Posts |
 Posted - 08/04/2005 : 04:15:55
|
casedetail table:
casedetail_ID, cases_ID, qty, price, product_code
1, 199, 2, 1000, 13701
2, 199, 2, 1000, 13702
3, 199, 2, 1000, 13705
product_items table:
product_items_ID, product_code, products_ID:
15, 13701, 3
16, 13701, 3
17, 13701, 3
18, 13702, 5
19, 13704, 3
20, 13705, 6
I would like to create a temporary table that is the same as the casedetail table but in place of the product_code is the products_ID. So the resulting table would be:
1, 199, 2, 1000, 3
2, 199, 2, 1000, 5
3, 199, 2, 1000, 6
I am very new to SQL, any help appreciated!
Thank you
|
|
|
jen
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 08/04/2005 : 04:20:38
|
just the way you created the permanent table but this time use temp tables or table variables
create table #temp(fields...)
or
declare @temp table(fields...)
--------------------
keeping it simple... |
 |
|
|
NewCents
Starting Member
19 Posts |
Posted - 08/04/2005 : 04:27:37
|
Yes, I know how to create temporary tables, I'm just trying to replace the product_code in casedetail to products_ID in product_items. I was hoping someone could post a query.
Thank you for any help |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 08/04/2005 : 04:58:27
|
I think you can create view also
Create view myView as
Select cd.casedetail_ID, cd.cases_ID, cd.qty, cd.price, pi.products_ID from
casedetail cd inner join product_items pi on cd.product_code=pi.product_code
or use temp table
Create table #mytable(your columns)
Insert into #mytable
Select cd.casedetail_ID, cd.cases_ID, cd.qty, cd.price, pi.products_ID from
casedetail cd inner join product_items pi on cd.product_code=pi.product_code
Madhivanan
Failing to plan is Planning to fail |
Edited by - madhivanan on 08/04/2005 04:59:54 |
|
|
|
NewCents
Starting Member
19 Posts |
Posted - 08/04/2005 : 05:21:15
|
madhivanan,
here is the problem I'm having. If I try this:
Select cd.casedetail_ID, cd.cases_ID, cd.qty, cd.price, pi.products_ID
into #mytable
from casedetail cd inner join product_items pi on cd.product_code=pi.product_code
#mytable has 88 rows of data, even though casedetail only has 3 rows of data. There might be 50 rows in product_items table that have 13701 as a product_code. So when creating this temporary table, all of those rows get thrown in. What I'm looking for is simply to replace the product_code in the case_detail table to the corresponding products_ID in the product_items table.
Thank you for any help! |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 08/04/2005 : 05:32:36
|
Try this
Select cd.casedetail_ID, cd.cases_ID, cd.qty, cd.price, pi.products_ID
into #mytable
from (Select casedetail_ID, cases_ID, qty, price, (select top 1 product_id
from product_items pi where pi.product_code=cd.product_code) as product_id from casedetail cd) T
Madhivanan
Failing to plan is Planning to fail |
|
|
|
NewCents
Starting Member
19 Posts |
Posted - 08/04/2005 : 05:51:34
|
Thank you for the quick response!
For some reason I get:
The column prefix 'cd' does not match with a table name or alias name used in the query.
The column prefix 'pi' does not match with a table name or alias name used in the query. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 08/04/2005 : 06:01:18
|
This should work
Select casedetail_ID, cases_ID, qty, price, products_ID
into #mytable
from (Select casedetail_ID, cases_ID, qty, price, (select top 1 product_id
from product_items pi where pi.product_code=cd.product_code) as product_id from casedetail cd) T
Madhivanan
Failing to plan is Planning to fail |
|
|
|
NewCents
Starting Member
19 Posts |
Posted - 08/04/2005 : 16:33:09
|
madhivanan,
I can't thank you enough!, this works perfectly! |
|
|
| |
Topic |
|
|
|
Easy SQL Query Question!
