#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    6
    Rep Power
    0

    Simple-ish sql statement help


    Table x rows
    -------------
    id
    --
    1
    2
    3
    4
    5

    Table y rows
    -------------
    key | a_id | b_id
    ------------------------------------------
    20 | 1 | 2
    22 | 1 | 3
    32 | 3 | 4
    42 | 5 | 1
    52 | 5 | 2
    64 | 2 | 3


    I want to have a sql statement that returns to me all the keys from table y where if i am given
    2 id's from table x i can find all the cases where they are both in table y in a_id AND b_id in the same row.

    For instance given id's of 2 and 1 I would return key 20. I will always be given a pair of id's(the order I get the id's can't matter and they could be in a_id or b_id) and I simply have to find where those keys are in the same row in either column a_id or b_id. Given id's of 1 and 4 would return 0 rows as those don't appear in the same row anywhere.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    15
    Rep Power
    0
    Hi,

    You can try this example.

    create temp table aav (key int, a_id int, b_id int);
    insert into aav
    select 20,1,2
    union select 22,1,3
    union select 32,3,4
    union select 42,5,1;


    CREATE OR REPLACE FUNCTION aav_getkey(id1 int,id2 int) RETURNS int AS $$
    DECLARE

    BEGIN

    return key FROM aav WHERE a_id=id1 and b_id=id2
    UNION
    SELECT key FROM aav WHERE a_id=id2 and b_id=id1
    UNION
    SELECT 0 WHERE
    (SELECT COUNT(*) FROM(
    SELECT key FROM aav WHERE a_id=id1 and b_id=id2
    UNION
    SELECT key FROM aav WHERE a_id=id2 and b_id=id1
    )a)=0;

    END;
    $$LANGUAGE PLPGSQL;


    select * from aav;
    key | a_id | b_id
    -----+------+------
    20 | 1 | 2
    32 | 3 | 4
    42 | 5 | 1
    22 | 1 | 3
    (4 rows)

    select * from aav_getkey(1,5);
    aav_getkey
    ------------
    42

    select * from aav_getkey(1,9);
    aav_getkey
    ------------
    0

    Hope this help
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by argo4242
    Table x rows
    Code:
    -------------
    id
    --
    1
    2
    3
    4
    5
    
    Table y rows
    -------------
    key		|	a_id	        |	b_id	
    ------------------------------------------
    20		|	1		|	2
    22		|	1		|	3
    32		|	3		|	4
    42		|	5		|	1
    52		|	5		|	2
    64		|	2		|	3
    I want to have a sql statement that returns to me all the keys from table y where if i am given
    2 id's from table x i can find all the cases where they are both in table y in a_id AND b_id in the same row.

    For instance given id's of 2 and 1 I would return key 20. I will always be given a pair of id's(the order I get the id's can't matter and they could be in a_id or b_id) and I simply have to find where those keys are in the same row in either column a_id or b_id. Given id's of 1 and 4 would return 0 rows as those don't appear in the same row anywhere.
    Code:
    select *
    from y
    where (least(a_id,b_id), greatest(a_id, b_id)) in (least(1,2), greatest(1,2));
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo