Solved

which join is required in my query

Posted on 2012-03-23
11
283 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
  • 4
  • 4
  • 3
11 Comments
 
LVL 6

Assisted Solution

by:ingriT
ingriT earned 200 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 300 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now