Is it possible with MySQL to search for a value and with its results use those to complete the search in one statement?

A little confused with how I’d do this at all in a single statement. If it’s possible?

I would like to have my Virtual [email protected] 10.0.0.5 to see it belongs to Donny and Lisa, and the returned result would be their server domains (type 0).

I can achieve this with multiple statements but was curious if it could be done with single statements and with that unsure how! Appreciate any insight on this.

I think I got it working, this is what I have thus far!

SELECT results.domain, results.owner
FROM (
    SELECT domain, ip, owner, type
    FROM server
    WHERE owner IN (SELECT owner FROM server WHERE ip=?)
) AS results WHERE results.type=0;

So if I search 10.0.0.5, I am returned

Seems to do what I want very clean/stealthy but wondering if it can be done better than this. 😐

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Hey mate absolutely it can.

You can use the result set from 1 SQL query in theFROM statement of another Query.

Select * from (Select name from 'Table' where domain = "goto.com");

This article will take you further: https://learnsql.com/blog/sql-nested-select/

You can also use nested SQL in the WHERE clause too

Method 2

Based on your comment you can use this two methods (maybe there are other methods).

A. You can use subquery for the names

select name,type,domain
from test_tbl 
where name in ( select name 
                from test_tbl 
                where ip='10.0.0.5')
and type='0';

B. You can use inner join but in this case, I do not recommend it.

select t1.name,t1.type,t1.domain
from test_tbl t1
inner join
(
  select name,type 
  from test_tbl 
  where ip='10.0.0.5'
) t2 on t1.name=t2.name
and t1.type='0';

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/123

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0