Dose anyone know SQL? IÂ’m taking a SQL concepts class using Microsoft Access and I canÂ’t for the life of me figure out one of the problems on this assignment. Any help would be appreciated.

(6 points) For all students, list the student name in the first column. In the second column display 'Yes' if the student has enrolled in any course. Display 'No' in the second column if the student has not enrolled in any courses. Sort the results by student name. NOTE: It is possible for a student to have a NULL GPA even if they have enrolled in a course (they may not have completed the course yet!).

Here is the Database and discription this problem uses.

Anyone? iv been messing with this for over a day and its due in about an hour...yikes.

I can come up with the query if given a little time. If you don't need it before the weekend is over.

i came up with this('.' are just formating place holders):

SELECT stuname, (SELECT 'Yes'
.........................................FROM entroll
.....................................WHERE EXISTS (SELECT stuid
.................................................. .......................FROM enroll
.................................................. ...................WHERE enroll.stuid = student.stuid))

....................................UNION

.................................(SELECT 'No'
......................................FROM entroll
..................................WHERE NOT EXISTS (SELECT stuid
.................................................. .............................FROM enroll
.................................................. .........................WHERE enroll.stuid = student.stuid))
FROM student
ORDER BY stuname;


but it dosnt work. depending on how i do the parenthases around the sub-querie it gives me a few different error messages. I turned the assignment in, but if i could still get some help with it i could turn the on problem in late.

Thanks godfoot

Quote: Originally Posted by Demon Made i came up with this('.' are just formating place holders): SELECT stuname, (SELECT 'Yes' .........................................FROM entroll .....................................WHERE EXISTS (SELECT stuid .................................................. .......................FROM enroll .................................................. ...................WHERE enroll.stuid = student.stuid)) ....................................UNION .................................(SELECT 'No' ......................................FROM entroll ..................................WHERE NOT EXISTS (SELECT stuid .................................................. .............................FROM enroll .................................................. .........................WHERE enroll.stuid = student.stuid)) FROM student ORDER BY stuname; but it dosnt work. depending on how i do the parenthases around the sub-querie it gives me a few different error messages. I turned the assignment in, but if i could still get some help with it i could turn the on problem in late. Thanks godfoot

Well, I admit that my SQL knowledge is limited to what I have learned in the last few months, but what you have looks pretty close except for the text in red having a typo. I am not sure either whether that typo was in the query that you submitted. Only other thing(s) I could think of are potential missing semicolons.

when i run that query i get this error:

"Query input must contain at least one table or query."

I have no idea what this means. Oh, and yeah that misspell was in the query but apparently the assebler didnt get that far in running it to notice.

Quote: Originally Posted by Demon Made when i run that query i get this error: " Query input must contain at least one table or query." I have no idea what this means. Oh, and yeah that misspell was in the query but apparently the assebler didnt get that far in running it to notice.

If the table you are trying to query was misspelled, you will always get that error as it cannot find the table it is looking for since it is misspelled. Have you tried rerunning the query with the proper spelling?

yeah i have and i quoted the error. its the same error as before when the table was misspelled

I'm not sure what to tell you then, as I said my SQL knowledge is limited... sorry.

4 queries within one...damn

i appreciate the help anyway godfoot.

Quote: Originally Posted by CLowN 4 queries within one...damn

yeah i know, and this is the SQL Concepts (begining SQL) class. all we do is cover the SELECT statment.

Here's one that works, but it may use some things the teacher hasn't covered yet (they don't often like when you get too far ahead of 'em ).
In your query, it was giving you that error only because of the parenthases. Here it is with only the parenthases changed (Now the UNION statement is inside the parenthases). If you run this:
then it'll give you a different error message, "Operation is not allowed in subqueries". I think it's talking about the "EXISTS" operation. So if you use the modified version below that takes it out of a subquery, that would work.
And yes, the "ORDER BY" works for the whole statement, even though you'd think it'd only work for the second one. *shrug*

Note: I used "UNION ALL" in the last query instead of just plain "UNION", since it'll run faster for large queries. "UNION" attempts to eliminate any duplicate rows. But since you know there are no duplicates (aren't any No's in the first query or Yes's in the second query), "UNION ALL" will work the same and run faster.

Quote: Originally Posted by FunkyFresh And yes, the "ORDER BY" works for the whole statement, even though you'd think it'd only work for the second one. *shrug* Note: I used "UNION ALL" in the last query instead of just plain "UNION", since it'll run faster for large queries. "UNION" attempts to eliminate any duplicate rows. But since you know there are no duplicates (aren't any No's in the first query or Yes's in the second query), "UNION ALL" will work the same and run faster.

Good idea on the UNION ALL when I was working on a database, I was looking for something a little faster, and this would of helped me out greatly.