SQL query problem

rubyonrails-talk
#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.

#2

This is not an issue for the -core list - please restrict your user questions to the -talk list.

#3

plain SQL solution, although not really scalable, I guess... :wink: :

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)

#4

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... :slight_smile:

--Matt Jones

#5

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)

#6

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)

#7

Thanks everyone for your help! Happy New Year.