User Tools

Site Tools


podoc:view_lens_type_sql_view

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
podoc:view_lens_type_sql_view [2007/04/13 09:10] Luudpodoc:view_lens_type_sql_view [2007/10/19 20:13] (current) Luud
Line 11: Line 11:
 === Schema === === Schema ===
  
-  create view view_lens_type as +<code sql> 
-    select lens_type.identifier, manufacturer.name, model, variation, s_min_aperture, s_max_aperture, s_min_focal_length, s_max_focal_length, manufacturer.url +create view view_lens_type as select lens_type.identifier, manufacturer.name, 
-      from lens_type, manufacturer, +        model, variation, s_min_aperture, s_max_aperture, 
-           (select identifier as identifier, value as s_min_focal_length from focal_length) as t_min_focal_length, +        s_min_focal_length, s_max_focal_length, manufacturer.url, 
-           (select identifier as identifier, value as s_max_focal_length from focal_length) as t_max_focal_length, +        exiftool_lens_id 
-           (select identifier as identifier, value as s_min_aperture from aperture) as t_min_aperture, +    from lens_type, manufacturer, 
-           (select identifier as identifier, value as s_max_aperture from aperture) as t_max_aperture +         (select identifier as identifier, value as s_min_focal_length 
-     where lens_type.manufacturer = manufacturer.identifier +        from focal_length) as t_min_focal_length, 
-       and lens_type.min_focal_length = t_min_focal_length.identifier +         (select identifier as identifier, value as s_max_focal_length 
-       and lens_type.max_focal_length = t_max_focal_length.identifier +        from focal_length) as t_max_focal_length, 
-       and lens_type.min_aperture = t_min_aperture.identifier +         (select identifier as identifier, value as s_min_aperture from 
-       and lens_type.max_aperture = t_max_aperture.identifier +        aperture) as t_min_aperture, 
-     order by manufacturer.name, s_min_focal_length, model, variation; +         (select identifier as identifier, value as s_max_aperture from 
 +        aperture) as t_max_aperture 
 +   where lens_type.manufacturer = manufacturer.identifier 
 +     and lens_type.min_focal_length = t_min_focal_length.identifier 
 +     and lens_type.max_focal_length = t_max_focal_length.identifier 
 +     and lens_type.min_aperture = t_min_aperture.identifier 
 +     and lens_type.max_aperture = t_max_aperture.identifier 
 +   order by manufacturer.name, s_min_focal_length, model, variation; 
 +</code>
  
 === Related tables === === Related tables ===
Line 32: Line 39:
   * [[lens_type sql table|lens_type]]   * [[lens_type sql table|lens_type]]
   * [[manufacturer sql table|manufacturer]]   * [[manufacturer sql table|manufacturer]]
- 
-=== Related indexes === 
- 
-=== Related functions === 
- 
-=== Related sequences === 
  
 === History === === History ===
Line 53: Line 54:
 = Version 2.5 = = Version 2.5 =
  
-  create view view_lens_type as +<code sql> 
-    select lens_type.identifier, manufacturer.name, model, variation, s_min_aperture, s_max_aperture, s_min_focal_length, s_max_focal_length, manufacturer.url +create view view_lens_type as 
-      from lens_type, manufacturer, +  select lens_type.identifier, manufacturer.name, model, variation, s_min_aperture, s_max_aperture, s_min_focal_length, s_max_focal_length, manufacturer.url 
-           (select identifier as identifier, value as s_min_focal_length from focal_length) as t_min_focal_length, +    from lens_type, manufacturer, 
-           (select identifier as identifier, value as s_max_focal_length from focal_length) as t_max_focal_length, +         (select identifier as identifier, value as s_min_focal_length from focal_length) as t_min_focal_length, 
-           (select identifier as identifier, value as s_min_aperture from aperture) as t_min_aperture, +         (select identifier as identifier, value as s_max_focal_length from focal_length) as t_max_focal_length, 
-           (select identifier as identifier, value as s_max_aperture from aperture) as t_max_aperture +         (select identifier as identifier, value as s_min_aperture from aperture) as t_min_aperture, 
-     where lens_type.manufacturer = manufacturer.identifier +         (select identifier as identifier, value as s_max_aperture from aperture) as t_max_aperture 
-       and lens_type.min_focal_length = t_min_focal_length.identifier +   where lens_type.manufacturer = manufacturer.identifier 
-       and lens_type.max_focal_length = t_max_focal_length.identifier +     and lens_type.min_focal_length = t_min_focal_length.identifier 
-       and lens_type.min_aperture = t_min_aperture.identifier +     and lens_type.max_focal_length = t_max_focal_length.identifier 
-       and lens_type.max_aperture = t_max_aperture.identifier +     and lens_type.min_aperture = t_min_aperture.identifier 
-     order by manufacturer.name, s_min_focal_length, model, variation;+     and lens_type.max_aperture = t_max_aperture.identifier 
 +   order by manufacturer.name, s_min_focal_length, model, variation; 
 +</code> 
 + 
 += Version 2.32 = 
 + 
 +<code sql> 
 +-- Convert focal length from a varchar to an integer 
 +--  and aperture to a numeric type 
 + 
 +drop view view_lens_type; 
 + 
 +create view view_lens_type as select lens_type.identifier,  
 +manufacturer.name, 
 +        model, variation, s_min_aperture, s_max_aperture, 
 +        s_min_focal_length, s_max_focal_length, manufacturer.url 
 +    from lens_type, manufacturer, 
 +         (select identifier as identifier, value as s_min_focal_length 
 +        from focal_length) as t_min_focal_length, 
 +         (select identifier as identifier, value as s_max_focal_length 
 +        from focal_length) as t_max_focal_length, 
 +         (select identifier as identifier, value as s_min_aperture from 
 +        aperture) as t_min_aperture, 
 +         (select identifier as identifier, value as s_max_aperture from 
 +        aperture) as t_max_aperture 
 +   where lens_type.manufacturer = manufacturer.identifier 
 +     and lens_type.min_focal_length = t_min_focal_length.identifier 
 +     and lens_type.max_focal_length = t_max_focal_length.identifier 
 +     and lens_type.min_aperture = t_min_aperture.identifier 
 +     and lens_type.max_aperture = t_max_aperture.identifier 
 +   order by manufacturer.name, s_min_focal_length, model, variation; 
 +</code> 
 + 
 += Version 2.34 = 
 + 
 +<code sql> 
 +drop view view_lens_type; 
 + 
 +create view view_lens_type as select lens_type.identifier, manufacturer.name, 
 +        model, variation, s_min_aperture, s_max_aperture, 
 +        s_min_focal_length, s_max_focal_length, manufacturer.url, 
 +        exiftool_lens_id 
 +    from lens_type, manufacturer, 
 +         (select identifier as identifier, value as s_min_focal_length 
 +        from focal_length) as t_min_focal_length, 
 +         (select identifier as identifier, value as s_max_focal_length 
 +        from focal_length) as t_max_focal_length, 
 +         (select identifier as identifier, value as s_min_aperture from 
 +        aperture) as t_min_aperture, 
 +         (select identifier as identifier, value as s_max_aperture from 
 +        aperture) as t_max_aperture 
 +   where lens_type.manufacturer = manufacturer.identifier 
 +     and lens_type.min_focal_length = t_min_focal_length.identifier 
 +     and lens_type.max_focal_length = t_max_focal_length.identifier 
 +     and lens_type.min_aperture = t_min_aperture.identifier 
 +     and lens_type.max_aperture = t_max_aperture.identifier 
 +   order by manufacturer.name, s_min_focal_length, model, variation; 
 +</code>
  
podoc/view_lens_type_sql_view.1176455425.txt.gz · Last modified: 2007/04/13 09:10 by Luud