# Select distinct not working **URL:** \ **Category:** Rails\ **Created:** [May 2, 2008, 5:19pm UTC](https://www.ruby-forum.com/t/select-distinct-not-working/136490 "2008-05-02T17:19:40Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Tarscher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@Tarscher](https://www.ruby-forum.com/u/Tarscher)\ **Post date:** [May 2, 2008, 5:19pm UTC](https://www.ruby-forum.com/t/select-distinct-not-working/136490/1 "2008-05-02T17:19:40Z")
HI all, @users = Node.find(:all, :select =\> “DISTINCT users.id”, :conditions =\> [" lft \>= ? AND rgt \<= ? AND nodes.statistics = 1", @event.node.lft, @event.node.rgt], :include =\> { :users =\> :registrations }, :order =\> “users.first\_name ASC” ) I want to select users from multiple nodes (groups that contain users). Since 1 user can be a member of multiple nodes I only want to have the user returned once Eg: Node 1 - user 1 - user 2 Node 2 - user 2 - user 3 This should return Node 1 - user 1 - user 2 Node 2 - user 3 The sql results in: SELECT `nodes`.`id` AS t0\_r0, `nodes`.`parent_id` AS t0\_r1, `nodes`.`lft` AS t0\_r2, `nodes`.`rgt` AS t0\_r3, `nodes`.`name` AS t0\_r4, `nodes`.`node_type_id` AS t0\_r5, `nodes`.`registration` AS t0\_r6, `nodes`.`statistics` AS t0\_r7, `users`.`id` AS t1\_r0, `users`.`activation_key` AS t1\_r1, `users`.`username` AS t1\_r2, `users`.`password` AS t1\_r3, `users`.`first_name` AS t1\_r4, `users`.`last_name` AS t1\_r5, `users`.`email` AS t1\_r6, `registrations`.`id` AS t2\_r0, `registrations`.`event_id` AS t2\_r1, `registrations`.`user_id` AS t2\_r2, `registrations`.`present` AS t2\_r3, `registrations`.`created_at` AS t2\_r4, `registrations`.`updated_at` AS t2\_r5 FROM `nodes` LEFT OUTER JOIN `nodes_users` ON `nodes_users`.node\_id = `nodes`.id LEFT OUTER JOIN `users` ON `users`.id = `nodes_users`.user\_id LEFT OUTER JOIN `registrations` ON registrations.user\_id = users.id WHERE ( lft \>= 1 AND rgt \<= 6 AND nodes.statistics = 1) ORDER BY users.first\_name ASC I don’t see the Distinct in the SQL an this results in duplicate users. Someone knows why? Thanks Stijn ---
**Author:** ![Tarscher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@Tarscher](https://www.ruby-forum.com/u/Tarscher)\ **Post date:** [May 2, 2008, 5:32pm UTC](https://www.ruby-forum.com/t/select-distinct-not-working/136490/2 "2008-05-02T17:32:38Z")
On 2 May 2008, at 16:18, Tarscher wrote: > users. :include overwrites your :select. Do you actually want to use :joins ? Fred ---
**Author:** ![Tarscher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@Tarscher](https://www.ruby-forum.com/u/Tarscher)\ **Post date:** [May 2, 2008, 5:43pm UTC](https://www.ruby-forum.com/t/select-distinct-not-working/136490/3 "2008-05-02T17:43:53Z")
I do it so I don’t have to use a find in a iteration (performance). I guess I have to filter the duplicates out afterwards. Thanks Stijn On May 2, 5:32 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![Tarscher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@Tarscher](https://www.ruby-forum.com/u/Tarscher)\ **Post date:** [May 2, 2008, 5:45pm UTC](https://www.ruby-forum.com/t/select-distinct-not-working/136490/4 "2008-05-02T17:45:40Z")
On 2 May 2008, at 16:43, Tarscher wrote: > I do it so I don’t have to use a find in a iteration (performance). > > I guess I have to filter the duplicates out afterwards. You may find it easier to first select those users you’re interested in and then do User.find user\_ids, :include =\> :registrations Fred ---
**Author:** ![Tarscher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@Tarscher](https://www.ruby-forum.com/u/Tarscher)\ **Post date:** [May 2, 2008, 5:55pm UTC](https://www.ruby-forum.com/t/select-distinct-not-working/136490/5 "2008-05-02T17:55:04Z")
Thank you for the help. Can you please elaborate on ‘first select those users you’re interested in’ . Regards, Stijn On May 2, 5:45 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![Tarscher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@Tarscher](https://www.ruby-forum.com/u/Tarscher)\ **Post date:** [May 2, 2008, 5:58pm UTC](https://www.ruby-forum.com/t/select-distinct-not-working/136490/6 "2008-05-02T17:58:36Z")
On 2 May 2008, at 16:54, Tarscher wrote: > Thank you for the help. > > Can you please elaborate on ‘first select those users you’re > interested in’ . users = Users.find :all, … (without using include, possibly using :joins User.find users.collect(&:id), :include =\> :registrations Fred ---
**Author:** ![Tarscher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@Tarscher](https://www.ruby-forum.com/u/Tarscher)\ **Post date:** [May 2, 2008, 7:10pm UTC](https://www.ruby-forum.com/t/select-distinct-not-working/136490/7 "2008-05-02T19:10:39Z")
Many thanks. The user id select works. I want to get the users with a specific id and the joined registration table for a specific event. I also want to get the user when he did not register for an event When I do : players = User.find(users.collect(&:id), :include =\> :registrations, :conditions =\> [“registrations.event\_id = ?”, event.id ] ) I only get the users thar registered to the event I try: players = User.find(users.collect(&:id), :include =\> :registrations, :conditions =\> [“registrations.event\_id = ? OR registrations.id = NULL”, event.id ] ) but I get no result back SQL: SELECT `users`.`id` AS t0\_r0, `users`.`activation_key` AS t0\_r1, `users`.`username` AS t0\_r2, `users`.`password` AS t0\_r3, `users`.`first_name` AS t0\_r4, `users`.`last_name` AS t0\_r5, `users`.`email` AS t0\_r6, `registrations`.`id` AS t1\_r0, `registrations`.`event_id` AS t1\_r1, `registrations`.`user_id` AS t1\_r2, `registrations`.`present` AS t1\_r3, `registrations`.`created_at` AS t1\_r4, `registrations`.`updated_at` AS t1\_r5 FROM `users` LEFT OUTER JOIN `registrations` ON registrations.user\_id = users.id WHERE (`users`.`id` IN (1,3,2) AND (registrations.event\_id = 3 OR registrations.id = null)) Someone has a solution? thanks Stin On May 2, 5:57 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![Tarscher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@Tarscher](https://www.ruby-forum.com/u/Tarscher)\ **Post date:** [May 3, 2008, 4:14pm UTC](https://www.ruby-forum.com/t/select-distinct-not-working/136490/8 "2008-05-03T16:14:30Z")
On 2 May 2008, at 18:10, Tarscher wrote: > event.id ] ) > I only get the users thar registered to the event > > I try: > players = User.find(users.collect(&:id), :include > =\> :registrations, :conditions =\> [“registrations.event\_id = ? OR > registrations.id = NULL”, event.id ] ) NULL is special in sql (so NULL = NULL is false, NULL != NULL is false etc…). You need to use IS NULL. Fred ---
**Author:** ![Tarscher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@Tarscher](https://www.ruby-forum.com/u/Tarscher)\ **Post date:** [May 3, 2008, 7:12am UTC](https://www.ruby-forum.com/t/select-distinct-not-working/136490/9 "2008-05-03T07:12:11Z")
use find\_by\_sql