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

Why do you have 3 tables with the same columns? Would this not be better off as a single test table with an extra column to differentiate between the 3 test runs? — Ben James
– Ben James, Commented Dec 2, 2009 at 13:25

VolkerK · Accepted Answer · 2009-12-02 13:26:26Z

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

answered Dec 2, 2009 at 13:26

VolkerK

96.3k20 gold badges169 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

just somebody Over a year ago

this from the mysql manual (and indeed, it's the behavior prescribed by the SQL standard): The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal.

VolkerK Over a year ago

Yes, it's the default behaviour (as already mentioned in the answer) and it also does no harm to write it explicitly into the query (for clarity).

Corey Ballou · Accepted Answer · 2009-12-02 13:48:04Z

1

@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.

edited Dec 2, 2009 at 13:48

answered Dec 2, 2009 at 13:30

Corey Ballou

43.7k8 gold badges66 silver badges77 bronze badges

3 Comments

VolkerK Over a year ago

What's the reason for wrapping the thing in a sub-query?

just somebody Over a year ago

isn't the database smart enough to see that it's only test_id that's needed? i thought mysql has grown up a bit since the 3.22 days... sigh.

Corey Ballou Over a year ago

@VolkerK - good point, I had simply pasted and modified @just somebody's code to show the optimization.

just somebody · Accepted Answer · 2009-12-02 13:25:06Z

0

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

answered Dec 2, 2009 at 13:25

just somebody

19.4k6 gold badges55 silver badges65 bronze badges

1 Comment

Corey Ballou Over a year ago

This query would cause an unnecessary amount of overhead if you are dealing with larger tables. See solution below.

Collectives™ on Stack Overflow

Mysql Query doubt

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related