Do i need DISTINCT here?

.NET
1

Hey,

I am trying to execute the following SQL query:


SELECT        tbl_vacancies.job_id, tbl_vacancies.job_name, tbl_vacancies.location, tbl_job_titles.body
FROM            tbl_vacancies INNER JOIN
                         tbl_job_titles ON tbl_vacancies.job_name = tbl_job_titles.name
WHERE        (tbl_vacancies.availability = 'Live')

It works but the problem is that is shows tbl_job_titles.body for each return. I want to only show the body field once…? Is this possible? I thought i may need DISTINCT but i don’t know how to add it as i was getting a syntax error…

This is the page that i am working on:

http://kidsunlimited.co.uk/vacancies_test.aspx

As you can see I need to show the Job name, the description (ONLY one time) and then show the location…

For this page i have this code:

string connVac = "...";
        SqlConnection dbConnVac = new SqlConnection(connVac);

        DataSet dsInfo = new DataSet();

        string sqlVac1 = "Select distinct job_name from tbl_vacancies Where (availability = 'Live')";
        string sqlVac2 = "select tbl_vacancies.job_id, tbl_vacancies.job_name, tbl_vacancies.location, tbl_vacancies.location as Region, " +
        "max(tbl_job_titles.body) as theDetails from tbl_vacancies, tbl_job_titles where tbl_vacancies.availability = 'Live' AND " +
        "tbl_vacancies.job_name = tbl_job_titles.name Group by tbl_vacancies.job_id, tbl_vacancies.job_name, tbl_vacancies.location, tbl_job_titles.body";
        dbConnVac.Open();

        SqlCommand dbVac = new SqlCommand(sqlVac1, dbConnVac);
        SqlDataAdapter adapter = new SqlDataAdapter();

        adapter.SelectCommand = dbVac;
        adapter.Fill(dsInfo, "Jobs");

        dbVac = new SqlCommand(sqlVac2, dbConnVac);

        adapter = new SqlDataAdapter();
        adapter.SelectCommand = dbVac;
        adapter.Fill(dsInfo, "JobDetails");

        dbConnVac.Close();

        int ocount = 0;
        int icount = 0;

        string jobname = "";

        for (ocount = 0; ocount < dsInfo.Tables["Jobs"].Rows.Count; ocount++)
        {
            Label2.Text += "<div id='vacancies'>" +
                    "<p><strong>" + dsInfo.Tables["Jobs"].Rows[ocount]["job_name"].ToString() + "</strong></p>";
            jobname = dsInfo.Tables["Jobs"].Rows[ocount]["job_name"].ToString().ToLower();

            for (icount = 0; icount < dsInfo.Tables["JobDetails"].Rows.Count; icount++)
            {
                if (jobname == dsInfo.Tables["JobDetails"].Rows[icount]["job_name"].ToString().ToLower())
                {
                    Label2.Text += "<div class='vacancy-info'><p>" + dsInfo.Tables["JobDetails"].Rows[icount]["theDetails"].ToString() + "</p><br/>" +
                "<span>" + dsInfo.Tables["JobDetails"].Rows[icount]["Region"].ToString() + "</span>" +
                    "</div>";
                }
            }
            Label2.Text += "</div>";
        }

Now this brings up the body more than once, so I am trying to change the SQL statement as shown at the very top… Can anyone please help?

Thanks
Regards

Billy

2

Ok i have had a play around and have manage to come up with this:


        string connVac = "...";
        SqlConnection dbConnVac = new SqlConnection(connVac);

        DataSet dsInfo = new DataSet();

        string sqlVac1 = "Select distinct job_name from tbl_vacancies Where (availability = 'Live')";
        string sqlVac2 = "SELECT DISTINCT tbl_job_titles.body, tbl_vacancies.job_id, tbl_vacancies.job_name, " +
                         "tbl_vacancies.location FROM tbl_vacancies INNER JOIN tbl_job_titles ON " +
                         "tbl_vacancies.job_name = tbl_job_titles.name " +
                         "WHERE (tbl_vacancies.availability = 'Live')";
        dbConnVac.Open();

        SqlCommand dbVac = new SqlCommand(sqlVac1, dbConnVac);
        SqlDataAdapter adapter = new SqlDataAdapter();

        adapter.SelectCommand = dbVac;
        adapter.Fill(dsInfo, "Jobs");

        dbVac = new SqlCommand(sqlVac2, dbConnVac);

        adapter = new SqlDataAdapter();
        adapter.SelectCommand = dbVac;
        adapter.Fill(dsInfo, "JobDetails");

        dbConnVac.Close();

        int ocount = 0;
        int icount = 0;

        string jobname = "";

        for (ocount = 0; ocount < dsInfo.Tables["Jobs"].Rows.Count; ocount++)
        {
            Label2.Text += "<div id='vacancies'>" +
                    "<p><strong>" + dsInfo.Tables["Jobs"].Rows[ocount]["job_name"].ToString() + "</strong></p>";
            jobname = dsInfo.Tables["Jobs"].Rows[ocount]["job_name"].ToString().ToLower();

            for (icount = 0; icount < dsInfo.Tables["JobDetails"].Rows.Count; icount++)
            {
                if (jobname == dsInfo.Tables["JobDetails"].Rows[icount]["job_name"].ToString().ToLower())
                {
                    Label2.Text += "<div class='vacancy-info'><p>" + dsInfo.Tables["JobDetails"].Rows[icount]["body"].ToString() + "</p><br/>" +
                "<span>" + dsInfo.Tables["JobDetails"].Rows[icount]["location"].ToString() + "</span>" +
                    "</div>";
                }
            }
            Label2.Text += "</div>";
        }

Now to make it easier is there a way i can iterate through the sql result and check to see if the body row has been displayed once, if so then don’t display again?

Any ideas how i could do this?

Thanks