Sql, Know if record exist and in which table it is

Question

tab1

| id      | description |
-------------------------
| 1       | lake        |
| 2       | moon        |
| 3       | sun         |
| 4       | mars        |

tab2

| id      | description |
-------------------------
| 1       | home        |
| 2       | top         |
| 3       | cat         |
| 4       | dog         |

tab3

| id      | description |
-------------------------
| 1       | home        |
| 2       | car         |
| 3       | yea         |
| 4       | flower      |

I would like, with a SQL query, verify "string" exit in the field "description" in one of these tables.

I use Php and I need to know if the row exist, and if yes, where the record has been found.

Why do you have 3 tables that need comparison between them? Is your data structure well designed? — casraf, May 21 '14 at 12:58
What should happen if the string exists in more than one `description` field? — BenM, May 21 '14 at 13:00
I smell a XY problem... http://meta.stackexchange.com/a/66378/248885 — Reeno, May 21 '14 at 13:05
Find your solution here : [Click][1] [1]: http://stackoverflow.com/questions/394041/mysql-how-to-search-multiple-tables-for-a-string-existing-in-any-column Same question / answer their — harsh4u, May 21 '14 at 13:07
Below link has a same answer for your question same answer here : [click][1] [1]: http://stackoverflow.com/questions/394041/mysql-how-to-search-multiple-tables-for-a-string-existing-in-any-column — harsh4u, May 21 '14 at 13:09

BenM · Answer 1 · 2014-05-21T14:24:14.193

5

You can use UNION ALL and the wildcard operator %, for example:

SELECT 'tab1' AS `table` FROM `tab1` WHERE `description` LIKE '%string%'
UNION ALL
SELECT 'tab2' AS `table` FROM `tab2` WHERE `description` LIKE '%string%'
UNION ALL
SELECT 'tab3' AS `table` FROM `tab3` WHERE `description` LIKE '%string%'

SQLFiddle Demo

edited May 21 '14 at 14:24

answered May 21 '14 at 13:02

BenM

52,573
26
113
168

Don't think you should use union all. If you do then searching for "r" for example would bring back 3 rows. 1 for tab1 and 2 for tab3. As you are bringing back the table name it seems irrelevant to bring it back identically multiple times. – Kickstart May 21 '14 at 14:48

Raging Bull · Answer 2 · 2014-05-21T15:18:58.710

2

Try this:

SELECT 'tab1' as TableName,id,description FROM tab1 WHERE description LIKE '%Cat%'
UNION
SELECT 'tab2' as TableName,id,description FROM tab2 WHERE description LIKE '%Cat%'
UNION
SELECT 'tab3' as TableName,id,description FROM tab3 WHERE description LIKE '%Cat%'

Explanation:

Using this query, the first field would be the name of the table in which the record exists.

Something like:

TableName    id    description
------------------------------
tab2         3     cat

Side Note:

If any table contains the description more than once, then you can use UNION ALL instead of UNION.

edited May 21 '14 at 15:18

answered May 21 '14 at 13:03

Raging Bull

18,593
13
50
55

The problem happen when the string is found on two or tre table; in this case it's impossible kwon the names of tables. – Henry8 May 21 '14 at 13:37
1

@Henry8 - it would return one row for every id / table combination that the string was found in. For example searching for home it would return `tab1 1 home` and `tab2 1 home` – Kickstart May 21 '14 at 13:45
@Henry8: Union all is only required when the a table contains the description more than once. Otherwise, it is not necessary since the table name is being selected with the result. – Raging Bull May 21 '14 at 15:20

score 1 · Accepted Answer · answered May 21 '14 at 13:01

1

Couple of unioned queries?

SELECT 'tab1' FROM tab1 WHERE description = 'something'
UNION
SELECT 'tab2' FROM tab2 WHERE description = 'something'
UNION
SELECT 'tab3' FROM tab3 WHERE description = 'something'

answered May 21 '14 at 13:01

Kickstart

21,403
2
21
33

Ok, using Php how can I know in which table the record was found? – Henry8 May 21 '14 at 13:03
1

Just execute that query. If it returns a row then the value is found in one of the tables. It will return one row for each table that has that that value found (if the value is found twice in one table then the union will result in the duplicate values being dropped) – Kickstart May 21 '14 at 13:04
1

@GordonLinoff - I specifically do not use `union all`. The requirement seems to be to just get a list of the tables where the description is a certain value. Union all would return the name of a table multiple times when the value was found on multiple rows which doesn't appear to be the requirement. – Kickstart May 21 '14 at 13:21
1

@Kickstart . . . The records between the tables cannot be the same, because the first column is different. The duplicate eliminate step inherent in `union` is not doing anything. – Gordon Linoff May 21 '14 at 14:11
@Kickstart this will only return exact matches in the `description` field, and the field name returned will change all of the time. Why do you keep changing the accepted answer? – BenM May 21 '14 at 14:25
@GordonLinoff - Nothing says that a table cannot contain duplicate descriptions. With UNION ALL that would result in the table being listed twice. The query is not returning the id or description (as that did not appear to be the requirement) so both rows would be the same. – Kickstart May 21 '14 at 14:39
@BenM - fully agree, and that was my reading of the OPs requirement. – Kickstart May 21 '14 at 14:40

Sql, Know if record exist and in which table it is

3 Answers3