which join is required in my query

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?
scm0smlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ingriTCommented:
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
CodebotCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
scm0smlAuthor Commented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

ingriTCommented:
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
CodebotCommented:
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
scm0smlAuthor Commented:
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
scm0smlAuthor Commented:
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
ingriTCommented:
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
CodebotCommented:
Joins are always Best and efficient and easy to understand and update
0
ingriTCommented:
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
scm0smlAuthor Commented:
Thanks for your help both.

I'll try and post which was the quickest.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

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.