SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Easy SQL Query Question!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NewCents
Starting Member

19 Posts

Posted - 08/04/2005 :  04:15:55  Show Profile  Reply with Quote
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  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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...
Go to Top of Page

NewCents
Starting Member

19 Posts

Posted - 08/04/2005 :  04:27:37  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 08/04/2005 :  04:58:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

NewCents
Starting Member

19 Posts

Posted - 08/04/2005 :  05:21:15  Show Profile  Reply with Quote
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!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 08/04/2005 :  05:32:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

NewCents
Starting Member

19 Posts

Posted - 08/04/2005 :  05:51:34  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 08/04/2005 :  06:01:18  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

NewCents
Starting Member

19 Posts

Posted - 08/04/2005 :  16:33:09  Show Profile  Reply with Quote
madhivanan,

I can't thank you enough!, this works perfectly!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.74 seconds. Powered By: Snitz Forums 2000