Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Friday October 15, 2004
02:26 PM

How do I write an sql union without a union clause?

[ #21360 ]

For movies, we have an original_language_id. We also have a table that lists all of the languages the movie has been translated into. Naturally, we have a "languages" table, too. Buried in a massive SQL generator is a snippet of code that allows us to search for movies based upon all languages they're available in. Unfortunately, this means I need to search two tables, but I only get one bind parameter. The following bad SQL snippet demonstrates what I want to do, but the DISTINCT title_no is ambiguously defined:

ti_titles.title_no IN
  ( SELECT DISTINCT title_no
      FROM  ti_title_languages ttl,
            ti_titles tt,
            (SELECT language_id FROM ti_languages WHERE language_id = ?) tl
     WHERE  ttl.language_id = tl.language_id
       AND  tt.original_language_id  = tl.language_id)

Because this is in a huge code generator, I only get the parameter once and, because other search terms are in there, I can't guarantee which bind param I'm using, so I also can't use :1 twice. This is Oracle 9i. Any thoughts?

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • I've only used MySQL and Postgres with most my DB work done in MySQL so... if I were to do it, it'd look something like this:
    SELECT DISTINCT tt.title_no
       FROM ti_title_languages AS ttl,
            ti_titles AS tt,
            ti_languages AS tl
      WHERE tl.language_id = ?
        AND ttl.language_id = tl.language_id
        AND tt.original_language_id = tl.language_id;
  • If the ti_title_languages has the language_id and the title_no, why join to anything:

    where title_no in
    (select distinct title_no
    from ti_title_languages
    where language_id = ?)

    Primary and foreign keys should make sure that any language_id in ti_title_languages is in ti_languages, or any title_no in ti_title_languages is in ti_titles, so there should be no need to explicitly join the tables. But then again, maybe I read too fast and didn't get the finer details of the schema :-)

    • ...and unless the 'distinct' cuts down on alot of duplicates, then it is unnecessary also.
    • That's what I had originally. The ti_titles table has the original language id and that was not guaranteed to be in the ti_title_languages table because that table appeared handled the languages that the title had been translated into. Because the original language id was not a translation, it was skipped in the second table.

  • Maybe instead of writing a union without a union clause, you could use a union clause?

        ti_titles.title_no in (
           select distinct title_no
             from (
                    select title_no, language_id
                      from ti_title_languages
                    union all
                    select