[RESOLVED] CONDITIONAL SELECT?!

[acrabadabra]

I have a problem which I am not sure or it is even possible to fix with sql statements. So here it goes.

I have a table with 3 columns (PID, LANGUAGE, TEXT). PID+LANGUAGE is PRIMARY KEY. In this way I can have unlimited languages next to each other in one table with one correspending ID.

Depending on the language chosen, I want to retrieve the text. No so hard. SELECT TEXT FROM TABLE WHERE LANGUAGE = 'spanish'.

PROBLEM
NOT all languages are presented. So I want to fall back to English if Spanish (e.g.) is not presented in the table. Of course I could choose for SELECTING english AND the language I want, and determine with PHP or I have data. But I am talking about 10k+ rows, so I dont want to retrieve everything double. Just only when I really need to!

How to do this in SQL statements?

[Lars_Berg]

SELECT TEXT,language 
   FROM TABLE 
  WHERE LANGUAGE = 'spanish'
union all
select text,language
  from TABLE as t
 where language = 'english'
     and not exists
    (select *
         from table
       where language = 'spanish'
           and table.pid = t.pid)

[acrabadabra]

Thanks Lars! I had never heard of the UNION command 🙁
Will make things so much easier now!