Mysql Query doubt

Question

I have three tables namely test1, test2, test3

test1 data
===========
id test_id q_id 
1    25      2
2    27      2

test2 data
===========
id test_id q_id 
1    25      2
2    34      2

test3 data
===========
id test_id q_id 
1    34      2

how get test_id value with q_id = 2 from these three tables without repeating data ?

that is 25, 27, 34

Answer 1

SELECT test_id
FROM (
    SELECT * FROM test1 UNION
    SELECT * FROM test2 UNION
    SELECT * FROM test3
) tests
WHERE q_id = 2

Answer 2

If you really can't get rid of two of the three structural identical tables take a look at the UNION operator. The default behaviour is UNION DISTINCT which removes duplicates from the results.

  SELECT test_id FROM test1 WHERE q_id=2
UNION DISTINCT
  SELECT test_id FROM test2 WHERE q_id=2
UNION DISTINCT
  SELECT test_id FROM test3 WHERE q_id=2

Answer 3

@just somebody - Your query is doing 3 select * which might be intensive, it is best to limit all three to avoid an unnecessary number of rows:

    SELECT test_id, 'test1' AS tableName FROM test1 WHERE q_id = 2
    UNION
    SELECT test_id, 'test2' AS tableName FROM test2 WHERE q_id = 2
    UNION
    SELECT test_id, 'test3' AS tableName FROM test3 WHERE q_id = 2

The above query was modified to reflect which table each q_id came from.