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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
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

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?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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

I'll try and post which was the quickest.
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.