question regarding select (multiple rows select into one result row) 
Author Message
 question regarding select (multiple rows select into one result row)

Hello,
I have the following table:
CREATE TABLE [dbo].[contacts] (
 [id_req] [numeric](18, 0) NULL ,
 [id_rec] [numeric](18, 0) NULL ,
 [state] [varchar] (5)
)
Which has the following rows
11        127        c
127        11        r
11        130        r

What I need to do is create a SQL statement which answers the following:
What is the relationchip between user1 towards  user2 and user2 towards
user1
For example, the relationship from user 11 towards user 127 is "c" and from
user 127 to user 11 is "r"

This is a piece of cake when the relationship is by-directional  (2 rows per
pair of users)
it would simply be:
  select  c1.state as rel1,  c2.state as rel2  FROM contacts as c1, contacts
as c2
 WHERE (c1.id_req = 11 and c1.id_rec=127) and (c2.id_req = 127 and
c2.id_rec=11)
This returns "rel1=c, rel2=r"
But what about the cases when there is only one row?
How can I get a result like "rel1=r, rel2=NULL" ?????

Thanks for your help!



Mon, 25 Aug 2003 06:00:45 GMT
 question regarding select (multiple rows select into one result row)

When you need a row returned but there might not be any such row in
the source data life does get tricky.  The simplest approach is to
include another table in the query, a table with one row for every id
value used in either the id_req or id_rec columns.  Such a table
would, of course, be used for a foreign key constraint on each of
those columns.

With such a table to draw upon:

SELECT U1.*
  FROM Users as U1
  LEFT JOIN Contacts as C1
    ON U1.id = C1.id_req
 UNION
SELECT U2.*
  FROM Users as U2
  LEFT JOIN Contacts as C2
    ON U2.id = C2.id_rec

An alternate approach is to return a single row with all the
information.  If you can tolerate a result set of zero rows when the
relationship is not defined in either direction you could use
something like:

SELECT COALESCE(C1.id_req, C2.id_rec) as User1,
       COALESCE(C1.id_rec, C2.id_req) as User2,
       C1.state as U1toU2,
       C2.state as U2toU1      
  FROM Contacts as C1
  FULL OUTER JOIN Contacts as C2
    ON C1.id_req = C2.id_rec
   AND C1.id_rec = C2.id_req
   AND C1.id_req = 11
   AND C1.id_rec = 127
   AND C2.id_req = 127
   AND C2.id_rec = 11

Roy

Quote:

>Hello,
>I have the following table:
>CREATE TABLE [dbo].[contacts] (
> [id_req] [numeric](18, 0) NULL ,
> [id_rec] [numeric](18, 0) NULL ,
> [state] [varchar] (5)
>)
>Which has the following rows
>11        127        c
>127        11        r
>11        130        r

>What I need to do is create a SQL statement which answers the following:
>What is the relationchip between user1 towards  user2 and user2 towards
>user1
>For example, the relationship from user 11 towards user 127 is "c" and from
>user 127 to user 11 is "r"

>This is a piece of cake when the relationship is by-directional  (2 rows per
>pair of users)
>it would simply be:
>  select  c1.state as rel1,  c2.state as rel2  FROM contacts as c1, contacts
>as c2
> WHERE (c1.id_req = 11 and c1.id_rec=127) and (c2.id_req = 127 and
>c2.id_rec=11)
>This returns "rel1=c, rel2=r"
>But what about the cases when there is only one row?
>How can I get a result like "rel1=r, rel2=NULL" ?????

>Thanks for your help!



Mon, 25 Aug 2003 21:19:06 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. SELECT multiple rows back as one row with many columns

2. selecting rows into one row

3. select case return rows in one row (how to group by three column)

4. Grouping multiple rows into 1 row during a select into

5. Concatenating the results of multiple rows into on computed column on Select

6. column in multiple rows to multiple columns in one row

7. column in multiple rows to multiple columns in one row

8. Select a-row alternative from several tables one-by-one until found

9. One source row to multiple destination rows

10. Pivoting one source row into multiple destination rows

11. concatenate varchar field from multiple rows into one row as a group

12. Get values from multiple rows as attributes of one row


 
Powered by phpBB® Forum Software