JDBC sql query

Thread view:

raavi - 02 Mar 2006 09:24 GMT

Hi all
can anyone suggest an sql query for the following table...
The Database table look like

Table:TblFnds

ID FndID
1 2
1 3
1 5
2 3
4 2
5 1
8 1

I should get the direct friends of 1 without repetition
For example Direct Friends of 1 must return the values 2,3,5,8.
So can u please suggest an SQL query for this??

Reply to this Message

the.dodger@gmail.com - 02 Mar 2006 10:36 GMT

> Hi all
> can anyone suggest an sql query for the following table...
[quoted text clipped - 14 lines]
> For example Direct Friends of 1 must return the values 2,3,5,8.
> So can u please suggest an SQL query for this??

select distinct FndID from TBlFnds where id=1

But you should ask this in a sql group...

Reply to this Message

James McGill - 02 Mar 2006 15:57 GMT

> I should get the direct friends of 1 without repetition
> For example Direct Friends of 1 must return the values 2,3,5,8.

The "DISTINCT" keyword is what you're looking for.

Reply to this Message

Dag Sunde - 03 Mar 2006 09:01 GMT

I see that nobody else have reacted on the point that you also want
8 returned...

Is your requirement such that you also want those that have 1 as a friend
returned, and not only those friends 1 lists as his friend?

If so, its two queries, or a UNION query...

SELECT f1.FndId AS Friend
FROM TblFnds f1
WHERE f1.ID = 1
UNION
SELECT f2.Id AS Friend
FROM TblFnds f2
WHERE f2.ID NOT IN (SELECT f3.FndId FROM TblFnds f3 WHERE f3.ID = 1)
AND f2.FndId = 1