You may have generalized yourself into a corner.

The fastest way to deal with optional data is to make it required in the actual database but have a value that means "NO DATA". Then you can use a normal join, which will outperform an outer join. If you can't do that, you have to use an outer join or do multiple queries, one for each record. Multiple queries would be slower than what you're doing now, so I don't recommend it.

Be careful when generalizing your schema. One of the things databases do is enforce certain rules about your data relationships. If you don't let the database do that, you can get into tricky territory.

In reply to Re: OT: Generalizing SQL Select statements by perrin
in thread OT: Generalizing SQL Select statements by AidanLee

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.