• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql query for set column pair values

 
tormod eriksen
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i found a solution
select codes, date from codetable where (codes not in ('c', 'g') or date > '19770303')
 
capricorn
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
should be "AND" instead of "or"
 
tormod eriksen
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic