Hi guys:

I Would like to mix two tables using SQL without use while or for instruction, I have these tables:

Table 1
IdWeek  Employee  Concept  Amount
1                    15             1    150.00
1                    15            25     25.00

Table 2
IdWeek  Employee  Concept  Amount
1                    15             2      35.00
1                    15             4      13.50
1                    15            30     28.00
1                    15            99     14.00

I need to mix the tables to get the follow table:
Table Result
IdWeek  Employee  Concept(table1)  Amount(Table1)  Concept(Table2)  Amount(Table2)
1                     15                     1                 150.00                      2                    35.00
1                     15                    25                  25.00                      4                    13.50
1                     15                                                                      30                    28.00
1                     15                                                                      99                    14.00

Some times the number of records in both tables are the same per employee, but not always, some times the number of records in table 1 is minor
to the table2 or viceversa.

My best regards

<< I Would like to mix two tables using SQL without use while or for
instruction, I have these tables:

Table 1
IdWeek  Employee  Concept  Amount
1                    15             1    150.00
1                    15            25     25.00

Table 2
IdWeek  Employee  Concept  Amount
1                    15             2      35.00
1                    15             4      13.50
1                    15            30     28.00
1                    15            99     14.00

I need to mix the tables to get the follow table:
Table Result
IdWeek  Employee  Concept(table1)  Amount(Table1)  Concept(Table2)
Amount(Table2)
1                     15                     1                 150.00
2                    35.00
1                     15                    25                  25.00
4                    13.50
1                     15
30                    28.00
1                     15
99                    14.00

Some times the number of records in both tables are the same per employee,
but not always, some times the number of records in table 1 is minor to the
table2 or viceversa. >>

You really can't do so in straight SQL without getting repeating values in
one of the tables (join), or getting blank values where you don't want them
(union).

--
Tim Young
Elevate Software
www.elevatesoft.com

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:


<< I Would like to mix two tables using SQL without use while or for
instruction, I have these tables:

Table 1
IdWeek  Employee  Concept  Amount
1                    15             1    150.00
1                    15            25     25.00

Table 2
IdWeek  Employee  Concept  Amount
1                    15             2      35.00
1                    15             4      13.50
1                    15            30     28.00
1                    15            99     14.00

I need to mix the tables to get the follow table:
Table Result
IdWeek  Employee  Concept(table1)  Amount(Table1)  Concept(Table2)
Amount(Table2)
1                     15                     1                 150.00
2                    35.00
1                     15                    25                  25.00
4                    13.50
1                     15
30                    28.00
1                     15
99                    14.00

Some times the number of records in both tables are the same per employee,
but not always, some times the number of records in table 1 is minor to the
table2 or viceversa. >>

You really can't do so in straight SQL without getting repeating values in
one of the tables (join), or getting blank values where you don't want them
(union).

--
Tim Young
Elevate Software
www.elevatesoft.com

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Jim, do you know any way to assign a number by SQL to the record, the result should be something like this:

Table 1
IdWeek  Employee  Concept  Amount  IdRec
1                    15             1    150.00        1
1                    15            25     25.00        2
2                    15              3     14.00        1
2                    15             12   100.00       2
1                    17              1    100.00       1
1                    17              5      25.00       2

Table 2
IdWeek  Employee  Concept  Amount  IdRec
1                    15             2      35.00        1
1                    15             4      13.50        2
1                    15            30     28.00        3
1                    15            99     14.00        4
2                    15            13       7.00        1
1                    17             8      20.00        1

<< Thanks Jim, do you know any way to assign a number by SQL to the record,
the result should be something like this: >>

You might be able to get something working using a RUNSUM(1) expression and
grouping on every selected column.

--
Tim Young
Elevate Software
www.elevatesoft.com