How to search all friend request sent in SQL server

Question

1.00/5 (2 votes)

See more:

Hello friens,
i need help with join in sql. i have 2 table 1st is tbl_register amd 2nd is request.

Tbl_register

UserID 	   f_name	   Designationnm	Comapny_name
1	   aaa	               CEO	           Apple
2	   Rajeev	   General manager	   Dell
3	   upendra	   Managing director	    Dell
4	   soumendr        Vice president	   lenovo
9	   Suneal	  Managing director	   Apple
8	    asa	               CEO	           Apple

request table

req_from_id	req_to_id	req_status
2	           9	           0
3	           9	           0
4	           9	           0
2	           8	           0
2	           1	           0

i need to get output like this.

f_name       Designationnm   Comapny_name  UserID    req_from_id
aaa  aa             CEO            Apple     1                 2
Rajeev          General manager    Dell      2                 9
Suneal          Managing director  Apple     9             2
asa               CEO              Apple     8             2

What I have tried:

 select distinct 
tb1.UserID,tb1.f_name,tb1.Designationnm,tb1.Comapny_name,tb2.req_from_id	  
from Tbl_register tb1 
left join
tbl_friend_and_match_request tb2
on 
tb1.UserID = tb2.req_from_id
where 
Comapny_name like '%a%' or Designationnm like '%Manager%'

Posted 29-Aug-17 20:16pm

Rajiv.net40

Updated 30-Aug-17 1:04am

v5

Add a Solution

Comments

Rajiv.net40 30-Aug-17 2:16am

it works but gives duplicate records

Graeme_Grant 30-Aug-17 3:52am

Please click on the "Improve question" widget above and post the update there - it won't be easily seen in the comments section...

Rajiv.net40 30-Aug-17 3:57am

have u any solution for my question ?

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)

OriginalGriff · Answer 1 · 2017-08-29T22:13:00

Solution 1

It gives duplicates, because that is what you told it to do.
If you change it so that it returns req_to_id instead of req_from_id then it's obvious why:

SQL

SELECT r.f_name, r.Designationnm, r.Comapny_name, r.UserID, f.req_to_id
  FROM tbl_Register r
  LEFT JOIN tbl_friend_and_match_request f ON f.req_from_id = r.UserID 
where Comapny_name like '%a%' or Designationnm like '%Manager%'

Becuase there are three rows in the request table that JOIN with the one row in the register table.
And there is no way that SQL on it's own has any idea which one you want!

f_name	Designationnm	Comapny_name	UserID	req_to_id
aaa       	CEO                 	Apple     	1	NULL
Rajeev    	General manager     	Dell      	2	9
Rajeev    	General manager     	Dell      	2	8
Rajeev    	General manager     	Dell      	2	1
Suneal    	Managing director   	Apple     	9	NULL
asa       	CEO                 	Apple     	8	NULL

Your tables need rethinking, or you need to decide how SQL should "pick" one of the options when this occurs.

Posted 29-Aug-17 22:13pm

OriginalGriff

Comments

Rajiv.net40 30-Aug-17 4:27am

I changed it to req_to_id but it still shows all the entries

aaa CEO Apple 1 1
Rajeev General manager Dell 2 NULL
Suneal Managing director Apple 9 9
Suneal Managing director Apple 9 9
Suneal Managing director Apple 9 9
asa CEO Apple 8 8

OriginalGriff 30-Aug-17 4:31am

Yes - that's what I said. You need to decide how SQL should select the "one" that you want - we have no idea how it should be picked, and nor does SQL!

Rajiv.net40 30-Aug-17 4:35am

i dont know how to decide to select one.if u know then tell me or any idea

OriginalGriff 30-Aug-17 5:11am

It's your data! If *you* don't know on what criteria one should be selected, how do you expect me (or SQL) to have any idea? :laugh:

What makes "9" more appropriate than "8" or "1"? You at least have an idea what this information is need for - we don't.

Rajiv.net40 30-Aug-17 5:16am

i need all users with friend request id in search result from both table.

OriginalGriff 30-Aug-17 5:19am

And that is what you are getting!

Rajiv.net40 30-Aug-17 6:34am

finally i have updated my quey and for result i was looking for but there is a problem only it null in req_from_id.

select distinct
tb1.UserID,tb1.f_name,tb1.Designationnm,tb1.Comapny_name,tb2.req_from_id
from Tbl_register tb1
left join
tbl_friend_and_match_request tb2
on
tb1.UserID = tb2.req_from_id
where
Comapny_name like '%a%' or Designationnm like '%Manager%'

OriginalGriff 30-Aug-17 7:14am

Except...that isn't doing anything like what you asked for. None of the values in your req_from_id matches the column values in your example. Why? For the reasons I gave you above...

Member 10393746 · Answer 2 · 2017-08-31T06:32:00

Solution 2

Select distinct
tb1.UserID,tb1.f_name,tb1.Designationnm,tb1.Comapny_name,MAX(tb2.req_from_id)
from Tbl_register tb1
left join
tbl_friend_and_match_request tb2
on
tb1.UserID = tb2.req_from_id
where
Comapny_name like '%a%' or Designationnm like '%Manager%'
Group By tb1.UserID,tb1.f_name,tb1.Designationnm,tb1.Comapny_name

Posted 31-Aug-17 6:32am

Member 10393746

Updated 31-Aug-17 7:07am

Comments

Rajiv.net40 1-Sep-17 7:09am

not worked your query.