But I prefer having identity column in the table than generating and using that in query [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

I cannot use set rowcount 2. Because I don#%92t know how many records would be in both the tables. Moreover I am looking for a query, which can run without setting rowcount. My ultimate goal is to create a view.

Dear FrankKalis

Thanking you for replying me.

Actually I have 11 tables with all relationship established. I want to create a view involving all tease tables. I have created some sub views from those sub view I would be creating main view. Among those views, two views are like the example I have given. If situation is like the example I have given, how to solve? That#%92s my question.

Looks like you need an "unique key" so you can know which is the "first" row to select (as Khtan showed using count). I wonder why the "xy qr" or "as op" missing in the cross join result set. Anyway, here is what I found if I use Madhivanan query.

you only need the first instance of the record to join, is that right? I guess the assumption that the "first" instance of the record is assumed with a lower value of the identity. However, if this is not correct, you should have a PRIORITY column where the PRIORITY dictates the order of precedence and not the IDENTITY column.