Query to select record based on age and qualification in SQL Server

Last Reply 29 days ago By itsme

Posted one month ago

I am trying to match the job seeker profile with posted job but i am not sure about how to do this. I have 2 matching column(Qualification and age or JS_age)

Job seeker profile table 
CREATE TABLE [dbo].[Job_Seeker_Profile] (
    [Id]            INT          IDENTITY (1, 1) NOT NULL,
    [js_name]       VARCHAR (50) NOT NULL,
    [js_address]    VARCHAR (50) NOT NULL,
    [Contact]       VARCHAR (50) NOT NULL,
    [email]         VARCHAR (50) NOT NULL,
    [tenth]         VARCHAR (50) DEFAULT ((0)) NULL,
    [twelve]        VARCHAR (50) DEFAULT ((0)) NULL,
    [graduate]      VARCHAR (50) DEFAULT ((0)) NULL,
    [Qualification] VARCHAR (50) NULL,
    [Gender]        VARCHAR (50) NULL,
    [DOB]           DATE         NULL,
    [JS_age]        INT          DEFAULT ((0)) NULL,
    [Diploma]       VARCHAR (50) DEFAULT ((0)) NULL,
    [Masters]       VARCHAR (50) DEFAULT ((0)) NULL,
    [Phd]           VARCHAR (50) DEFAULT ((0)) NULL,
    [Bio]           VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

 

Posted job table
CREATE TABLE [dbo].[Post_job_details] (
    [Id]              INT           IDENTITY (1, 1) NOT NULL,
    [Job_title]       VARCHAR (50)  NULL,
    [Company_name]    VARCHAR (20)  NOT NULL,
    [Salary]          VARCHAR (50)  NOT NULL,
    [Skills]          VARCHAR (MAX) NOT NULL,
    [Qualification]   VARCHAR (MAX) NOT NULL,
    [Details]         VARCHAR (50)  NULL,
    [Experience]      VARCHAR (50)  NULL,
    [email]           VARCHAR (50)  NOT NULL,
    [Location]        VARCHAR (50)  NULL,
    [Last_Apply_date] DATE          NULL,
    [City]            VARCHAR (50)  NULL,
    [Age]             VARCHAR (50)  NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

 

Posted one month ago

Hi itsme,

Refer below query.

SQL

SELECT * FROM Post_job_details
WHERE Qualification IN (select Qualification from Job_Seeker_Profile) 
OR Age IN (Select JS_age from Job_Seeker_Profile)

 


Posted 29 days ago

Thanks

I also found one answer :

protected void Page_Load(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["Q_SS_regnConnectionString1"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    string query = "SELECT Job_title,Company_name,Salary,Location,City,Qualification,Age FROM Post_job_details WHERE Qualification IN (select Qualification from Job_Seeker_Profile) OR Age In (Select JS_age from Job_Seeker_Profile)  and email='" + Session["mail"].ToString() + "'";
    SqlCommand cmd = new SqlCommand(query,con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    GridView2.DataSource = dt;
    GridView2.DataBind();
}