Postgresql - Need to get data from 2 tables

Question

Users Table:

ID, UserName, Location

Follow Table:

ID, User_ID, User_Follow_ID

--User_ID --> ID of user who is following 

--User_Follow_ID --> ID of user being followed

I want to get the Username, location of people whose location is same as the 'User' and i also want to know whether the user is following them or not. The query i have written to get people in the same location is as follows:

String query = @"
    select * 
    from User 
    where Location = (
        select Location 
        from Users 
        where UserName ='abc'
    ) and UserName != 'abc'
";

I need to modify this query to connect to or include data from the Follow table as well.

I'm using PostgreSql DB and writing code in C#. Any help or suggestion will be appreciated!

Andomar · Accepted Answer · 2012-12-28 11:54:21Z

1

You could use an inner join to find other users in the same location, and a left join to retrieve a potential match in the following table:

select  you.UserName as You
,       case
        when fme.User_Follow_ID is not null then 'True'
        else 'False'
        end as YouFollowMe
,       case
        when fyou.User_Follow_ID is not null then 'True'
        else 'False'
        end as IFollowYou
from    User me
join    User you
on      you.Location = me.Location
        and you.UserName <> me.UserName
left join
        Following fme
on      fme.User_Follow_ID = me.User_ID
        and fme.User_ID = you.User_ID
left join
        Following fyou
on      fyou.User_Follow_ID = you.User_ID
        and fyou.User_ID = me.User_ID
where   me.UserName = 'abc'

edited Dec 28, 2012 at 11:54

answered Dec 28, 2012 at 11:48

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NamrataP Over a year ago

Thank you @Andomar..Sry for the delay in my reply..took me a while to understand what ws happening..though u've written it in a very self-explanatory way...but i'm getting "Field" does not exist" exception..even though my USERS table has UserName field.

Collectives™ on Stack Overflow

Postgresql - Need to get data from 2 tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related