JDBC sql query

Thread view:

raavi - 02 Mar 2006 10:45 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

Dieter Bender - 02 Mar 2006 11:34 GMT

select distinct FndId
from TblFnds
where ID = 1

Dieter Bender

> 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??

Reply to this Message

Bjorn Abelli - 02 Mar 2006 14:20 GMT

"Dieter Bender" wrote...

>> 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

...which will miss no 8.

Another suggestion should be;

select FndId as Friends
from TblFnds
where ID = 1
union
select ID as Friends
from TblFnds
where FndId = 1

// Bjorn A

Reply to this Message

Dieter Bender - 02 Mar 2006 15:03 GMT

> Another suggestion should be;
>
[quoted text clipped - 7 lines]
>
> // Bjorn A

wich will show up nr. 5 twice

on db2 will work:

select distinct f.Friends from(
select FndId as Friends
from TblFnds
where ID = 1
union
select ID as Friends
from TblFnds
where FndId = 1
) as f

Dieter Bender

Reply to this Message

Green - 02 Mar 2006 16:34 GMT

union does not show duplicates. 'union all' will.

Reply to this Message

Alexey Shevchenko - 06 Mar 2006 13:59 GMT

Why we can not use the following:

select FndId as Friends
from TblFnds
where ID = 1 or FndId = 1

> "Dieter Bender" wrote...
>
[quoted text clipped - 34 lines]
>
> // Bjorn A

Reply to this Message

Bjorn Abelli - 06 Mar 2006 14:19 GMT

"Alexey Shevchenko" wrote...

> "Bjorn Abelli" wrote...

>>>> 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 FndId as Friends
>> from TblFnds
[quoted text clipped - 5 lines]
>>
>> // Bjorn A

> Why we can not use the following:
>
> select FndId as Friends
> from TblFnds
> where ID = 1 or FndId = 1

You'll get the result: 2, 3, 5, 1, 1

...and not the intended: 2, 3, 5, 8

Look at what column you're retrieving values from: FndId

Then look again into my suggestion (above, with the union), and look at the
two separate queries inside.

Each of the queries retrieves values from one column depending on the value
in *another* column, then merges the results from those queries. As "union"
eliminates duplicates, 5 will only turn up once, as intended.

// Bjorn A

P.S. Please don't top post.

Reply to this Message

Alexey Shevchenko - 06 Mar 2006 15:55 GMT

Oh, yes - UNION ALL is acceptable. Of course, only if the data type of
columns is identical.

> "Alexey Shevchenko" wrote...
>
[quoted text clipped - 51 lines]
>
> P.S. Please don't top post.

Reply to this Message

Bjorn Abelli - 06 Mar 2006 22:51 GMT

"Alexey Shevchenko" wrote...

> "Bjorn Abelli" wrote...
>> "Alexey Shevchenko" wrote...
>>> "Bjorn Abelli" wrote...

>>>> select FndId as Friends
>>>> from TblFnds
[quoted text clipped - 22 lines]
>> value in *another* column, then merges the results from those queries. As
>> "union" eliminates duplicates, 5 will only turn up once, as intended.

> Oh, yes - UNION ALL is acceptable.

No, the *default* for UNION is to eliminate duplicates (as with DISTINCT),
but using the keyword ALL *keeps* them.

> Of course, only if the
> data type of columns is identical.

In this case they obviously are identical.

// Bjorn A

P.S. And I repeat the plead from the last message:

Please don't top post.

It just messes things up and make it difficult for people to follow any
discussion.

Reply to this Message

JDBC sql query

Free Magazines