This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes sql query for set column pair values Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "sql query for set column pair values" Watch "sql query for set column pair values" New topic
Author

sql query for set column pair values

tormod eriksen
Ranch Hand

Joined: Jan 23, 2002
Posts: 52
hi
i am to get a resultset based on values in two columns.
the columns:
codes, char(1) - holds various characters.
date, datetime - holds various dates.
i am to get all rows except the ones that have codes 'c' and 'g' and also have a date older than march 3rd 1977. (i am to get the rows that have codes 'c' and 'g', and a date that is march 3rd 1977 or after.)
hints would be fine
tormod eriksen
Ranch Hand

Joined: Jan 23, 2002
Posts: 52
i found a solution
select codes, date from codetable where (codes not in ('c', 'g') or date > '19770303')
capricorn
Greenhorn

Joined: May 30, 2002
Posts: 2
should be "AND" instead of "or"
tormod eriksen
Ranch Hand

Joined: Jan 23, 2002
Posts: 52
if i set 'and' instead of 'or', won't that give results with dates from march 3rd 1977 onwards only, for all codes (not only 'c' and 'g') ?
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by tormod eriksen:
if i set 'and' instead of 'or', won't that give results with dates from march 3rd 1977 onwards only, for all codes (not only 'c' and 'g') ?

Just the opposite. Your current query ( with OR ) will not only omit all rows with a 'c' or a 'g' but will also omit omit rows with a date > '19770303'. If you want to omit the rows with either a 'c' or a 'g' as well as a date > '19770303' then you must use AND.
Jamie
[ May 30, 2002: Message edited by: Jamie Robertson ]
 
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link: http://aspose.com
 
subject: sql query for set column pair values
 
jQuery in Action, 3rd edition