fork download
copy 
  1. /* Create the schema for our tables */
  2. create table Highschooler(ID int, name text, grade int);
  3. create table Friend(ID1 int, ID2 int);
  4. create table Likes(ID1 int, ID2 int);
  5.  
  6. /* Populate the tables with our data */
  7. insert into Highschooler values (1510, 'Jordan', 9);
  8. insert into Highschooler values (1689, 'Gabriel', 9);
  9. insert into Highschooler values (1381, 'Tiffany', 9);
  10. insert into Highschooler values (1709, 'Cassandra', 9);
  11. insert into Highschooler values (1101, 'Haley', 10);
  12. insert into Highschooler values (1782, 'Andrew', 10);
  13. insert into Highschooler values (1468, 'Kris', 10);
  14. insert into Highschooler values (1641, 'Brittany', 10);
  15. insert into Highschooler values (1247, 'Alexis', 11);
  16. insert into Highschooler values (1316, 'Austin', 11);
  17. insert into Highschooler values (1911, 'Gabriel', 11);
  18. insert into Highschooler values (1501, 'Jessica', 11);
  19. insert into Highschooler values (1304, 'Jordan', 12);
  20. insert into Highschooler values (1025, 'John', 12);
  21. insert into Highschooler values (1934, 'Kyle', 12);
  22. insert into Highschooler values (1661, 'Logan', 12);
  23.  
  24. insert into Friend values (1510, 1381);
  25. insert into Friend values (1510, 1689);
  26. insert into Friend values (1689, 1709);
  27. insert into Friend values (1381, 1247);
  28. insert into Friend values (1709, 1247);
  29. insert into Friend values (1689, 1782);
  30. insert into Friend values (1782, 1468);
  31. insert into Friend values (1782, 1316);
  32. insert into Friend values (1782, 1304);
  33. insert into Friend values (1468, 1101);
  34. insert into Friend values (1468, 1641);
  35. insert into Friend values (1101, 1641);
  36. insert into Friend values (1247, 1911);
  37. insert into Friend values (1247, 1501);
  38. insert into Friend values (1911, 1501);
  39. insert into Friend values (1501, 1934);
  40. insert into Friend values (1316, 1934);
  41. insert into Friend values (1934, 1304);
  42. insert into Friend values (1304, 1661);
  43. insert into Friend values (1661, 1025);
  44. insert into Friend select ID2, ID1 from Friend;
  45.  
  46. insert into Likes values(1689, 1709);
  47. insert into Likes values(1709, 1689);
  48. insert into Likes values(1782, 1709);
  49. insert into Likes values(1911, 1247);
  50. insert into Likes values(1247, 1468);
  51. insert into Likes values(1641, 1468);
  52. insert into Likes values(1316, 1304);
  53. insert into Likes values(1501, 1934);
  54. insert into Likes values(1934, 1501);
  55. insert into Likes values(1025, 1101);
  56.  
  57.  
  58. select *
  59. from   Likes L
  60. where  L.ID2 not in (select ID1 from Likes);
  61.  
  62. select '--';
  63.  
  64. select *
  65. from   ( Highschooler H1 join Likes L1
  66.          on H1.ID = L1.ID1 ) as HL1
  67.        left join
  68.        ( Highschooler H2 join Likes L2
  69.          on H2.ID = L2.ID1 ) as HL2
  70.        on HL1.ID2 = HL2.ID1
  71. where  HL2.ID is null;
  72.  
  73. select '--';
  74.  
  75. select *
  76. from   ( Highschooler H1
  77.          join 
  78.          (Likes L1 left join Likes L2 on L1.ID2 = L2.ID1) as LL
  79.          on H1.ID = LL.ID1 ) as H1LL
  80.        left join 
  81.          Highschooler H2
  82.        on H1LL.ID2 = H2.ID;
  83.  
  84. select '--';
  85.  
  86. select *
  87. from   ( Highschooler H1
  88.          join 
  89.          ( select L1.ID1 as L1ID1, L1.ID2 as L1ID2,
  90.                   L2.ID1 as L2ID1, L2.ID1 as L2ID2
  91.            from   Likes L1
  92.                   left join
  93.                   Likes L2
  94.                   on L1.ID2 = L2.ID1
  95.            where  L2.ID1 is null )
  96.          LL
  97.          on H1.ID = LL.ID1 );
Success #stdin #stdout #stderr 0.01s 5304KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
1247|1468
1641|1468
1316|1304
1025|1101
--
1641|Brittany|10|1641|1468|||||
1247|Alexis|11|1247|1468|||||
1316|Austin|11|1316|1304|||||
1025|John|12|1025|1101|||||
--
1689|Gabriel|9|1689|1709|1709|1689|1709|Cassandra|9
1709|Cassandra|9|1709|1689|1689|1709|1689|Gabriel|9
1782|Andrew|10|1782|1709|1709|1689|1709|Cassandra|9
1641|Brittany|10|1641|1468|||1468|Kris|10
1247|Alexis|11|1247|1468|||1468|Kris|10
1316|Austin|11|1316|1304|||1304|Jordan|12
1911|Gabriel|11|1911|1247|1247|1468|1247|Alexis|11
1501|Jessica|11|1501|1934|1934|1501|1934|Kyle|12
1025|John|12|1025|1101|||1101|Haley|10
1934|Kyle|12|1934|1501|1501|1934|1501|Jessica|11
--
stderr
copy 
Error: near line 86: no such column: LL.ID1
https://ideone.com/dlYhrP
language:
SQLite (sqlite 3.27.2)
created:
10 days ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!