MS SQL Server New Users - update tables

Author

update tables

Luiz Horacio

2005-04-15, 3:23 am

Hi,

I'm having a big problem trying to create a query.

I have two tables as described below:

Table 1 Table 2
code code
col1 col4
col2 col5
col3 col6

col1 has the same kind of data as col4, col2 has the same kind of data as
col5 and col3 has the same kind of data as col6.

Some numbers from 'code' are present on both tables, some only on table 1
and some only on table 2

I want to create just 1 table instead of 2, like this:

code
col1
col2
col3
col4
col5
col6

If there is a 'code' that exists only in one of the tables the other table's
columns would have value 0.

How can I do this?

Regards,

--
Luiz Horacio
l horacio@ imadi. com. br (remove spaces)

Walter Clayton

2005-04-15, 3:23 am

Untested, but this should give you the idea.

Solution one:
select coalesce (tab1.code, tab2.code)
, coalesce (tab1.col1,0)
, coalesce (tab1.col2,0)
, coalesce(tab1.col3,0)
, coalesce(tab2.col4,0)
, coalesce(tab2.col5,0)
, coalesce(tab2.col6,0)
from table1 tab1
full outer join
table2 tab2
on (tab1.code = tab2.code)
;

Solution two:
select code_list.code
, coalesce (tab1.col1,0)
, coalesce (tab1.col2,0)
, coalesce(tab1.col3,0)
, coalesce(tab2.col4,0)
, coalesce(tab2.col5,0)
, coalesce(tab2.col6,0)
from
(
select distinct
code code
from table_1
UNION
select distinct
code code
from table_2
) as code_list
left join
table_1 tab1
on (tab1.code = code_list.code)
left join
table_2 tab2
on (tab2.code = code_list.code)
;

--
Walter Clayton
Any technology distinguishable from magic is insufficiently advanced.

"Luiz Horacio" <lhoracio@iname.com> wrote in message
news:%23wmmvpWQFHA.1176@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I'm having a big problem trying to create a query.
>
> I have two tables as described below:
>
> Table 1 Table 2
> code code
> col1 col4
> col2 col5
> col3 col6
>
> col1 has the same kind of data as col4, col2 has the same kind of data as
> col5 and col3 has the same kind of data as col6.
>
> Some numbers from 'code' are present on both tables, some only on table 1
> and some only on table 2
>
> I want to create just 1 table instead of 2, like this:
>
> code
> col1
> col2
> col3
> col4
> col5
> col6
>
> If there is a 'code' that exists only in one of the tables the other
> table's
> columns would have value 0.
>
> How can I do this?
>
>
> Regards,
>
>
>
> --
> Luiz Horacio
> l horacio@ imadi. com. br (remove spaces)
>
>

Sponsored Links