CODEWITHSUNDEEP
phpmysqlsqldatabase
Adrianno Barello
Adrianno Barello

Reputation: 137

Searching for something in Database (2) - SELECT - Need to search in 2 tables

enter image description here As you can see in the print screen above, I am wondering, if it is possible to check if there is one thing in two tables at once WITH gaining whole one row if found (logical 1). Without making 2 queries. I need to look, if "fajne-to-jest" is in table1 or table2, and then take data row. I'm doing it in 2 queries... but question is, is it possible to check this information using one query? Mabye something else? Most efficent way? The best way?

@Gordon Linoff gave me this:

select (exists (select 1 from table1 where url = 'fajne-to-jest')) as in_table1,
       (exists (select 1 from table2 where url = 'fajne-to-jest')) as in_table2;

But this produces: enter image description here

And I want query to produce this + data from this row, like this: enter image description here

Upvotes: 1

Views: 58

Answers (1)

A C
A C

Reputation: 705

Here's a quick stab at this, haven't tested it at all:

select 
    IF(table1.id IS NULL, 0, 1) as table1,
    IF(table2.id IS NULL, 0, 1) as table2,
    COALESCE(table1.id, table2.id, "NONE") as id,
    `master`.url
from
(select 'fajne-to-jest' as url)  `master`
left join table1 on table1.url = `master`.url
left join table2 on table2.url = `master`.url
;

Should always give a single row, whether it's found in 0,1, or both tables. You said url is unique so shouldn't need to worry about repeats, but if IDs don't match then this favors table1 (and prints "NONE" if not in either table).

Upvotes: 2

Related Questions