HelloI have below situation3 users friends registered initially as underSQL Table ndash MId UserId Password1 A AXXX2 B BXXX3 C CXXX When ldquoArdquo logged in He was directed to a secure page with following UI in a gridviewUserId CommentsB TextBox ndash Required fieldC T

SQL query to return non matching record from another table in SQL Server

sat

SOLVED CLOSED ACTIVE ANSWERED
User: sat
Posted: on Jun 08, 2018 05:59 AM
Forum: SQL Server
Answer: 1
Views: 1844

Hello,

I have below situation:

3 users (friends) registered initially as under:

SQL Table – M

Id UserId Password

1 A AXXX

2 B BXXX

3 C CXXX

When “A” logged in He was directed to a secure page with following UI in a gridview

UserId Comments

B TextBox – Required field

C TextBox – Required field

Save Button

“A” entered the comments for “B” & “C” and saved as under:

SQL Table - N

CId UserId ToUserId Comments

1 A B Comment1

2 A C Comment2

Meanwhile some more users registered and now Table “M” is like as under:

Id UserId Password

1 A AXXX

2 B BXXX

3 C CXXX

4 D DXXX

5 E EXXX

“B” also logs in and following similar UI, fills the comments for “A”, “C” and “D”.

SQL Table – N now looks like

CId UserId ToUserId Comments

1 A B Comment1

2 A C Comment2

3 B A Comment3

4 B C Comment4

5 B D Comment5

Now when “A” logs in, He SHOULD get below UI on his secure page gridview:

UserId Comments

D TextBox – Required field

E TextBox – Required field

Save Button

The idea is that each user has to write comments for every other users. Please advise the sql query that gets the above result or help me complete my sql statement as: “SELECT M.UserId FROM M INNER JOIN N ON N.UserId = M.UserId WHERE M.UserId IS NOT LIKE ‘”+ Label1.text +”’ AND ---------------------”

Thanks for your help. Sat

Download FREE API for Word, Excel and PDF in ASP.Net

pandeyis...

0 2

Reply
ANSWER
User: pandeyism
Replied: on Jun 08, 2018 07:34 AM
Report

Hi sat,

Please take reference the below query and change your query.

SQL

DECLARE @UserTbl TABLE(Id INT, UserId CHAR(1), Password VARCHAR(20))
INSERT INTO @UserTbl VALUES(1,'A','AXXX')
INSERT INTO @UserTbl VALUES(2,'B','BXXX')
INSERT INTO @UserTbl VALUES(3,'C','CXXX')
INSERT INTO @UserTbl VALUES(4,'D','DXXX')
INSERT INTO @UserTbl VALUES(5,'E','EXXX')

DECLARE @CommentTbl TABLE(CId INT, UserId CHAR(1),ToUserId CHAR(1), Comments VARCHAR(20))
INSERT INTO @CommentTbl VALUES(1,'A','B','Comment1')
INSERT INTO @CommentTbl VALUES(2,'A','C','Comment2')
INSERT INTO @CommentTbl VALUES(3,'B','A','Comment3')
INSERT INTO @CommentTbl VALUES(4,'B','C','Comment4')
INSERT INTO @CommentTbl VALUES(5,'B','D','Comment5')

DECLARE @UserId CHAR(1)
SET @UserId = 'A'
SELECT UserId FROM @UserTbl WHERE UserId NOT IN (SELECT ToUserId FROM @CommentTbl WHERE UserId = @UserId) AND UserId <> @UserId

SET @UserId = 'B'
SELECT UserId FROM @UserTbl WHERE UserId NOT IN (SELECT ToUserId FROM @CommentTbl WHERE UserId = @UserId) AND UserId <> @UserId

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

SQL query to return non matching record from another table in SQL Server

Company

Explore

Follow us

Contact Us