Select Subquery Filtering out problem

[Spazasaurus]

I am having trouble. I am not sure if it is not possible or not, but don't know any alternatives.

I am converting my site from PHP and MYSQL to ASP.NET and MSSQL. In my current site. I did a query and then did another query on each record while looping through those results to get what I am trying to do in one query now. I would simply do that same thing but I am not sure how while using Visual Studio and I know that it was bad code, so thought I would fix it.

Here is my current setup:

event table definition in MySQL
+-----------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-------------+------+-----+---------+----------------+
| ev_id | smallint(6) | | PRI | NULL | auto_increment |
| st_id | smallint(6) | YES | | NULL | |
| re_id | smallint(6) | YES | | NULL | |
| ev_name | varchar(50) | | | | |
| ev_description | text | YES | | NULL | |
| ev_date | date | YES | | NULL | |
| ev_time | time | YES | | NULL | |
| lo_id | smallint(6) | YES | | NULL | |
| lc_id | smallint(6) | YES | | NULL | |
| ev_duration | varchar(10) | YES | | NULL | |
| ev_adultsattended | smallint(4) | YES | | NULL | |
| ev_youthattended | smallint(4) | YES | | NULL | |
| ev_staffattended | smallint(4) | YES | | NULL | |
| ev_residentsattended | smallint(4) | YES | | NULL | |
| ev_volunteersattended | smallint(4) | YES | | NULL | |
+-----------------------+-------------+------+-----+---------+----------------+

eventrating table definition in MySQL
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| ev_id | smallint(6) | | PRI | 0 | |
| st_id | smallint(6) | | PRI | 0 | |
| rlc_id | smallint(6) | YES | | NULL | |
| er_comment | text | YES | | NULL | |
| er_famrating | char(1) | YES | | NULL | |
| er_staffrating | char(1) | YES | | NULL | |
| er_resrating | char(1) | YES | | NULL | |
| er_volrating | char(1) | YES | | NULL | |
| er_repeat | varchar(5) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+

PHP queries to get the data and place in a dropdown box
*** I am using a formcreator class that I built so the code will look a little different, but should be easy enough to follow ***

[PHP]$query = "select DISTINCT e.ev_id, e.ev_name, e.ev_date, s.st_username, s.st_id from event e, staff s, resource r where (e.ev_date >= '$start_date' and e.ev_date <= '$end_date' and s.st_id = e.st_id and r.re_id = e.re_id) ";//and (er.st_id != 1)";// and ".$user_id." <> ANY (select er.st_id from eventrating er) order by e.ev_name";
$result = mysql_query($query);
$select_event_array = array ();
$select_event_count = 0;
if (mysql_num_rows($result))
{
$select_event_array[$select_event_count] = array("label" => "", "value" => "^");
$select_event_count++;
while ($row = mysql_fetch_array($result))
{
$query2 = "select * from eventrating er where er.ev_id = ".$row[0]." and er.st_id = $user_id";
$result2 = mysql_query($query2);
if (mysql_num_rows($result2) == 0)
{
$select_event_array[$select_event_count] = array("label" => $row[1], "value" => $row[0]);
$select_event_count++;
}
}
}
else
{
$select_event_array[$select_event_count] = array("label" => "No Events", "value" => "^");
}
if ($select_event_count < 2)
{
$select_event_array[$select_event_count - 1] = array("label" => "No Events", "value" => "^");
}[/PHP]

So I am trying to do this in mssql. I have converted the tables over and here are the defenitions that I am currently using.
events table
ev_id | smallint <- primary
st_id | smallint
re_id | smallint
ev_name | varchar(50)
ev_description | varchar(489)
ev_date | datetime
ev_time | varchar(12)
lo_id | smallint
lc_id | smallint
ev_duration | varchar(10)
ev_adultsattended | smallint
ev_youtattented | smallint
ev_staffattended | smallint
ev_residentsattended | smallint
ev_volunteersattended | smallint

eventrating table
ev_id | smallint <- primary
st_id | smallint <- primary
rlc_id | smallint
er_comment | varchar(291)
er_famrating | varchar(1)
er_famrating | varchar(1)
er_staffrating | varchar(1)
er_resrating | varchar(1)
er_volrating | varchar(1)
er_repeat | varchar(5)

The staff id in the event table is for the staff member responsible for the event, while the staff id in the eventrating table is for the staff member who is rating the event. There can be many ratings on each event by staff members, but each staff member can only rate each event once.

So, I am try to show all events within a certain date range that have not been rated by the current staff member logged in.

I may not have my database normalized very well and would take suggestions toward getting it in shape as well. I have a lot of limitations based on the fact that I am going to be importing the current data into the new database and don't want to have to convert data as well as much as possible.

Sorry if I have shared too much!

Thanks!

[almaz]

Why do you use resource table in your first query? Can the events.st_id field have NULL values?
I removed resource table as it looks like not used, and assume that events.st_id cannot have NULL values.
The following script should do the right thing:

select  e.ev_id, e.ev_name, e.ev_date, s.st_username, s.st_id
from    events e inner join staff s on e.st_id = s.st_id
where   e.ev_date between @start_date and @end_date 
    and e.ev_id not in (select ev_id from eventrating where er.st_id = @user_id)

[Spazasaurus]

First off, congratulations on being brilliant!

Thank you so much!

Yes, you are right. I didn't need the resource table linked in there at all and am at a miss as to why I had it in there in the first place. Perhaps I should see if my original code is working as I expected it to.

I did get an error with the code you gave, but it was only because referred to an alias (er.st_id) where ther wasn't one, so that was easy to fix.

This is what I ended up with for those who may need it.

SELECT     e.ev_id, e.ev_name, e.ev_date, s.st_username, s.st_id
FROM         event AS e INNER JOIN
                      staff AS s ON e.st_id = s.st_id
WHERE     (e.ev_date BETWEEN @start_date AND @end_date) AND (e.ev_id NOT IN
                          (SELECT     ev_id
                            FROM          eventrating
                            WHERE      (st_id = @user_id)))

Thank you again almaz. I was actually really close, but I was trying ot link the not in on the event id and the staff id, which would return only the one that had been rated by the staff member or nothing at all if neither were true. Somtimes the brain wanders!