Putting two tables together - 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

General SQL Server Forums

New to SQL Server Programming

Putting two tables together

Author

Topic

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-09-09 : 11:58:23

Hello,

I have a problem. I have two tables looking like this:

ID__Price1____Price2____Price3____Prop
02__120_______140_______100_______C
05__120_______140_______90________D
09__150_______150_______90________C
etc

ID__Property1__Property2__Property3__Property4
01__A__________120________10000______10000000
02__A__________120________10000______5000000
03__B__________120________12000______9000000
etc

First I need 5 more columns in table 1, named (AorB) / (Property2) / (Property 3) / (Property4) / (CorD) / (Change1)

Now I need check the corresponding row in table 2 (where ID=ID) and to fill Column (AorB) with the value one, if Property1 = A and value zero, if Property1 = B.

Afterwards I need to fill the corresponding properties of table2 in the empty columns of table 1.

Then I also need to assign a binary value to (CorD) -> 1 for C, 0 for D from table 1.

Afterwards I need to find out wheather or not, there has been a change in Price1 and put a binary value in column1 (1=yes,2=no).

Can somebody help me on this, would really appreciate it!

tnx in advance

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-09-09 : 12:14:16

what will be the value against AorB if property is anything other than A or B.

what will be the value against CorD if property is anything other than C or D.

how do you determine any change on price ?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-09-09 : 12:14:16

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 12:42:21

What about start normalizing your tables?

E 12°55'05.63"
N 56°04'39.26"

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-09-24 : 04:40:32

hi, had to go out of town..

AorB will only have the Value A or B, no other value, the same with CorD (only C or D).

any ideas??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 04:51:22

use ALTER TABLE table1 ADD ... to add new columns

UPDATE t1
SET t1.[AorB]=CASE WHEN t2.Property1 ='A' THEN 1 ELSE 0 END,
t1.Property2=t2.Property2,
t1.Property3=t2.Property3,
t1.Property4=t2.Property4,
t1.[CorD]=CASE WHEN t1.Prop='C' THEN 1 ELSE 0 END,
t1.Change=CASE WHEN t1.Price1<>t2.Price2 OR t1.Price1<>t2.Price3 THEN 1 ELSE 2 END 
FROM table1 t1
INNER JOIN table2 t2
ON t2.ID=t1.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources