• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Some quick SQL help on hard query

 
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi guys,
I am trying to write a query that in one of the where columns there is a default value and it's giving me trouble.
Basically, I narrow a table of records down by a few different fields. One of the fields is a "unittype" field. There's a know set of values in the table, so I set a value to check like unittype = 'S3', but there are also records in the same column like '**' which stands for default.
The table has an "effectivedate" colum, so I set up a where effectivedate > "somedate" and unittype = 'S3'.
My problem is this, what I really want is where unittype = 'S3' or if there is no 'S3' use the '**' record.
Also if there were both an 'S3' and '**' that met the other criteria, I can only have one of the records returned, because this is a subquery. I would want the 'S3' always first and the '**' only if no 'S3' existed that met the effective date requirement.
So I need some help! how to I write the where statement so that it favors the 'S3', but still inserts the '**' record if it can't find 'S3'?
Thanks a million!

A
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
most databases support the 'exists' functionality, so you could do something like this:
select a.unittype from unit a where
( a.unittype = 'S3' OR
( a.unittype = '**' and not exists ( select b.unittype from unit b where b.unittype = 'S3' and effectivedate > somedate ) ) )
AND effectivedate > somedate
something like that anyways
Jamie
 
reply
    Bookmark Topic Watch Topic
  • New Topic