locked
Need help with this query RRS feed

  • Question

  • User739135361 posted

    Hi,

    I have a two database table Profile and Proposal. 

    ProfileID FirstName LastName
    1 aaa bbb
    2 Abc abc
    3 Def def
    4 Ghi ghi
    5 fasdf fasf
    ID ProposedBy (FK ProfileID) CreatedBy ProposedTo (FK ProfileID)
    1 1 1 2
    2 1 1 3
    3 2 2 3
    4 1 1 4

    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
    
    SELECT DISTINCT
    MP.ProfileID,
    MP.FirstName,
    MP.LastName,			
    PR.ProposalBy,	
    PR.ProposalTo,
    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
    		THEN 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)

    Friday, June 26, 2020 12:05 PM

All replies

  • User452040443 posted

    Hi,

    I don't know if I understand your question correctly but try:

    WHERE 
        MP.ProfileID = @ProfileID AND 
        (PR.ProposalBy = @ProfileID OR PR.ProposalTo = @ProfileID) AND
        (PR.ProposalBy = @UserProfileID OR PR.ProposalTo = @UserProfileID)

    Hope this help

    Friday, June 26, 2020 6:15 PM