[Last Call] Learn how to a build a cloud-first strategyRegister Now


Sql query doubt

Posted on 2011-10-25
Medium Priority
Last Modified: 2012-06-22
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...
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

Question by:gaugeta
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 37023056
This query fetch those Student records which are present in stuattendence, and not in student table
stuattendence A
LEFT JOIN student S ON A.StuID = S.StuID 
WHERE S.StuID is null

Open in new window

LVL 23

Accepted Solution

Rajkumar Gs earned 2000 total points
ID: 37023109
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.
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

LVL 17

Expert Comment

by:Muhammad Khan
ID: 37023149
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)
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 37023156
@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.

Author Comment

ID: 37023168
@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.
LVL 71

Expert Comment

ID: 37023188
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 *).
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37024052
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.
LVL 35

Expert Comment

ID: 37024405
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.
LVL 32

Expert Comment

ID: 37024860
See attached.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, we’ll look at how to deploy ProxySQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question