Question

create table node(
node_id integer primary key,
node_color varchar(10));

create table edge(
edge_id integer primary key,
origin_id integer,
destination_id integer,
foreign key (origin_id) references node(node_id),
foreign key (destination_id) references node(node_id));

write an SQL query that lists all those nodes that have edges with a destination node that has color 'red'.

Answer 1

Hi,

Please find below query as per your requirement.

Let me know if you have any doubt/concerns in this via comments.

Hope this answer helps you.

Thanks.

Solution:

Select n.node_id,n.node_color from node n 
inner join edge e on e.origin_id = n.node_id
inner join node n1 on n1.node_id = e.destination_id
where n1.node_color = 'red'; 

Here in this query we using inner join, which gives all matching values. Here node and edge matching basis on origin_id and node_id which satisfies condition of node having edges.

After that again using inner join on edge and node matching on the basis of node_id and destination_id, meaning we are checking the node who are destination node.

In where condition we are checking destination node color as 'red' i.e. filtering result on color.

Sample data used to test query :

insert into node values(1,'black');
insert into node values(2,'red');
insert into node values(3,'blue');
insert into node values(4,'red');

insert into edge values(1,1,2);
insert into edge values(2,2,3);
insert into edge values(3,3,4);

/***********Sample Output*************/