Last post Jun 26, 2020 06:15 PM by imapsp
Jun 26, 2020 12:05 PM|N1ZAM|LINK
I have a two database table Profile and Proposal.
Now, User 1 has sent proposal to profile id 2,3,4. In this scenario, when 1 sees the profile of 2,3 or 4 he should get the status as Proposal Sent / Invitation Sent. Other way round, when 2,3 or 4 views profile 1, as they have received an invite, they should
see the status as Proposal Received / Invitation Received. I am able to accomplish this. But the requirement is specific to user 1 viewing profile 2 or User 2 viewing profile 1. Hence I should get unique record. Instead I get all records of 1 if one is viewing
2 or 2 is viewing 1.
Secondly, If its like profile 5 viewing profile 1 or vice versa, I dont get any records.
Query I could come up with is as follows. Please suggest whats wrong.
DECLARE @ProfileID INT
DECLARE @UserProfileID INT
SET @ProfileID =3
SET @UserProfileID =4
CASE WHEN PR.ProposalBy = @UserProfileID
THEN 'Proposal Sent'
WHEN PR.ProposalBy = @ProfileID
THEN 'Proposal Received'
ELSE '--' END AS 'Proposal',
CASE WHEN PR.ProposalBy = @UserProfileID AND PR.Accepted = 1
ELSE 0 END AS 'ProposalStatus'
FROM tblMatrimonyProfile MP
INNER JOIN tblUsers U ON U.UserID = MP.ProfileID
INNER JOIN tblProposal PR ON MP.ProfileID = PR.CreatedBy
WHERE MP.ProfileID = @ProfileID AND (PR.ProposalBy =@ProfileID OR PR.ProposalTo= @ProfileID) OR (PR.ProposalBy =@UserProfileID OR PR.ProposalTo = @UserProfileID)
Jun 26, 2020 06:15 PM|imapsp|LINK
I don't know if I understand your question correctly but try:
MP.ProfileID = @ProfileID AND
(PR.ProposalBy = @ProfileID OR PR.ProposalTo = @ProfileID) AND
(PR.ProposalBy = @UserProfileID OR PR.ProposalTo = @UserProfileID)
Hope this help