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 Machine@ 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. :|

CodePudding user response：

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

CodePudding user response：

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