Improve my SQL Select statement to select students who have not fully completed a section

Tag: sql,sql-server,linq,tsql

My SQL skills are quite limited. I'm in my second year of computer science at a technical college. I'm building a windows forms application that will allow the BAS director at my college to keep track of students and their progress throughout the courses. I have complete control over the database design so if you think of a way to help me reach a solution that would involve tweaking the database that is a possibility.

I'm trying to select all of the Students who do not have an EnrollmentStatus of 3 in all of the Courses that have a CreditSection of 1. There are 12 courses with a CreditSection of 1

The tables I'm using look like this: Schema

I can think of a few ways to get my solution in speech, but can't seem to write them SQL:

SELECT * FROM Students WHERE each student has 12 entries in CourseEnrollment AND
CourseEnrollment.EnrollmentStatus = 3 AND Courses.CreditSection = 1

SELECT * FROM Students WHERE Courses.CourseID 1 thru 12 EXIST in
CourseEnrollment for each student AND CourseEnrollment.EnrollmentStatus = 3

I can get to the desired solution using this mess below, but as I check for students who have completed 4 years worth of courses... this query becomes ridiculously long and probably mega resource consuming.

This query selects students who are not in the list of students who have completed one or more the given courses:

SELECT DISTINCT s.* FROM Students s
WHERE s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 1 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 2 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 3 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 4 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 5 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 6 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 7 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 8 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 9 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 10 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 11 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 12 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 13 AND ce.EnrollmentStatus = 3)

My goal is to figure out how to write this query in SQL and then convert it to LINQ which is ultimately what I need. If anyone can help with either part of this I would be grateful.

I've converted the above to LINQ and it looks just as horrendous:

var query =
                from student in datStudents.Students.AsEnumerable<dsStudentManager.StudentsRow>()
                where !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 1 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 2 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 3 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 4 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 5 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 6 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 7 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 8 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 9 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 10 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 11 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 12 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 13 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID)
                select new
                {
                    id = student.StudentID,
                    rtcid = student.RTCStudentID,
                    firstname = student.FirstName,
                    lastname = student.LastName,
                    phone = student.Phone,
                    studentemail = student.StudentEmail,
                    personalemail = student.PersonalEmail,
                    address = student.Address,
                    city = student.City,
                    state = student.State,
                    zip = student.Zip,
                    birthdate = student.BirthDate,
                    gender = student.Gender,
                    notes = student.Notes,
                    studentdocumentslocation = student.StudentDocumentsLocation
                };

Best How To :

Using a subquery with a group by and having statement you can come up with something similar to this:

 SELECT * FROM Students WHERE StudentID NOT IN (
      SELECT s.StudentID FROM Students s 
        JOIN CourseEnrollment ce ON s.StudentID = ce.StudentID
        JOIN Courses c ON ce.CourseID = c.CourseID
        WHERE ce.EnrollmentStatus = 3 AND c.CreditSection = 1
        GROUP BY s.StudentID
        HAVING COUNT(*) = 12
      )

The inner query builds the criteria for students to return, and the "HAVING COUNT(*) = 12" gets you students that match 12 courses. If you only a subset of courses, you can also try the following.

 SELECT * FROM Students WHERE StudentID NOT IN (
      SELECT s.StudentID FROM Students s 
        JOIN CourseEnrollment ce ON s.StudentID = ce.StudentID
        JOIN Courses c ON ce.CourseID = c.CourseID
        WHERE ce.EnrollmentStatus = 3 AND c.CreditSection = 1
        AND c.CourseID IN (1,2,3,4,5,6,7,8)
        GROUP BY s.StudentID
        HAVING COUNT(*) = 8 -- Number of courses in the ID in clause
      )

Hope this helps you get on track.

Using Sum in If in Mysql

mysql,sql,select,sum

Using least would be much easier: SELECT LEAST(SUM(my_field), 86400) FROM my_table ...

SQL Server / C# : Filter for System.Date - results only entries at 00:00:00

c#,asp.net,sql-server,date,gridview-sorting

What happens if you change all of the filters to use 'LIKE': if (DropDownList1.SelectedValue.ToString().Equals("Start")) { FilterExpression = string.Format("Start LIKE '{0}%'", TextBox1.Text); } Then, you're not matching against an exact date (at midnight), but matching any date-times which start with that date. Update Or perhaps you could try this... if (DropDownList1.SelectedValue.ToString().Equals("Start"))...

Title search in SQL With replacement of noice words [on hold]

sql,sql-server,sql-server-2008

I think you want something like this: DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) ) INSERT INTO @nw VALUES ( 1, 'and' ), ( 2, 'on' ), ( 3, 'of' ), ( 4, 'the' ), ( 5, 'view' ) DECLARE @s VARCHAR(100) = 'view This of is the Man';...

SQL Customized search with special characters

sql,sql-server,sql-server-2008

Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT...

Setting time limit in SQL Query

sql-server

Assuming that you want between 10:00 AM and 5:00 PM, you can use this SELECT CASE WHEN CAST(GETDATE() AS TIME) BETWEEN '10:00:00' AND '17:00:00' THEN 1 ELSE 0 END In this context, Select * from table makes no sense, unless you have a time column and want to evaluate that....

Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql

I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...

SQL: overcoming no ORDER BY in nested query

sql,sqlite

Use a join instead: SELECT a, b FROM t JOIN (SELECT DISTINCT date FROM t ORDER BY date DESC LIMIT 2) tt on t.date = tt.date; ...

Join SQL query Results and Get-ChildItem Results

sql-server,sql-server-2008,powershell

OK so if the SQL query does not have results then NULL is returned and, in essence, nothing is added to the $dbResults array. Instead lets append the results to a custom object. I don't know what PowerShell version you have so I needed to do something that I know...

INSERT INTO fails due to incorrect conversion T-SQL

sql-server,tsql

The problem is that there's no implicit conversion from varchar (your literal) to sql_variant. Just add an explicit conversion and you're done: cast('FooBar' as sql_variant) ...

Error connecting to MSSQL using PHP

php,sql-server,pdo,odbc,sqlsrv

Change it to: $this->link = new PDO( "sqlsrv:Server={$this->serverName},{$this->port};Database={$this->db};", $this->uid, $this->pwd ); The default SQL Server port is 1433. Note the curly brackets, they allow for class variables....

Default the year based on month value

sql,sql-server

SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return "2016" Your distinct only works on the first select you've done so these are your options: 1) Use cte's with distincts with subq1 (syear, eyear,...

Matplotlib: Plot the result of an SQL query

python,sql,matplotlib,plot

Take this for a starter code : import numpy as np import matplotlib.pyplot as plt from sqlalchemy import create_engine import _mssql fig = plt.figure() ax = fig.add_subplot(111) engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') ## the data data =...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

sql,sql-server,sql-server-2008

You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (...

T-SQL Ordering a Recursive Query - Parent/Child Structure

sql,tsql,recursion,order,hierarchy

The easiest way would be to pad the keys to a fixed length. e.g. 038,007 will be ordered before 038,012 But the padding length would have to be safe for the largest taskid. Although you could keep your path trimmed for readability and create an extra padded field for sorting....

TSQL - Error in stored procedure due to conversion failure

sql-server,sql-server-2008,tsql

I'd suggest doing it like that: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE INNER JOIN...

SQL Group By multiple categories

php,mysql,sql,mysqli

Just include a case statement for the group by expression: SELECT (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3' ELSE Categories.name END) as name, sum(locations.name = 'loc 1' ) as Location1, sum(locations.name = 'loc 2') as Location2, sum(locations.name = 'loc 3') as Location3, count(*) as total FROM ... GROUP BY (CASE...

Pull information from SQL database and getting login errors

php,sql,database

change $username = "'rylshiel_order"; to $username = "rylshiel_order"; and you should be through. You are passing on an extra single quote here. ...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012

Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...

Query how often an event occurred at a given time

mysql,sql

This could be done using user defined variable which is faster as already mentioned in the previous answer. This needs creating incremental variable for each group depending on some ordering. And from the given data set its user and date. Here how you can achieve it select user, date, purchase_count...

Select Statement on Two different views

sql

Yes, You can use two different view in SELECT query. You have to JOIN them, if them have matched column in each other. Just treat two different views as like two different tables when using in SELECT Clause. SELECT vw1.a, vw2.b FROM View1 vw1 INNER JOIN View2 vw2 ON vw1.id...

Convert AWK command to sqlite query

sql,awk,sqlite3

SQLite is an embedded database, i.e., it is designed to be used together with a 'real' programming language. It might be possible to import that log file into a database file, but the whole point of having a database is to store the data, which is neither a direct goal...

ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables

sql-server,join

If I understood correctly this should be what you're looking for SELECT A.UserName, A.[Email ID], A.[Supervisor Email ID] FROM A INNER JOIN B ON A.UserCode = B.UserCode WHERE B.ACTIVE_FLAG = 'Y' ...

Foreign key in C#

c#,sql,sql-server,database

You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx...

Sql injected code is inserted to my database . How to remove it

sql-server

You can use the fact that html code starts with symbol <. Then: UPDATE TableName SET SomeColumn = CASE WHEN CHARINDEX('<', SomeColumn) > 0 THEN SUBSTRING(SomeColumn, 1, CHARINDEX('<', SomeColumn) - 1) ELSE SomeColumn END If this is not true then we will need more information about data. May be it...

How to use OFFSET and Fetch without Order by in SQL Server

sql-server,sql-server-2012,sql-order-by,fetch,offset

By adding an identity column to the temp table variable declare @TempTable table([some columns], rownr int identity(1,1) ) INSERT INTO @TempTable [some columns] select [some columns] from table1 order by col1 INSERT INTO @TempTable [same columns] select [some columns] from table2 order by col2 An automatic incrementing number is added...

Implement reference key in SQL Server

sql-server,sql-server-2008

It's called a "one-to-zero-or-one" relationship, as one Line might be associated to zero or one TestPacks. You can implement it by using a FK that allows NULL values. CREATE TABLE TestPack (id INT, PRIMARY KEY (id)) CREATE TABLE Line (id INT, TestPackId INT NULL, FOREIGN KEY (TestPackId) REFERENCES TestPack(id)) By...

Retrieve Values As Column

mysql,sql

If types are fixed (just IMPRESSION and CLICK), you could use a query like this: SELECT headline, SUM(tracking_type='IMPRESSION') AS impressions, SUM(tracking_type='CLICK') AS clicks FROM tracking GROUP BY headline ...

Take thousand value in SQL

sql,sql-server

SELECT CONVERT(INT,YourColumn) % 1000 FROM dbo.YourTable ...

Add 1 to datediff result if time pass 14:30 or 2:30 PM

sql,ms-access,ms-access-2007

Could be: SELECT reservations.customerid, DateDiff("d",reservations.checkin_date, Date()) + Abs(DateDiff("s", #14:30#, Time()) > 0)AS Due_nights FROM reservations ...

How to select next row after select in SQL Server?

sql,sql-server

The query can be written as: ; WITH Base AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Shift_Date) RN FROM #Table1 ) , WithC AS ( SELECT * FROM Base WHERE Shift2 = 'C' ) SELECT * FROM WithC UNION SELECT WithCNext.* FROM WithC C LEFT JOIN Base WithCNext ON...

MySQL: Select several rows based on several keys on a given column

mysql,sql,database

If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2 ...

oracle sql error Case When Then Else

sql,oracle,oracle11g

Perhaps this is what you want? If there are rows in SecondTable, then do the second EXISTS: SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND (NOT EXISTS (SELECT 1 from SecondTable) OR EXISTS (SELECT 1 FROM SecondTable WHERE FirstTable.Key = SecondTable.Key and SecondTable.RowProcessed = 'Y')) AND OtherConditions ...

Cannot Browse in sql to backup bak file

sql-server

Try this query RESTORE DATABASE Databasename FROM DISK = 'Z:\SQLServerBackups\tms.bak' ; ...

mysql_real_escape_string creates \ in server only not in local

php,sql

Your server has magic quotes enabled and your local server not. Remove it with the following sentence set_magic_quotes_runtime(0) As this function is deprecated and it will be deleted in PHP 7.0, I recommend you to change your php.ini with the following sentencies: magic_quotes_gpc = Off magic_quotes_runtime = Off If you...

Get unique row by single column where duplicates exist

sql,sql-server

SELECT MIN(date),thread_id FROM messages GROUP BY thread_id HAVING COUNT(thread_id) > 1 ...

SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like

WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile...

SQL varchar variable inserts question mark

sql,sql-server-2012

there is un recognizable character in your string that is giving that ?. Delete the value and retype. see my above screen shot...

left join table, find both null and match value

sql,sql-server,join

Try FULL OUTER JOIN. This is the sqlfiddle. It will produce the op you are expecting SQLFiddle select t1.years, t1.numOfppl, t2.years, t2.numOfppl from t1 full outer join t2 on t1.years=t2.years ...