Click here to Skip to main content
13,139,834 members (53,155 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hello All,

I have Tables Like

1) User Which Holds The Master Data Like
User ID   Name    OtherColumns
1         Banshi   Nothing
2         Banshi2  Nothing 
3         Banshi3  Nothing 

--------------------------------------
2)Second Table "Friends" Holds Data Like
ID    UserID     FriendID    FriendCategory
1       1          2             2
2       2          1             2
3       4          1             2
4       1          4             2

----------------------------------------

3)Third Table "Subscribers" Holds Data Like
ID    SubscribersID  SubscribToID    FriendCategory
1       1              3                  3
2       1              4                  3

---------------------------------------
4)Fourth Table "FriendCategory" Like
ID      Name
2       Friends
3       Subscribers

---------------------------------------

5)Fifth Table "Posts" Contains Data Like
PostID   UserID  Post           
1         2      Hello Dear Post By Banshi2
2         3      Hello Dear  Post By Banshi3
3         4     Hello Dear  Post By Banshi4 


---------------------------------------
Now Being User 1 i Need Data Like
A) I need Data if Posted By My Friends
B) I need Data if Posted By i am Subscribed to
c) If User is My Friend Subscribed as Well Then it Must Show Friend not i am Subscribed

My Data Something Like
----------------------------
 
UserID      Name        Post                          Category
2          Banshi2    Hello Dear Post By Banshi2     Friends
3          Banshi3    Hello Dear Post By Banshi3     I Am Subcriber 
4          Banshi4    Hello Dear Post By Banshi4     Friends



Please Suugest Me How to Get Such a Out put in Sql Server 2008


Thanks in Advance
Banshi
Posted 25-Dec-12 2:15am
Updated 25-Dec-12 2:17am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi,

Try like this:

select m.ID, m.Name, p.Post, fc.Name from Master Data m
  inner join Friends f on m.ID = f.UserID
  inner join FriendCategory fc on fc.ID = f.FriendCategory



Thanks
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

A) I need Data if Posted By My Friends
select f.userid,f.friendids,md.Name,post.Post,fc.Category
from post
left join friends f on friendids = post.userids
left join MasterData md on md.userid = f.friendids 
left join FriendCategory fc on f.FriendCategory = f.FriendCategory
where f.userid = 1


B) I need Data if Posted By i am Subscribed to
select f.SubscribersID as userid,f.SubscribToID,md.Name,post.Post,fc.Category
from post
left join Subscribers f on SubscribToID  = post.userids
left join MasterData md on md.userid = f.SubscribToID    
left join FriendCategory fc on f.FriendCategory = f.FriendCategory
where f.SubscribersID  = 1


c) If User is My Friend Subscribed as Well Then it Must Show Friend not i am Subscribed
select userid,friendids,name,post,case when sum(flag)=3 then 'I m subscribed to' else 'Friend' end as Category
from
(
    select f.userid,f.friendids,md.Name,post.Post,fc.Category, 1 as flag
    from post
    left join friends f on friendids = post.userids
    left join MasterData md on md.userid = f.friendids
    left join FriendCategory fc on f.FriendCategory = f.FriendCategory
    where f.userid = 1
    
    select f.SubscribersID as userid,f.SubscribToID,md.Name,post.Post,fc.Category , 3 as flag
    from post
    left join Subscribers f on SubscribToID  = post.userids
    left join MasterData md on md.userid = f.SubscribToID
    left join FriendCategory fc on f.FriendCategory = f.FriendCategory
    where f.SubscribersID  = 1
)
as a
group by userid,friendids,name,post


Happy Coding!
:)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web02 | 2.8.170915.1 | Last Updated 26 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100