SQL query problem

Rails
#1

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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

#2

Hi,

Learn by Doing wrote:

Please help me with finding a SQL select statement for the following
problems.

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

TableName.find_by_sql(“select Linking_site_id, Linked_site_id from table
t where Linking_site_id=3 or (Linked_site_id=3 and Linking_site_id in
(select distinct(Linking_site_id) from table t2 where Linking_site_id=3
)”)

Hope that helps :slight_smile:

Regards,

Salil G.

#3

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 B. [email protected]

linked_site_id that site 3 links to. For example, if the Link table

–

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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

#4

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 B. [email protected]

linked_site_id that site 3 links to. For example, if the Link table

–

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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

#5

Thanks everyone for your help! Happy New Year.

On Dec 21, 6:24 am, Roberta [email protected] wrote:

Please help me with finding a SQL select statement for the following
contains the following rows
3 2
You received this message because you are subscribed to the Google G.
“Ruby on Rails: Talk” group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected][email protected]
.
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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.