SQL - SELECT With Multiple WHERE Conditions On New Column Rather Than New Row

Question

I have an SQLite database of phrases and their native translations in the form

code | language_id | script_source | script_target | transliteration
     |             |               |               |
yes  |      1      |     Yes.      |     Yes.      | NULL
no   |      1      |     No.       |     No.       | NULL
yes  |      3      |     Oui.      |     Oui.      | NULL
no   |      3      |     Non.      |     Non.      | NULL

where the language_id specifies the language. I am trying to write a query that SELECT's the code, the script_source from one language and the script_target from another language as well as the transliteration, all on one row. The closest I have managed to get is

SELECT code, script_source, transliteration FROM phrases WHERE language_id=001
  UNION
SELECT code, script_target, transliteration FROM phrases WHERE language_id=003;

which produces this query result:

code | script_source | transliteration
yes  |      Yes.     |      NULL                   
yes  |      Oui.     |      NULL  
no   |      No.      |      NULL                   
no   |      Non.     |      NULL

This is ok, but it would be better for my purposes if the `script_target' had it's own column, like this:

code | script_source | script_target | transliteration
yes  |      Yes.     |      Oui.     |      NULL   
no   |      No.      |      Non.     |      NULL

I think this will probably require a JOIN function of some sort, but as of yet I haven't been able to get the desired result. Any help will be appreciated.

EDIT: My mistake, I want the transliteration corresponding to the script_target value.

MySQL and SQLite are two different products with slightly different sql syntax and vastly different features. Pls only use the relevant product tags for your question. — Shadow, Jun 19 '17 at 11:04
[MySQL](https://dev.mysql.com/doc/refman/5.7/en/) and [SQLite](https://www.sqlite.org/) are different, unrelated software packages produced by different companies/persons. Please use the correct tags. — axiac, Jun 19 '17 at 11:04
Btw this transformation is called pivoting and the result is called pivot table. — Shadow, Jun 19 '17 at 11:06

Gordon Linoff · Accepted Answer · 2017-06-19T11:44:32.853

2

One method is a join:

SELECT p1.code, p1.script_source,p2.script_source as script_target, 
       p2.transliteration as transliateration
FROM phrases p1 JOIN
     phrases p2
     ON p1.code = p2.code
WHERE p1.language_id = 1 AND p2.language_id = 3;

It is not clear which transliteration you want.

edited Jun 19 '17 at 11:44

answered Jun 19 '17 at 11:05

Gordon Linoff

1,242,037
58
646
786

This works well, but (sorry for not clarifying) I only want the transliteration for script_target. – Sadie LaBounty Jun 19 '17 at 11:07

Giorgos Betsos · Answer 2 · 2017-06-19T11:26:34.410

For a fixed set of language_id values you can do something like:

SELECT code, 
       MAX(CASE WHEN language_id = 1 THEN script_source END) AS script_source, 
       MAX(CASE WHEN language_id = 3 THEN script_target END) AS script_target,
       MAX(CASE WHEN language_id = 3 THEN transliteration END) AS transliteration 
FROM phrases 
WHERE language_id IN (1,3)
GROUP BY code

The above query picks the transliteration value that corresponds to language_id = 3.

score 1 · Answer 3 · answered Jun 19 '17 at 11:07

What you are describing is a join :-)

SELECT 
  source.code, 
  source.script_source, 
  source.transliteration as source_transliteration,
  target.script_taget, 
  target.transliteration as target_transliteration
FROM phrases source
JOIN phrases target ON target.code = source.code
WHERE source.language_id = 001
  AND target.language_id = 003;

SQL - SELECT With Multiple WHERE Conditions On New Column Rather Than New Row

3 Answers3