Как сделать выборку и вывести данные из двух таблиц одним запросом?

Question

Есть sqlite база с двумя таблицами

t1
|id| name |lang|city|date  |
|--|------|----|----|------|
 1 |vasya |ru  |msk |1.1.70|
 2 |petya |ru  |spb |1.1.70|
|--|------|----|----|------|

t2
|id| name_test |    email    |tel |
|--|-----------|-------------|----|
 1 |vasya      |ru@localhost |1111|
 2 |petya      |ru@localhost1|2222|
 3 |vasya      |             |3333|
 4 |petya      |             |4444|
|--|-----------|-------------|----|

Результат, которго пытаюсь добиться

1   vasya   ru  msk 1.1.70  ru@localhost    1111
                                            3333
2   petya   ru  spb 1.1.70  ru@localhost1   2222
                                            4444

Вывожу таблицу

<?php
$db = new SQLite3("db2.db");
$res = $db->query('SELECT t1.id, t1.name, t1.lang, t1.city, t1.date, t2.email, t2.tel from t1 JOIN t2 ON (name=name_test)');
while ($row = $res->fetchArray()) {
echo "<table><tr><td>{$row['id']}</td><td>{$row['name']}</td><td>{$row['lang']}</td><td>{$row['city']}</td><td>{$row['date']}</td><td>{$row['email']}</td><td>{$row['tel']}</td></tr></table>";
}
$db->close();
?>

получаю

1   vasya   ru  msk 1.1.70                  3333
1   vasya   ru  msk 1.1.70  ru@localhost    1111
2   petya   ru  spb 1.1.70                  4444
2   petya   ru  spb 1.1.70  ru@localhost1   2222

sqlite> WITH cte AS ( SELECT t1.id, t1.name, t1.lang, t1.city, t1.date, t2.email, t2.tel, ROW_NUMBER() OVER (PARTITION BY t2.name_test ORDER BY t2.tel) rn FROM t1 JOIN t2 ON t1.name = t2.name_test ) SELECT CASE WHEN rn = 1 THEN id END id, CASE WHEN rn = 1 THEN name END name, CASE WHEN rn = 1 THEN email END email, tel FROM cte ORDER BY name, rn;
Error: near "(": syntax error

Answer 1

SELECT CASE WHEN t2.email > '' THEN t1.id ELSE '' END id, 
       CASE WHEN t2.email > '' THEN t1.name ELSE '' END name, 
       CASE WHEN t2.email > '' THEN t1.lang ELSE '' END lang, 
       CASE WHEN t2.email > '' THEN t1.city ELSE '' END city, 
       CASE WHEN t2.email > '' THEN t1.date ELSE '' END date, 
       COALESCE(t2.email, '') email, 
       t2.tel
FROM t1
JOIN t2 ON t1.name = t2.name_test
ORDER BY t1.id, t2.tel;

fiddle

БЛОГ НА HUSL

Как сделать выборку и вывести данные из двух таблиц одним запросом?

Ответы (1 шт):