Hi,
I have a table called students
it has 6 fields and 2 of them are
Attendance
Completion
the values for Attendance is Completed or null
the values for Completion is Present or null
I want to find out students where the values for the attendance and completion are null
and also the students where the attendance is Completed and completion is null
and also where the Attendance is null and Completion is Present
can someone tell me how my query will be
todd
Quote:
| Originally Posted by todd2006 Hi,
I have a table called students
it has 6 fields and 2 of them are
Attendance
Completion
the values for Attendance is Completed or null
the values for Completion is Present or null
I want to find out students where the values for the attendance and completion are null
and also the students where the attendance is Completed and completion is null
and also where the Attendance is null and Completion is Present
can someone tell me how my query will be
todd |
you could try a union query
Code:
SELECT 'Both Null' AS Type, * FROM students WHERE attendance IS NULL AND completion IS NULL
UNION
SELECT 'Completed' AS Type, * FROM students WHERE attendance = 'Completed' AND completion IS NULL
UNION
SELECT 'Present' AS Type, * FROM students WHERE completion = 'Present' AND attendance IS NULL
not pretty, but should work.
Was this answer helpful ?
Yes No
mehere,
how will i be able to find the the count for those 3 conditions
todd
Was this answer helpful ?
Yes No
Hey todd... I know the answer, but this feels like homework, so I don't want to do ALL the work for you. First of all, think about how to check for NULL values and you should be able to figure it out.
If not , perhaps I can give you a little push in the right direction.
[EDIT] - I started this reply before mehere replied, but I think all you need is a count function to solve it the rest of the way.
Was this answer helpful ?
Yes No
i get this error mehere
The text, ntext, or image data type cannot be selected as DISTINCT.
the datatype for both the fields are varchar
from the query i want to display the students name and ph number and email mehere
Was this answer helpful ?
Yes No
laura,
this is what i tried earlier before even i posted the post
Code:
select * from students where ((Attendance = '' or Attendance IS null) AND (Completion = '' or Completion IS null)) AND ((Attendance <> '' OR Attendance is not NULL) AND (Completion = '' or Completion IS null)) AND ((Completion <> '' or Completion IS not null) AND (Attendance = '' or Attendance IS null))
but it didnt work
Was this answer helpful ?
Yes No
Hmm... Well I think the union is a better idea because what you have there means that the returned records have to meet ALL of that criteria to be returned. Therefore you won't get anything. Did you try the union statements that mehere suggested?
Or you can get the data as three separate queries....
Was this answer helpful ?
Yes No
laura,
see this is what i want to do
1)i want to tell the admin the count of the students
2)second i want to display those students info
yes i tried mehere query but it gave me an error saying
The text, ntext, or image data type cannot be selected as DISTINCT
Was this answer helpful ?
Yes No
laura and mehere,
to display the info i tweaked mehere's query like this
Code:
SELECT 'Both Null' AS Type, Id, First_Name, Last_Name, Email, Phone, Gender FROM students WHERE attendance IS NULL AND completion IS NULL
UNION
SELECT 'Completed' AS Type, * FROM students WHERE attendance = 'Completed' AND completion IS NULL
UNION
SELECT 'Present' AS Type, * FROM students WHERE completion = 'Present' AND attendance IS NULL
it gives an error saying
All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.
Was this answer helpful ?
Yes No
ok this works out
Code:
SELECT 'Both Null' AS Type, Id, First_Name, Last_Name, Email, Phone, Gender FROM students WHERE attendance IS NULL AND completion IS NULL
UNION
SELECT 'Completed' AS Type, Id, First_Name, Last_Name, Email, Phone, Gender FROM students WHERE attendance = 'Completed' AND completion IS NULL
UNION
SELECT 'Present' AS Type, Id, First_Name, Last_Name, Email, Phone, Gender FROM students WHERE completion = 'Present' AND attendance IS NULL
so it displays the info now
how do i change this query to get the count
todd
Was this answer helpful ?
Yes No
