CODEWITHSUNDEEP
phpjquerymysqlsql
Henry8
Henry8

Reputation: 264

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

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.

Upvotes: 0

Views: 103

Answers (3)

Raging Bull
Raging Bull

Reputation: 18737

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.

Upvotes: 2

BenM
BenM

Reputation: 53198

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

Upvotes: 5

Kickstart
Kickstart

Reputation: 21513

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'

Upvotes: 1

Related Questions