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

Ovid on 2004-10-15T19:26:18

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?

---

With MySQL...

Mr. Muskrat on 2004-10-15T19:47:10

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;

Re:With MySQL...

Ovid on 2004-10-15T23:11:11

Er, am I missing something? Does the DISTINCT tt.title_no skip everything in ti_title_languages?

Re:With MySQL...

Mr. Muskrat on 2004-10-16T07:37:50

I don't remember what I was thinking at the time but looking at it now I think I meant DISTINCT ttl.title_no.

Too many joins

runrig on 2004-10-15T23:02:55

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 :-)

Re:Too many joins

runrig on 2004-10-15T23:05:16

...and unless the 'distinct' cuts down on alot of duplicates, then it is unnecessary also.

Re:Too many joins

Ovid on 2004-10-15T23:06:28

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.

wrong question?

wickline on 2004-10-15T23:19:57

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 title_no, original_language_id as language_id
                  from ti_titles
              )
        where lower(language_id) = lower(?)
    )

I believe that you can use the above with either 'en' or 'bn' as your bind param and get results which include the title_no for Mr. 3000

-matt

Re:wrong question?

Ovid on 2004-10-15T23:33:20

D'oh! I forgot about union all. Thanks!