Easy SQL Query Question! - 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

SQL Server Development (2000)

Easy SQL Query Question!

Author

Topic

NewCents
Starting Member

19 Posts

Posted - 2005-08-04 : 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
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-08-04 : 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 - 2005-08-04 : 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

22864 Posts

Posted - 2005-08-04 : 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

NewCents
Starting Member

19 Posts

Posted - 2005-08-04 : 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

22864 Posts

Posted - 2005-08-04 : 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 - 2005-08-04 : 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

22864 Posts

Posted - 2005-08-04 : 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 - 2005-08-04 : 16:33:09

madhivanan,

I can't thank you enough!, this works perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources