Please help me with finding a SQL select statement for the following problems. I have two classes: Site and Link Table Link has two columns: linking_site_id, and linked_site_id I would like to find all the links in table Link where linking_site_id = 3 or linked_site_id = 3 but only include a link where linked_site_id = 3 if the linking_site_id in that link is not one of the linked_site_id that site 3 links to. For example, if the Link table contains the following rows Linking_site Linked_site 2 1 3 2 3 4 5 3 2 3 What SQL select statement would return only the following: 3 2 3 4 5 3 If SQL query alone cannot do this, what is the most efficient way to do it via RoR? Thanks. Thanks. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
This is not an issue for the -core list - please restrict your user questions to the -talk list. On Dec 17, 2009, at 9:10 PM, Ease Bus wrote:> Please help me with finding a SQL select statement for the following > problems. > > I have two classes: Site and Link > > Table Link has two columns: linking_site_id, and linked_site_id > > I would like to find all the links in table Link where linking_site_id > = 3 or linked_site_id = 3 but only include a link where linked_site_id > = 3 if the linking_site_id in that link is not one of the > linked_site_id that site 3 links to. For example, if the Link table > contains the following rows > > Linking_site Linked_site > 2 1 > 3 2 > 3 4 > 5 3 > 2 3 > > What SQL select statement would return only the following: > > 3 2 > 3 4 > 5 3 > > If SQL query alone cannot do this, what is the most efficient way to > do it via RoR? Thanks. > > Thanks. > > -- > > You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. > To post to this group, send email to rubyonrails-core@googlegroups.com. > To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. > For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
plain SQL solution, although not really scalable, I guess... ;-) :
SELECT * FROM `links` as l
WHERE
l.linking_site = 3 or
(l.linked_site = 3
AND (select count(*) from links where linking_site=3 and
linked_site=l.linking_site)=0)
On 18 Dez., 06:10, Ease Bus
<ease...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> Please help me with finding a SQL select statement for the following
> problems.
>
> I have two classes: Site and Link
>
> Table Link has two columns: linking_site_id, and linked_site_id
>
> I would like to find all the links in table Link where linking_site_id
> = 3 or linked_site_id = 3 but only include a link where linked_site_id
> = 3 if the linking_site_id in that link is not one of the
> linked_site_id that site 3 links to. For example, if the Link table
> contains the following rows
>
> Linking_site Linked_site
> 2 1
> 3 2
> 3 4
> 5 3
> 2 3
>
> What SQL select statement would return only the following:
>
> 3 2
> 3 4
> 5 3
>
> If SQL query alone cannot do this, what is the most efficient way to
> do it via RoR? Thanks.
>
> Thanks.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
On Dec 18, 12:10 am, Ease Bus <ease...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Please help me with finding a SQL select statement for the following > problems. > > I have two classes: Site and Link > > Table Link has two columns: linking_site_id, and linked_site_id > > I would like to find all the links in table Link where linking_site_id > = 3 or linked_site_id = 3 but only include a link where linked_site_id > = 3 if the linking_site_id in that link is not one of the > linked_site_id that site 3 links to. For example, if the Link table > contains the following rows > > Linking_site Linked_site > 2 1 > 3 2 > 3 4 > 5 3 > 2 3 > > What SQL select statement would return only the following: > > 3 2 > 3 4 > 5 3 > > If SQL query alone cannot do this, what is the most efficient way to > do it via RoR? Thanks.Probably not the most efficient, but this will do what you''re looking for: linking_sites = Link.find_by_linked_site_id(3).map { |l| l.linking_site_id } result = Link.find(:all, :conditions => [''linking_site_id = :base OR (linked_site_id = :base AND linking_site_id NOT IN (:sites)'', { :base => 3, :sites => linking_sites }) It has the advantage of being fairly close to the original text description as well, so when you see it again in a year you''ll know what it does... :) --Matt Jones -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
select *
from link_table le
where le.link_site = ''3'' or le.linked_site
=''3''
and not exists (select 0
from link_table li
where li.link_site = le.linked_site
and le.link_site = li.linked_site)
2009/12/18 Ease Bus <easebus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> Please help me with finding a SQL select statement for the following
> problems.
>
> I have two classes: Site and Link
>
> Table Link has two columns: linking_site_id, and linked_site_id
>
> I would like to find all the links in table Link where linking_site_id
> = 3 or linked_site_id = 3 but only include a link where linked_site_id
> = 3 if the linking_site_id in that link is not one of the
> linked_site_id that site 3 links to. For example, if the Link table
> contains the following rows
>
> Linking_site Linked_site
> 2 1
> 3 2
> 3 4
> 5 3
> 2 3
>
> What SQL select statement would return only the following:
>
> 3 2
> 3 4
> 5 3
>
> If SQL query alone cannot do this, what is the most efficient way to
> do it via RoR? Thanks.
>
> Thanks.
>
> --
>
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> .
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
select *
from link_table le
where le.link_site = ''3'' or le.linked_site
=''3''
and not exists (select 0
from link_table li
where li.link_site = le.linked_site
and le.link_site = li.linked_site)
2009/12/18 Ease Bus <easebus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> Please help me with finding a SQL select statement for the following
> problems.
>
> I have two classes: Site and Link
>
> Table Link has two columns: linking_site_id, and linked_site_id
>
> I would like to find all the links in table Link where linking_site_id
> = 3 or linked_site_id = 3 but only include a link where linked_site_id
> = 3 if the linking_site_id in that link is not one of the
> linked_site_id that site 3 links to. For example, if the Link table
> contains the following rows
>
> Linking_site Linked_site
> 2 1
> 3 2
> 3 4
> 5 3
> 2 3
>
> What SQL select statement would return only the following:
>
> 3 2
> 3 4
> 5 3
>
> If SQL query alone cannot do this, what is the most efficient way to
> do it via RoR? Thanks.
>
> Thanks.
>
> --
>
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> .
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
Thanks everyone for your help! Happy New Year. On Dec 21, 6:24 am, Roberta <role...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> select * > from link_table le > where le.link_site = ''3'' or le.linked_site =''3'' > and not exists (select 0 > from link_table li > where li.link_site = le.linked_site > and le.link_site = li.linked_site) > > 2009/12/18 Ease Bus <ease...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > Please help me with finding a SQL select statement for the following > > problems. > > > I have two classes: Site and Link > > > Table Link has two columns: linking_site_id, and linked_site_id > > > I would like to find all the links in table Link where linking_site_id > > = 3 or linked_site_id = 3 but only include a link where linked_site_id > > = 3 if the linking_site_id in that link is not one of the > > linked_site_id that site 3 links to. For example, if the Link table > > contains the following rows > > > Linking_site Linked_site > > 2 1 > > 3 2 > > 3 4 > > 5 3 > > 2 3 > > > What SQL select statement would return only the following: > > > 3 2 > > 3 4 > > 5 3 > > > If SQL query alone cannot do this, what is the most efficient way to > > do it via RoR? Thanks. > > > Thanks. > > > -- > > > You received this message because you are subscribed to the Google Groups > > "Ruby on Rails: Talk" group. > > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > > To unsubscribe from this group, send email to > > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > > . > > For more options, visit this group at > >http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.