Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

which join is required in my query

Posted on 2012-03-23
11
Medium Priority
?
289 Views
Last Modified: 2012-08-13
I have a case table which holds a load of information on cases.

I then  have a load of tables off that table table, all of which have a foreign key linking to this cases table with a CaseID.

In most of my queries I join off to these tables but on this occassion I actually want to say only get me case records that have no record in these other tables so I was presuming I would do a select from my table joining onto all the other tables with a specific join that would basically say only get the records from the left table.

What join would I use for this?
0
Comment
Question by:scm0sml
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 6

Assisted Solution

by:ingriT
ingriT earned 800 total points
ID: 37756433
You can't really use a join for this, you need to select the Id's from the other tables to exclude them.

Check these examples to find the most efficient solution for your case:
http://weblogs.sqlteam.com/peterl/archive/2007/09/20/Finding-records-in-one-table-not-present-in-another-table.aspx
0
 
LVL 6

Accepted Solution

by:
Codebot earned 1200 total points
ID: 37756445
SQL Outer Join

Use the SQL OUTER JOIN whenever multiple tables must be accessed through a SQL SELECT statement and results should be returned if there is not a match between the JOINed tables.

It can be sql "left outer join", "sql right outer join" depends on how you do that.

SELECT Products.ProductNumber,
Products.ProductName
FROM Products LEFT OUTER JOIN Order_Details
ON Products.ProductNumber =
Order_Details.ProductNumber
WHERE Order_Details.OrderNumber IS NULL
0
 

Author Comment

by:scm0sml
ID: 37756459
ok so I have
select *
from clientcase cc
left join clients cl on cl.clientid=cc.clientid
left join authorities a on a.id = cc.PurchasingLocalAuthority
left join adminworkers aw on aw.workerid = cc.AdminWorker
left outer join firstmeetings fm on cc.clientid = fm.ClientID
left outer join trialvisits tv on cc.clientid = tv.ClientID
left outer join overnightvisit ov on cc.clientid = ov.ClientID

I only want to get from cc where it has no matching records in the bottom 3 joins....

But I have ended up with loads of records coming back not less?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 6

Expert Comment

by:ingriT
ID: 37756469
Try this;
select *
from clientcase cc
left join clients cl on cl.clientid=cc.clientid
left join authorities a on a.id = cc.PurchasingLocalAuthority
left join adminworkers aw on aw.workerid = cc.AdminWorker
left join firstmeetings fm on cc.clientid = fm.ClientID
left join trialvisits tv on cc.clientid = tv.ClientID
left join overnightvisit ov on cc.clientid = ov.ClientID
where
fm.clientid is null and
tv.clientid is null and
ov.clientid is null
0
 
LVL 6

Expert Comment

by:Codebot
ID: 37756476
You missed where clause
Use this

where
fm.clientid is null and
tv.clientid is null and
ov.clientid is null

or

where
fm.clientid is null or
tv.clientid is null or
ov.clientid is null
0
 

Author Comment

by:scm0sml
ID: 37756483
Hi both,

Right then I got it working correctly with the example(s) in the link provided and also using left outer join and using the where clasue. Gave same results.

Which is more efficient?
0
 

Author Comment

by:scm0sml
ID: 37756484
Just to clairfy the example in the link was using this type of idea:

WHERE NOT EXISTS (SELECT * FROM proposedcarertoclient pcc WHERE cc.clientid = pcc.ClientID) and ...
0
 
LVL 6

Expert Comment

by:ingriT
ID: 37756489
I think you should test that with your own set of data. Do you have SQL Server Management Studio? You can check the execution plans there, and just test with the execution times as well to see which is more efficient with your tables.
0
 
LVL 6

Expert Comment

by:Codebot
ID: 37756490
Joins are always Best and efficient and easy to understand and update
0
 
LVL 6

Expert Comment

by:ingriT
ID: 37756497
Joins are always Best and efficient and easy to understand and update

When using joins 'both ways' in one query (to check if it does exist on some tables and doesn't on others), I don't really agree it is easy to understand and update. It takes away some of the overview, you do a join first and then exclude the records, that does not seem the most logical way to me.
0
 

Author Closing Comment

by:scm0sml
ID: 37756511
Thanks for your help both.

I'll try and post which was the quickest.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

715 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