6

I have a MYSQL database with a few tables, all of which have the same structure. I want to search all the tables to find a row with a specific value for a column. Do I have to search the tables one by one or there is an easier way?

Improve this question

3 Answers 3

Reset to default
13

You can union all tables. You still need traverse all tables one by one, but in case of union you will not have cartesian multiplication, hence best from all:

SELECT column FROM table1 WHERE column = 'value'
UNION ALL
SELECT column FROM table2 WHERE column = 'value'
;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

what to do if columns are different in the 2 tables? Because: 'UNION must have the same number of columns'
Use something like: SELECT column1 AS col FROM table1 ... UNION ALL SELECT column2 AS col FROM table2 ...
3

Easily done and ALSO TESTED IN MYSQL WORKBENCH.

SELECT ALL:

SELECT * FROM table_one, table_two;

SELECT ONE VALUE FROM TWO TABLES:

SELECT * FROM table_one, table_two WHERE field = 'some_val'

SELECT MULTIPLE VALUES FROM TWO TABLES:

SELECT * FROM table_one, table_two WHERE field = 'some_val' AND field2 = 'some_val' AND field3 = 'some_val'
Improve this answer

6 Comments

I think that better use union, because separate tables by comma it is cartesian multiplication, and union - no, even if you use union all...
Um. No. Works for me.
@StephenCarr is correct, The only thing i'd probably do differently is assign aliases to each table, that way you can search for values within uniquely named columns
even though it works, but performance wise that's a really bad answer. As @VladimirKovpak mentioned, selecting from two or more tables causes cartesian multiplication. So if you have 1000 records on each table, it makes your record set size of 1 000 000. I recommend not to use this approach if performance matters. Take a look at solutions with UNION.
@Jokūbas Completely agree with you! Moreover, I have answer with union example, but it was downvoted((( Don't know why(((
|
0

I don't understanding how FIRST I select city :product list will appare from city , next find product search , how I bring search all from same city which I select previous one automatically? Pls support on php.

Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.