how to do innerJoin these three Table aql

Jagapathibabu Banoth

--GetUserDetails
Alter PROCEDURE GetUserDetails
@MobileNo bigint,
@Password Nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;

IF EXISTS (SELECT 1 FROM Users WHERE MobileNo = @MobileNo AND Password=@Password AND KycStatus = 1)
BEGIN

SELECT
U.UserID,
U.MobileNo,
U.Firstname,
U.Lastname,
U.KycStatus,
U.CratedDate,
U.Password,
R.RoleName
FROM
Users U
INNER JOIN
UserCredential C ON U.UserID = C.UserID
INNER JOIN
Roles R ON U.RoleID = R.RoleID
WHERE
U.MobileNo = @MobileNo;
END
ELSE
BEGIN

SELECT 'Error: KYC status is not valid.' AS ErrorMessage;
END
END;

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shino Kp2y ago

Alter PROCEDURE GetUserDetails
    @MobileNo bigint,
    @Password Nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;     
    IF EXISTS (SELECT 1 FROM Users WHERE MobileNo = @MobileNo AND Password=@Password AND KycStatus = 1)
        BEGIN    
            SELECT  U.UserID,U.MobileNo,U.Firstname,U.Lastname,U.KycStatus,
            U.CratedDate,U.Password,R.RoleName
            FROM Users U
            LEFT JOIN UserCredential C ON U.UserID = C.UserID
            LEFT JOIN Roles R ON U.RoleID = R.RoleID
            WHERE U.MobileNo = @MobileNo;
        END
    ELSE
        BEGIN    
            SELECT 'Error: KYC status is not valid.' AS ErrorMessage;
        END
END;

Hi ,

I dont know the issue with you , but you get some data from user details( except UserCredential ,Roles )

Thank you

Naveen Kumar2y ago
Based on your query, the join seems to be correct. What issue you are facing exactly? Also If you provide the table definition it will help to identify the joining columns are correct.