How to display student have stdnt_activity 5,10

In this blog we will know how to display student have stdnt_activity 5,10

 

Here is the scenario

 

I have one table student, i.e stdnt_id, stdnt_name, stdnt_activity

i have values in student table like this.

stdnt_id          stdnt_name                           stdnt_activity

1                          kiran                                     5

1                          kiran                                     10

1                          kiran                                     15

2                          sachin                                     5

2                          sachin                                    10

3                          venkat                                     5

3                          venkat                                    10

3                          venkat                                    15

4                          kumar                                      5

4                          kumar                                     10

5                          naveen                                     5

5                          naveen                                    10

 

 

Here stdnt_activity 5 mean -> pending, 10 -> In progress, 15 means -> closed

 

So, i want a query to display the values of student table based on condition.

The condition is for example:

 

The student kiran having stdnt_activity 5,10,15 where as sachin having stdnt_activity 5,10.

 

I need a query do display all student records in a table which is having stdnt_activity 5,10 and not 15.

 

If student have stdnt_activity 5,10,15.we don't need to display to the user.

 

If student have stdnt_activity 5,10 then we need to display the values.

 

By above example i only need to display the values of sachin, kumar, naveen (these student's doesn't have 15).

 

While student's kiran, venkat will have 5,10,15.So these records need to be ignored (no need to display).

 

Solution: -

 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Student_have_activity_5_and_10._Default" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

    <asp:GridView ID="GridView1" runat="server">

        </asp:GridView>

    </div>

    </form>

</body>

</html>

 

 

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

namespace Student_have_activity_5_and_10

{

    public partial class _Default : System.Web.UI.Page

    {

        string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        SqlCommand com;

        string str;

        DataSet ds;

        SqlDataAdapter sqlda;

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                bindgrid();

            }

        }

        void bindgrid()

        {

            SqlConnection con = new SqlConnection(connStr);

            con.Open();

           

            str = "SELECT * FROM student e where not exists(select stdnt_id from student where stdnt_activity= 15 and stdnt_name= e.stdnt_name)";

            com = new SqlCommand(str, con);

            sqlda = new SqlDataAdapter(com);

            ds = new DataSet();

            sqlda.Fill(ds, "t1");

            GridView1.DataSource = ds;

            GridView1.DataMember = "t1";

            GridView1.DataBind();

            con.Close();

        }

    }

}

 

Thanks