'Addding' Column In View

Hi! Here is what I need to do:
I have 2 tables; the first called "IDs" and the second called "Test".
This 'Test' doesn't have an identity field, and I need to have one to
do some filtering, but for burocracy I can't simply add another column
to the table. So, as as it is, I had to create another table
called 'IDs' with only 1 field called 'ID' filled with autogenerated
numbers from 1 to 99999999^9999 :D. Then, the thing that I can't do is
to "append" this table to the "Test".
When I do, for example:
<select IDs.id, Test.Name from IDs, test>, what I get is something
like:
id name
--
----
1 ABC
2 ABC
3 ABC
4 ABC
1 DEF
2 DEF
3 DEF
4 DEF
1 GHI
2 GHI
3 GHI
4 GHI
1 JKM
2 JKM
3 JKM
4 JKM
Notice the repetition of the values...
I would need it to be like:
id name
--
----
1 ABC
2 ABC
3 ABC
4 ABC
So... is there something I can do to get this? And also, what would
happen if one of the tables had more registries than the other? Would
it work?
Very very thanks in advance;
Bye.See if this helps:
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Dan_Aykroyd" wrote:

> Hi! Here is what I need to do:
> I have 2 tables; the first called "IDs" and the second called "Test".
> This 'Test' doesn't have an identity field, and I need to have one to
> do some filtering, but for burocracy I can't simply add another column
> to the table. So, as as it is, I had to create another table
> called 'IDs' with only 1 field called 'ID' filled with autogenerated
> numbers from 1 to 99999999^9999 :D. Then, the thing that I can't do is
> to "append" this table to the "Test".
> When I do, for example:
> <select IDs.id, Test.Name from IDs, test>, what I get is something
> like:
> id name
> --
> ----
> 1 ABC
> 2 ABC
> 3 ABC
> 4 ABC
> 1 DEF
> 2 DEF
> 3 DEF
> 4 DEF
> 1 GHI
> 2 GHI
> 3 GHI
> 4 GHI
> 1 JKM
> 2 JKM
> 3 JKM
> 4 JKM
> Notice the repetition of the values...
> I would need it to be like:
> id name
> --
> ----
> 1 ABC
> 2 ABC
> 3 ABC
> 4 ABC
> So... is there something I can do to get this? And also, what would
> happen if one of the tables had more registries than the other? Would
> it work?
> Very very thanks in advance;
> Bye.
>|||SQL2005 will have 'rownumber', which will let you do it really easily.
But for the time being, you will have to do it the nasty way.|||Wow... it wasn't as easy as I thought! :)
I thought that what I needed to do would be easy, because it
requires... "no processing"; I mean, no combination of rows, no
relations, no linking, etc etc; it's just adding a column to a view in
the way it's stored in the table...
Thanks very much Alejandro; I'm gonna read it now!
And if there's an easier way... I'd be glad to hear it ;)sql