mysql - SQL Query with double nested level -

mysql - SQL Query with double nested level -

i doing stanford free online course of study (which recommend!) , stuck @ next problem. please help.

find names of students friends named gabriel.

this best come with.

select name highschooler h h.id exists in ( select friend.id1 friend friend.id2 exists in ( select h.id highschooler h2 h2.name="gabriel" ) );

i'm required run query in sql lite although using mysql workbench test place (i have understood relatively similar basic queries one).

/* create schema our tables */ create table highschooler(id int, name text, grade int); create table friend(id1 int, id2 int); create table likes(id1 int, id2 int); /* populate tables our info */ insert highschooler values (1510, 'jordan', 9); insert highschooler values (1689, 'gabriel', 9); insert highschooler values (1381, 'tiffany', 9); insert highschooler values (1709, 'cassandra', 9); insert highschooler values (1101, 'haley', 10); insert highschooler values (1782, 'andrew', 10); insert highschooler values (1468, 'kris', 10); insert highschooler values (1641, 'brittany', 10); insert highschooler values (1247, 'alexis', 11); insert highschooler values (1316, 'austin', 11); insert highschooler values (1911, 'gabriel', 11); insert highschooler values (1501, 'jessica', 11); insert highschooler values (1304, 'jordan', 12); insert highschooler values (1025, 'john', 12); insert highschooler values (1934, 'kyle', 12); insert highschooler values (1661, 'logan', 12); insert friend values (1510, 1381); insert friend values (1510, 1689); insert friend values (1689, 1709); insert friend values (1381, 1247); insert friend values (1709, 1247); insert friend values (1689, 1782); insert friend values (1782, 1468); insert friend values (1782, 1316); insert friend values (1782, 1304); insert friend values (1468, 1101); insert friend values (1468, 1641); insert friend values (1101, 1641); insert friend values (1247, 1911); insert friend values (1247, 1501); insert friend values (1911, 1501); insert friend values (1501, 1934); insert friend values (1316, 1934); insert friend values (1934, 1304); insert friend values (1304, 1661); insert friend values (1661, 1025); insert friend select id2, id1 friend; insert likes values(1689, 1709); insert likes values(1709, 1689); insert likes values(1782, 1709); insert likes values(1911, 1247); insert likes values(1247, 1468); insert likes values(1641, 1468); insert likes values(1316, 1304); insert likes values(1501, 1934); insert likes values(1934, 1501); insert likes values(1025, 1101);

any help hugely appreciated!

you want reference highschooler table twice this, building relationship highschooler -> friend -> highschooler

here's how it:

select h1.name highschooler h1 inner bring together friend f on f.id1 = h1.id inner bring together highschooler h2 on f.id2 = h2.id h2.name = 'gabriel'

here's sql fiddle query above.

mysql sql select exists