How to select data from multiple tables

Kasuni Abegunawardana

8y1.2k.NET

Hi all,

I have created two tables as User_tb and consultant_tb in sql server.

I just wanna select data from this two tables for the login. Data in those tables are not unique.

There is an one login for both users and consultants. When i enter the email and password, it should validate from those two tables and redirect to the different pages.

Please let me know the Sql Query.

Thank you.

Table 1- User_tb

CREATE TABLE [dbo].[User_tb](
[ID] [int] IDENTITY(1,1) NOT NULL,
[U_FirstName] [nvarchar](50) NULL,
[U_LasName] [nvarchar](50) NULL,
[U_EmailAddress] [nvarchar](50) NOT NULL,
[U_Password] [nvarchar](50) NULL,
[U_CurrentWeight] [float] NULL,
[U_CurrentHeight] [float] NULL,
[U_Gender] [nvarchar](10) NULL,
[U_BirthDay] [date] NULL,
[U_BMI] [float] NULL,
CONSTRAINT [PK_User_tb] PRIMARY KEY CLUSTERED
(
[U_EmailAddress] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Table 2 - Consultant_tb

CREATE TABLE [dbo].[Consultant_tb](
[ID] [int] IDENTITY(1,1) NOT NULL,
[C_SurName] [nvarchar](50) NULL,
[C_FirstName] [nvarchar](50) NULL,
[C_MiddelName] [nvarchar](50) NULL,
[C_NIC_Number] [nvarchar](10) NULL,
[C_Gender] [nvarchar](10) NULL,
[C_Nationality] [nvarchar](50) NULL,
[C_Address] [nvarchar](100) NULL,
[C_PostalCode] [nvarchar](50) NULL,
[C_TelephoneNumber] [int] NULL,
[C_MobileNumber] [int] NULL,
[C_MedicalLicenceNumber] [nvarchar](50) NULL,
[C_EmailAddress] [nvarchar](50) NOT NULL,
[C_Password] [nvarchar](50) NULL,
[C_DegreeName] [nvarchar](50) NULL,
[C_University] [nvarchar](50) NULL,
[C_YearObtained] [date] NULL,
[C_AdditionalQualification] [nvarchar](500) NULL,
[C_Experience] [nvarchar](500) NULL,
[C_Evidence] [nvarchar](50) NULL,
[C_Advertisement] [nvarchar](50) NULL,
CONSTRAINT [PK_Consultant_tb] PRIMARY KEY CLUSTERED
(
[C_EmailAddress] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

13 Replies

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

8y ago
hi Kasuni,
in my code the if condition will execute when row.count is greater then 0.Else it will redirect to WebForm1.aspx(This is the login page in my code).if email password not match then he will be redirected to WebForm1.aspx(login page).
Esakki Muthu8y ago
hey,
why can't you try with stored procedure , which I mentioned above is there any problem implementing procedure?
Ketan Hirapara8y ago
yes,
my bad it's dt DataTable object.
Kasuni Abegunawardana8y ago
Hi
Ashwani Bakshi,
Your code is woring but it redirect only one page. It does't go through the if condition it goes to else redirect page
Ketan Hirapara8y ago
hi kasuni,
just copy this and replace your method.

protected void btn_login_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("select ut.*,ct.* from User_tb ut join Consultant_tb ct on ut.ID =ct.id ct.ID where ut.U_EmailAddress = (select C_EmailAddress from Consultant_tb where C_EmailAddress='" + txt_EmailAddress.Text.Trim() + "') and ut.U_Password= (select C_Password from Consultant_tb where C_Password='" + txt_Password.Text.Trim() + "' ",con);
cmd.Parameters.AddWithValue("@U_EmailAddress", txt_EmailAddress.Text);
cmd.Parameters.AddWithValue("@U_Password", txt_Password.Text);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
string IsConsultant = dr.Rows[0]["C_EmailAddress"];
sda.Fill(dt);
con.Open();
int i = cmd.ExecuteNonQuery();
if (IsConsultant.Length > 0)
{
Response.Redirect("~/Consultant/Payment.aspx");
}
else
{
Response.Redirect("~/Pages/BMI.aspx");
}
if (chkRememberMe.Checked == true)
{
Response.Cookies["U_EmailAddress"].Value = txt_EmailAddress.Text;
Response.Cookies["U_Password"].Value = txt_Password.Text;
// set cookie expiration in a month
Response.Cookies["U_EmailAddress"].Expires = DateTime.Now.AddMonths(1);
Response.Cookies["U_Password"].Expires = DateTime.Now.AddMonths(1);
}
else
{
Response.Cookies["U_EmailAddress"].Expires = DateTime.Now.AddMonths(-1);
Response.Cookies["U_Password"].Expires = DateTime.Now.AddMonths(-1);
}
con.Close();
}

Hope it will work for you.
Thanks,
Ketan
Esakki Muthu8y ago
Hi Kasuni, Good Day.

Do this with stored procedure, Please try the below code and let me know.

Call this procedure in your code and based on the return result you can either redirect to User page(If procedure returns 1) or Contaltant page (If procedure retunrs 2) or invalid user login error message(If procedure returns 3).
CREATE PROCEDURE usp_validateUser
@Email nvarchar(50),
@Password nvarchar(50),
@IsValidLogin CHAR(1) OUT
AS
BEGIN
IF EXISTS (SELECT 1 FROM dbo.Consultant_tb WHERE C_EmailAddress = @Email AND C_Password = @Password)
BEGIN
SET @IsValidLogin = '1';
RETURN
END
ELSE IF EXISTS(SELECT 1 FROM dbo.User_tb WHERE U_EmailAddress =@Email AND U_Password = @Password)
BEGIN
SET @IsValidLogin = '2'
RETURN
END
ELSE
SET @IsValidLogin = '3'
RETURN
END
/*
Return 1 - Valid Consultant
Retun 2 - Valid User
Retun 3 - Not a valid user
*/
8y ago
Hi Kasuni

You should provide them role and based on it you can redirect them to there respective pages.
Kasuni Abegunawardana8y ago
Hi Ashwani Bakshi,

Your code is working but the thing is when i'm going to redirect two different pages its only redirect to BMI.aspx.

I want to redirect user_tb users into "BMI.aspx" and consultant_tb users into "~/Consultant/Payment.aspx" pages.

How can i do that ?

protected void btn_login_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("select ut.*,ct.* from User_tb ut join Consultant_tb ct on ut.ID =ct.id ct.ID where ut.U_EmailAddress = (select C_EmailAddress from Consultant_tb where C_EmailAddress='" + txt_EmailAddress.Text.Trim() + "') and ut.U_Password= (select C_Password from Consultant_tb where C_Password='" + txt_Password.Text.Trim() + "' ",con);
cmd.Parameters.AddWithValue("@U_EmailAddress", txt_EmailAddress.Text);
cmd.Parameters.AddWithValue("@U_Password", txt_Password.Text);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
con.Open();
int i = cmd.ExecuteNonQuery();
if (dt.Rows.Count > 0)
{
if (chkRememberMe.Checked == true)
{
Response.Cookies["U_EmailAddress"].Value = txt_EmailAddress.Text;
Response.Cookies["U_Password"].Value = txt_Password.Text;
// set cookie expiration in a month
Response.Cookies["U_EmailAddress"].Expires = DateTime.Now.AddMonths(1);
Response.Cookies["U_Password"].Expires = DateTime.Now.AddMonths(1);
}
else
{
Response.Cookies["U_EmailAddress"].Expires = DateTime.Now.AddMonths(-1);
Response.Cookies["U_Password"].Expires = DateTime.Now.AddMonths(-1);
}
Response.Redirect("~/Pages/BMI.aspx");
Session.RemoveAll();
}
else
{ Response.Redirect("~/Consultant/Payment.aspx");
//Response.Write("");
//con.Close();
}
Ketan Hirapara8y ago
hi,

plz replace this code

cmd.CommandText = "select * from User_tb join Consultant_tb on User_tb.ID = Consultant_tb.ID where User_tb.U_EmailAddress = (select from Consultant_tb where C_EmailAddress='" + txt_EmailAddress.Text.Trim() + "') and User_tb.U_Password= (select C_Password from Consultant_tb where C_Password='" + txt_Password.Text.Trim() + "' ";

by ,

cmd.CommandText = "select ut.*,ct.* from User_tb ut join Consultant_tb ct on ut.ID =ct.id ct.ID where ut.U_EmailAddress = (select C_EmailAddress from Consultant_tb where C_EmailAddress='" + txt_EmailAddress.Text.Trim() + "') and ut.U_Password= (select C_Password from Consultant_tb where C_Password='" + txt_Password.Text.Trim() + "' ";
Bryian Tan8y ago
I don't see why it neccessary to have several inline select. It look to me that the email and password on both tables are identical. Again, that my assumpstion.

The code can be simplify to
string commandText = "select * from User_tb join Consultant_tb on User_tb.ID = Consultant_tb.ID where User_tb.U_EmailAddress = '" + txt_EmailAddress.Text.Trim() + "' "
+ "AND User_tb.U_Password = '" + txt_Password.Text.Trim() + "'";
I would suggest to use parameterized queries when possible to prevent SQL injection.
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-2
8y ago
The Query Is working Fine
protected void Button1_Click(object sender, EventArgs e)
       {
           SqlConnection co = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["con"].ConnectionString);
           SqlDataAdapter da = new SqlDataAdapter("select * from User_tb join Consultant_tb on User_tb.ID=Consultant_tb.ID where User_tb.U_EmailAddress=(select C_EmailAddress from Consultant_tb where C_EmailAddress='"+TextBox1.Text+"') and User_tb.U_Password=(select C_Password from Consultant_tb where C_Password='"+TextBox2.Text+"')", co);
           DataSet ds = new DataSet();
           da.Fill(ds);
           if (ds.Tables[0].Rows.Count > 0)
           {
               Response.Redirect("Home.aspx");
           }
           else
               Response.Redirect("WebForm1.aspx");
       }
see your connection you didn't closed it con.close().
Kasuni Abegunawardana8y ago
It dosen't work for me i got and exception like this.

This is the my code.

try
{
con.Open();
cmd.CommandText = "select * from User_tb join Consultant_tb on User_tb.ID = Consultant_tb.ID where User_tb.U_EmailAddress = (select from Consultant_tb where C_EmailAddress='" + txt_EmailAddress.Text.Trim() + "') and User_tb.U_Password= (select C_Password from Consultant_tb where C_Password='" + txt_Password.Text.Trim() + "' ";
cmd.Connection = con;
apd.SelectCommand = cmd;
apd.Fill(ds, "User_tb");
if (ds.Tables[0].Rows.Count > 0)
{
if (txt_EmailAddress.Text.Trim() == "")
{
if (txt_Password.Text.Trim() == "")
{
if (chkRememberMe.Checked)
{
Response.Cookies["U_EmailAddress"].Expires = DateTime.Now.AddDays(30);
Response.Cookies["U_Password"].Expires = DateTime.Now.AddDays(30);
}
else
{
Response.Cookies["U_EmailAddress"].Expires = DateTime.Now.AddDays(-1);
Response.Cookies["U_Password"].Expires = DateTime.Now.AddDays(-1);
}
}
else
{
Response.Write("");
}
}
else
{
Response.Redirect("~/BMI.aspx");
}
Response.Cookies["U_EmailAddress"].Value = txt_EmailAddress.Text.Trim();
Response.Cookies["U_Password"].Value = txt_Password.Text.Trim();
}
}
catch (Exception ex)
{
Response.Write(ex);
}

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'from'. Incorrect syntax near '415263'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) at Food_Calorie_Calculator__Final_Project.Account.Login.user_2() in c:\Users\A\Desktop\Food new one\Food Calorie Calculator- Final Project\Food Calorie Calculator- Final Project\Account\Login.aspx.cs:line 95 ClientConnectionId:df169bfc-0e87-478e-b8fc-711dc220487a Error Number:156,State:1,Class:15
8y ago
Hi Kasuni,

You should use join for fetching data from both table on login.

select * from User_tb join Consultant_tb on User_tb.ID=Consultant_tb.ID where User_tb.U_EmailAddress=(select C_EmailAddress from Consultant_tb where C_EmailAddress=' ') and User_tb.U_Password=(select C_Password from Consultant_tb where C_Password=' ')

How to select data from multiple tables

Ashwani Bakshi,