Hanged on some of the querries do help......

Question

Hanged on some of the querries do help......

""Sameer""

Old Hand

Points: 302
More actions
March 20, 2009 at 9:42 am

#95116

Hi,
How to querry a table that will give output like
1.Atleast one from this and atleast one from other.....
eg.1.Find the name of each student who has taken or is taking at least one CS course and one math course.
2.Find the name of every student who has taken (or is taking) at least one course taken (including being taken) by student .
Just let me know how to querry for atleast A and B.......

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Please provide table creation scripts, some relevant easily-consumed sample data and a table showing your expected output. If you're not sure how to do this, read the link below.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

""Sameer"" (3/20/2009)
Hi,
How to querry a table that will give output like
1.Atleast one from this and atleast one from other.....
eg.1.Find the name of each student who has taken or is taking at least one CS course and one math course.
2.Find the name of every student who has taken (or is taking) at least one course taken (including being taken) by student .
Just let me know how to querry for atleast A and B.......

Two derived tables, one for distinct names who have a CS and one for distinct names who have a Math... they would be inner joined to each other to produce the final names.

Or, you can do like Chris suggested above and get a better answer quicker.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

""Sameer"" Old Hand Points: 302 More actions · Answer 3

well this is some of the description of table can u help on this.........

The following tables will be used in this project:

students(sid, sname, status, gpa, email, deptname)

courses(dept_code, course#, title, credits)

classes(cid, dept_code, course#, sect#, year, semester, limit, class_size)

enrollments(sid, cid, lgrade)

where sid is the student id and is the primary key of students; (dept_code, course#) form the primary key of courses; cid is the class id and is the primary key of classes; (sid, cid) form the primary key of enrollments. As a simplification, each student is allowed to be associated with one department. As a general clarification, we assume that no student takes the same course (including different sections of the same course) more than once. If you have questions about these tables, please contact the instructor for clarification.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

""Sameer"" (3/21/2009)
If you have questions about these tables, please contact the instructor for clarification.

Please contact the instructor? Are you asking us to do your homework for you?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass