C# SQL question

.NET
1

Hey everyone,

I am VERY stuck with a problem and don’t know how to fix…

Basically i have a job application process where users apply for a job, in the admin section administrators want to be able to see which applications have not been viewed. Now i have one page which lists all the job vacancies and then users can click into that specific vacancy to see which people have submitted an application. This all works fine.

However on the page i display the job vacancies i need to make it so that if there are any applications within a vacancy that need viewing it appears in RED.

The two tables i am using are below:

tbl_vacancies
job_id
job_name
location
voucher
details
availability
region
date_added

tbl_applications
application_id
job_id
fname
sname
other_info
.
.
.
.
date_addedd
viewed

Now the viewed column is in tbl_applications table, the applications relate to a specific vacancy via the job_id.

So what i need to check is IF there are ANY applications where the viewed = 0, if so that means that there are applications that need to be seen, and i want this to appear in RED.

Now i tried this several ways i.e an INNER JOIN etc… but the full list of vacancies won’t appearing.

So i tried this:


            string sqlStr = "SELECT tbl_vacancies.job_id, tbl_vacancies.job_name, " +
                "tbl_vacancies.location, tbl_vacancies.voucher, tbl_vacancies.details, " +
                "tbl_vacancies.availability, tbl_vacancies.region, " +
                "(SELECT tbl_applications.viewed FROM tbl_applications WHERE tbl_applications.ID = tbl_vacancies.job_id) AS VIEWED " +
                "FROM tbl_vacancies WHERE tbl_vacancies.availability != 'Archived'";

And then to check i did this:


                if (dbReader["VIEWED"] == "0")
                {
                    Label5.Text += "<span style=color:red>You have unread applications!</span>";
                }

But it doesn’t work at all. I don’t get any errors and the applications all display fine, but i do the IF and try to check to see if any of the applications have unread applications, it doesn’t work…

Can anyone please help me with this?

Thanks again

2 
SELECT vac.job_id
     , vac.job_name
     , vac.location
     , vac.voucher
     , vac.details
     , vac.availability
     , vac.region 
     , MAX(app.viewed) AS viewed    
  FROM tbl_vacancies AS vac
LEFT OUTER
  JOIN tbl_applications AS app
    ON app.ID = vac.job_id
   AND app.viewed = 0
 WHERE vac.availability <> 'Archived'
GROUP
    BY vac.job_id

holler if you don’t understand how this works :slight_smile:

3

Ah sorry,

I figured it out and actually your method seems better :rolleyes: I did it this way:


        string sqlStr = "SELECT tbl_vacancies.job_id, tbl_vacancies.job_name, " +   
                 "tbl_vacancies.location, tbl_vacancies.voucher, tbl_vacancies.details, " +   
                 "tbl_vacancies.availability, tbl_vacancies.region, " +   
                 "(SELECT COUNT(*) FROM tbl_applications WHERE tbl_applications.ID = tbl_vacancies.job_id and viewed = 0) AS VIEWED " +   
                 "FROM tbl_vacancies WHERE tbl_vacancies.availability != 'Archived'";

But i know you may tell me my method is not strong enough, so i will try using yours :wink:

thanks

4

the only thing i don’t like about your method is the crappy formatting of your query :smiley:

5

Human readability is worth the little bit of extra time it takes you to write it better and it won’t slow the engines down significantly.

I think you’ll agree that even with this, and it could still be improved, it’s much easier to see at a glance what’s going on, no?

string sqlStr = "SELECT tbl_vacancies.job_id
			, tbl_vacancies.job_name
			, " + "tbl_vacancies.location
			, tbl_vacancies.voucher
			, tbl_vacancies.details
			, " + "tbl_vacancies.availability
			, tbl_vacancies.region
			, " + "(SELECT COUNT(*) 
		FROM tbl_applications 
		WHERE tbl_applications.ID = tbl_vacancies.job_id and viewed = 0) 
		AS VIEWED " + "
		FROM tbl_vacancies 
		WHERE tbl_vacancies.availability != 'Archived'
		";
6

I agree! Writing the code in a readable way is good because:

  1. It makes it easier to read and therefore fix.
  2. You may have to go back to the code at a later date and change it.
  3. More importantly someone else may have to go back to the code at a later date and change it. They may change it so it resembles the way the code should have looked originally.