Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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?
0
scm0sml
Asked:
scm0sml
  • 4
  • 4
  • 3
2 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now