
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!