*
The moose likes JDBC and the fly likes Help in SQL Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help in SQL Query" Watch "Help in SQL Query" New topic
Author

Help in SQL Query

Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

Hi Ranchers,

There are two tables tableA and tableB and I need a complement of tableA with tableB , I mean, I want to return all the rows which are absents in tableB, like




Here "tableA COMPLEMENT tableB" results into following rows,



My first try,

Its working , but I'm not sure whether its good or not !
So any alternatives??


[LEARNING bLOG] | [Freelance Web Designer] | [and "Rohan" is part of my surname]
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2497
    
    8

You are close. Better not concatenate. That is messy.

There are several options:



or, in oracle:


OCUP UML fundamental and ITIL foundation
youtube channel
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

Ok, thanks, we are concentrating more on first option as we are using MS SQL SERVER.
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
I think operations like "Union", "Minus", "Intersect" are standard Relational Operations that all databases should adhere to.
not sure why SQL Server doesn't support this.


Rahul Babbar
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

Rahul Babbar wrote:I think operations like "Union", "Minus", "Intersect" are standard Relational Operations that all databases should adhere to.
not sure why SQL Server doesn't support this.


Yes, we checked out this basic set operation first, But SQL SERVER dosen't supports MINUS (Yeh, Its support UNION and INTERSECTION). Actually there in no keyword like, minus. And interestingly doesn't have CONCAT operator (Query is constructed on MySQL first) too.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30392
    
150

Some database use an alternate keyword for "minus." For example, db2 uses "except." I did check online and SQL Server doesn't have one. Which means this post isn't so much for you as it is for the db2 user who stumbles across this thread.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help in SQL Query