Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 14-10-2009
Member
 
Join Date: Jun 2009
Posts: 92
Exclude results in SQL script

I am little worried about sql script that I can not solve and so I'm asking for your help. Here is my situation: I have 3 tables: a customer table, resource table and a session table.

In the session table, I have list of resources for some customers.

My goal is to display all the resources of a client who does not includes (thus excluding them) in the session table.

In my session table I have a column that is named IDRES that refers to resources that are contained in the resource table (idresso).

Here is what I tried to exclude myself but it's not data that includes in the session table. In my application I included this constraint:

Code:
select codebar, quantity, ... from ressource, session, ...
where ...
and a.ressource.idresso <> a.session.idres
this constraint does not remove the display resources contained in the session table to display. But if I replace <> by = it gives me a good one that has the same id. I see too how to solve this problem.
Reply With Quote
  #2  
Old 14-10-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: Exclude results in SQL script

Code:
SELECT fields FROM ressource WHERE ressource.idresso NOT IN (SELECT idres FROM session)
You can even use left outer join as follows:

Code:
SELECT fields 
FROM ressource LEFT OUTER JOIN session 
ON (ressource.idresso = session.idres)
WHERE session.idres IS NULL;
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #3  
Old 14-10-2009
Member
 
Join Date: Jun 2009
Posts: 92
Re: Exclude results in SQL script

Thank you for your help. It worked. One more thing, which is "the cleaner": outer join or simply not in? In any case I thank you for your help.
Reply With Quote
  #4  
Old 14-10-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: Exclude results in SQL script

Outer Join is much cleaner, and probably more efficient. However it depends what you mean by "clean" (ie the version with sub-query is more readable for those unfamiliar), but more efficient chances.
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #5  
Old 14-10-2009
Member
 
Join Date: Nov 2008
Posts: 996
Re: Exclude results in SQL script

More efficient for sure if using an index! The tuning guide of oracle (O'Reilly) explains this easily:

Quote:
Remember that indexes can tell you what is in a table but not what is not in a table. All references to NOT, !=, and <> disable index usage.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Exclude results in SQL script"
Thread Thread Starter Forum Replies Last Post
Does it is safe to exclude sandboxie via firewall umaymah Networking & Security 5 02-01-2011 11:57 PM
Missing Cpbackup exclude.conf. Kaalicharan Operating Systems 3 16-06-2010 06:46 AM
Script to Ping and write results to file, then check services becky Windows Server Help 4 10-05-2010 02:07 PM
Exclude profile folders from roaming... Jake Windows Server Help 3 29-01-2009 12:44 AM
exclude system & hidden directories skipro Windows Software 2 15-10-2008 01:49 PM


All times are GMT +5.5. The time now is 08:16 PM.