sql query question

Question

I have tables

table 1

id text
1   A
1   B
2   C

table 2

id text
1  x
1  f  
2  y
2  z

I want to join them this way

1 A x
1 B f
2 C y
2   z

In other words i want to see all texts from table1 and table2 grouped by id, with no repeats. Any ideas?

Update: as they say in comments, the logic is not clear, I'll try to explain. I have current values in table_1 and deleted values in table_2. Customer wants to see current values and deleted values in one table grouped by some id.

Answer 1

This works if you can have no more than two items per id in either table and if neither one has complete duplicates. (And I must also add that this can only work if MySQL is able to swallow this monster and not choke with it.)

SELECT
  COALESCE (t1.id, t2.id) AS id,
  t1.text AS text1,
  t2.text AS text2
FROM (
  SELECT
    t.id,
    t.text,
    CASE t.text WHEN m.text THEN 1 ELSE 2 END AS rowid
  FROM table_1 t
    INNER JOIN (
      SELECT id, MIN(text) AS text
      FROM table_1
      GROUP BY id
    ) m ON t.id = m.id
) t1
FULL JOIN (
  SELECT
    t.id,
    t.text,
    CASE t.text WHEN m.text THEN 1 ELSE 2 END AS rowid
  FROM table_2 t
    INNER JOIN (
      SELECT id, MIN(text) AS text
      FROM table_2
      GROUP BY id
    ) m ON t.id = m.id
) t2
ON t1.id = t2.id AND t1.rowid = t2.rowid
ORDER BY COALESCE (t1.id, t2.id), COALESCE (t1.rowid, t2.rowid)

Answer 2

Simple solution to get something close to what you're looking for

SELECT t1.id, t1.text, t2.text
FROM tbl_1 t1
  INNER JOIN tbl_2 t2
    ON t1.id = t2.id

this will create output

1 A x
1 B x
2 C y
2 C z

Only different is now that the duplicated texts x and C should somehow removed.

Update precondition: duplicates per id are either in tbl_1 or tbl_2 not both !

Joining a grouped select in addition to above simple solution will allow to create kind of "CASE-Filters" to get your desired output.

SELECT
  t1.id,
  CASE
    WHEN t2.text = txt_i2 THEN t1.text
  END AS txt_t1,
  CASE
    WHEN t1.text = txt_i1 THEN t2.text
  END AS txt_t2
FROM (
    SELECT
      i1.id,
      i1.text AS txt_i1,
      i2.text AS txt_i2
    FROM tbl_1 i1
      INNER JOIN tbl_2 i2
        ON i1.id = i2.id
    GROUP BY id
  ) i
  INNER JOIN tbl_1 t1
    ON i.id = t1.id
  INNER JOIN tbl_2 t2
    ON t1.id = t2.id

You should create a view of the tbl_1-tbl_2-join to get more readable SQL:

CREATE OR REPLACE VIEW V_tbl_1_2 AS (
SELECT 
  t1.id,
  t1.text AS txt_1,
  t2.text AS txt_2
FROM tbl_1 t1
  INNER JOIN tbl_2 t2
    ON t1.id = t2.id
)
;

SELECT
  t.id,
  CASE
    WHEN t.txt_2 = i.txt_2 THEN t.txt_1
  END AS txt_t1,
  CASE
    WHEN t.txt_1 = i.txt_1 THEN t.txt_2
  END AS txt_t2
FROM V_tbl_1_2 t
  INNER JOIN (
    SELECT *
    FROM V_tbl_1_2
    GROUP BY id
  ) i ON t.id = i.id
;

Answer 3

USE MYSQL VIEW OR JOIN