wood burning stoves*
The moose likes JDBC 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
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 agree. Here's the link: http://aspose.com/file-tools
 
subject: sql query for set column pair values