Select Data From a Table By Selecting Category From Two Tables

Question

4.00/5 (1 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

Banci Gurjar

Updated 25-Dec-12 2:17am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

sisir patro · Answer 1 · 2012-12-25T03:42:00

Solution 1

Hi,

Try like this:

SQL

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

Posted 25-Dec-12 3:42am

sisir patro

Aarti Meswania · Answer 2 · 2012-12-25T18:28:00

A) I need Data if Posted By My Friends

SQL

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

SQL

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

SQL

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!
:)