Sql query doubt

Hi Experts...
I have two tables student and stuattendence.
The student table is the parent table.
Here the task is to check whether all the student id's present in the stuattendence table is present in the student table as well.
If not then the id not present in the student table should be collected.
Here i have a query for the same but which does in the work in subqueries.
How do i use any sort of join to to the same task efficiently.
Please help...
Query:
select stuID
from dbo.stuattendence
where attendeddate='10/23/2011'
and dbo.stuattendence.stuID not in (select stuID
from dbo.student)
group by stuID

Open in new window

gaugetaAsked:
Who is Participating?
 
Rajkumar GsSoftware EngineerCommented:
But can you please explain why you are trying to get this data. If you are maintaining proper foreign key constraint, there would be no such data, correct ?

This is the updated query including attendancedate as well.
SELECT * FROM
stuattendence A
LEFT JOIN student S ON A.StuID = S.StuID 
WHERE S.StuID is null and A.attendeddate='10/23/2011'

Open in new window


Raj
0
 
Rajkumar GsSoftware EngineerCommented:
This query fetch those Student records which are present in stuattendence, and not in student table
SELECT A.* FROM
stuattendence A
LEFT JOIN student S ON A.StuID = S.StuID 
WHERE S.StuID is null

Open in new window


Regards
0
 
Muhammad KhanManager, ITCommented:
Using NOT EXISTS is faster

select distinct stuID
from dbo.stuattendence sa
where attendeddate='10/23/2011'
and not exists (select 1 from dbo.student s where s.stuID=sa.stuID)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gaugetaAuthor Commented:
@RajkumarGS:Thanks for replying.
It worked like a charm.
Can you explain how this query does the same job as the above sub query.
If you can compare with the sub query and brief it would help a lot.
0
 
gaugetaAuthor Commented:
@RajkumarGS:The database is updated by scripts which refer many tables.
Just to check if all data in database is proper this check is done.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
NOT IN is usually resolved badly performancewise, as long as there are many records found in the subquery. It is almost always better to use a NOT EXISTS:
select stuID
from dbo.stuattendence sa
where attendeddate='10/23/2011'
and not exists (select * from student s where s.StuID = sa.StuID)

Open in new window

Doing that will still use a subquery (which is the traditional way to solve such quests). A join does not have any advantages over that.

BTW, the GROUP BY is superfluous, just use DISTINCT instead. And the solution presented above with LEFT JOIN will result in multiple records if a non-registered student has more than one attendence, and hence needs some modification (DISTINCT StuID instead of *).
0
 
slightwv (䄆 Netminder) Commented:
This was posted in Sybase Database, Oracle Database, Databases Miscellaneous

Whenever I see dbo I think SQL Server.

Please clarify what database this is for.
0
 
johnsoneSenior Oracle DBACommented:
SQL Server's origin is Sybase, so it would not be surprising to see dbo with Sybase as a zone.

I also see dbo a lot in applications that were ported from Sybase or SQL Server to Oracle.
0
 
awking00Commented:
See attached.
query.txt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.