[thelist] Selecting two distincts

[rudy]

       ( SELECT 'Genre' AS Type
              , DISTINCT(g.GenreName) AS Name
           FROM loggenretlk as lg
           JOIN genre as g
             ON g.GenreID = lg.GenreID
           JOIN weblog as w
             ON w.LogID = lg.LogID
          WHERE w.LogID = @LogID )
   UNION
       ( SELECT 'Band' AS Type
              , DISTINCT(b.BandName) AS Name
           FROM logbandtlk as lb
           JOIN bands as b
             ON b.BandID = lb.BandID
           JOIN weblog as w
             ON w.LogID = lb.LogID
          WHERE w.LogID = @LogID )
  ORDER BY Type, Name


good one, michiel

i had never seen anybody put the subselects in parentheses before

using parentheses certainly makes it obvious that the ORDER BY clause, an
example of which i've added above, belongs to the entire query and not the
last subquery as one might assume without the parentheses

<tip type="SQL">
  when writing a UNION query, take a moment to decide if there will be any
possibility of duplicate rows -- not duplicate keys, duplicate rows --
resulting from the merged result sets of the unioned subselects
  if not, use UNION ALL instead of UNION, in order to avoid an unnecessary
sort
  the sort is expensive, too, since it has to include all columns in order
to detect duplicate rows!
</tip>

so the above UNION should really be UNION ALL...

... unless you felt like removing the DISTINCT from each subselect -- this
might introduce duplicates within the each result set (but not across
result sets, because of the Type strings in the SELECT list), and these
would be removed by the single sort for UNION, instead of two sorts for the
DISTINCTs

if that don't make sense, don't worry about it -- database servers are fast
enough that you'll probably not notice the difference, not when dealing
with results sets that are of "normal" size (i.e. have no more than a few
hundred records, typical of web pages)

rudy