Distinct Unions!!

[Bernini]

hi

i have two tables one storing the OBject Details (name= tblObjectDetails) and another storing Object related fields (name= tblObjectUser)!

I have created a query which will return a total of 10 records. First it checks tblObjectUser and returns all objects related to a particular user then it makes a union on the tblObjectDetails. A limit of 10 is set so that only 10 records will sent back!

The problem is that the records on the Object ID are not distinct! So if object id 3 is returned from tblObjectUser an other object_id 3 can be returned from tblObjectDetails!

How can i make this query distinct!?

Thanks
B

 

[r937]

use UNION instead of UNION ALL

rudy

http://r937.com/

SQL Consulting

 

[Bernini]

Yes that's what i'm using Union not Union All!

thanks anyways!
B

 

[r937]

but UNION removes duplicates

perhaps you could restate the question?

rudy

http://r937.com/

SQL Consulting

 

[PHV]

Perhaps a JOIN instead of an UNION ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bernini]

Thanks all!

r937: yes thats what i thought too!

PHV: if i'm not mistaken a Join is a horizontal extender and not a vertical one like the Union

Well, i'try to figure it out! thanks anyways
Nick

 

[SQLSister]

perhaps you could give some data samples of data inthe tables and expected output and post your current query. THat might make it easier to help you.

Questions about posting. See faq183-874

 

[Bernini]

Thanks once again...well i solved my problem...it was quite stupid of me anyways...!

I had another column which was not getting the same value throuhg the union...therefore the output was Distinct in itself...so i removed that field and now its working great!

Thanks and sorry for the hasle!

Nick