Select

[Guest]

I tried to create a newtable from two tables by using "select statement".
However, a new table eliminated the duplicated value.
For example :
Table 1 : amount : 100
Table 2 : amount : 100

If I use the structure : select amount from table 1
union select amount from table 2
the result return only 1 record : 100 ( instead of 2)

Any one help me
Thanks

 

[Neil Sunderland]

I tried to create a newtable from two tables by using "select statement".
However, a new table eliminated the duplicated value.
For example :
Table 1 : amount : 100
Table 2 : amount : 100

If I use the structure : select amount from table 1
union select amount from table 2
the result return only 1 record : 100 ( instead of 2)

You need to use UNION ALL as UNION on it's own ignores duplicate
records:

SELECT amount
FROM [table 1]
UNION ALL
SELECT amount
FROM [table 2]

 

[Guest]

Thank you very much
I think I get stuck with this point. Your prompt answear save me a days.

I tried to create a newtable from two tables by using "select statement".
However, a new table eliminated the duplicated value.
For example :
Table 1 : amount : 100
Table 2 : amount : 100

If I use the structure : select amount from table 1
union select amount from table 2
the result return only 1 record : 100 ( instead of 2)

You need to use UNION ALL as UNION on it's own ignores duplicate
records:

SELECT amount
FROM [table 1]
UNION ALL
SELECT amount
FROM [table 2]

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address