fork(5) download
copy 
  1. CREATE TABLE posts (
  2.   post_id BIGINT,
  3.   date DATE,
  4.   post_name VARCHAR(200),
  5.   post_title VARCHAR(200),
  6.   post_description VARCHAR(200));
  7.  
  8. CREATE TABLE meta (
  9.   meta_id BIGINT,
  10.   meta_name VARCHAR(200));
  11.  
  12. CREATE TABLE meta_data (
  13.   meta_data_id BIGINT,
  14.   meta_id BIGINT,
  15.   type VARCHAR(50),
  16.   description VARCHAR(200),
  17.   parent BIGINT,
  18.   count BIGINT);
  19.  
  20. CREATE TABLE meta_relationships (
  21.   relationship_id BIGINT,
  22.   object_id BIGINT,
  23.   meta_data_id BIGINT,
  24.   meta_order INT);
  25.  
  26. INSERT INTO posts
  27. (post_id,date,post_name,post_title,post_description)
  28. VALUES
  29. (1,'2015-12-12','post1','Sylvester the cat','Sylvester the cat has been causing trouble again.'),
  30. (2,'2015-12-12','post2','Tweety bird blues','Sylvester the cat ate tweety bird.'),
  31. (3,'2015-12-12','post3','Polly the parrot','Some pictures of Polly the parrot.'),
  32. (4,'2015-12-12','post4','Went to a movie','Last night the family went and watched a movie.'),
  33. (5,'2015-12-12','post5','Joined a band','I joined a band named led zeppelin.'),
  34. (6,'2015-12-12','post6','Saw my doctor','Made an appointment with my doctor and he said I need to eat healthier.'),
  35. (7,'2015-12-12','post7','Worked out today','I did some pushups and situps.'),
  36. (8,'2015-12-12','post8','Landed the job','Got a new job today at stackoverflow!'),
  37. (9,'2015-12-12','post9','Doing maintenance on the boat','Cleaned the carburetor in the boat today.'),
  38. (10,'2015-12-12','post10','Walked over to the skate park','Fell and skinned my knee.'); 
  39.  
  40. INSERT INTO meta
  41. (meta_id,meta_name)
  42. VALUES
  43. (1,'animals'),
  44. (2,'computers'),
  45. (3,'entertainment'),
  46. (4,'health'),
  47. (5,'lifestyle'),
  48. (6,'transportation'),
  49. (100,'birds'),
  50. (101,'insects'),
  51. (102,'mammals'),
  52. (103,'fish'),
  53. (104,'hardware'),
  54. (105,'internet'),
  55. (106,'networking'),
  56. (107,'games'),
  57. (108,'music'),
  58. (109,'videos'),
  59. (110,'diet'),
  60. (111,'fitness'),
  61. (112,'medical'),
  62. (113,'activities'),
  63. (114,'family'),
  64. (115,'religion'),
  65. (116,'cars'),
  66. (117,'motorcycles'),
  67. (118,'trucks'),
  68. (119,'pets'),
  69. (120,'cats');
  70.  
  71. INSERT INTO meta_data
  72. (meta_data_id,meta_id,type,description,parent,`count`)
  73. VALUES
  74. (1,1,'category','various information about animals.',0,9),
  75. (2,2,'category','various information about computers.',0,4),
  76. (3,3,'category','various information about entertainment.',0,22),
  77. (4,4,'category','various information about health.',0,18),
  78. (5,5,'category','various information about lifestyles.',0,9),
  79. (6,6,'category','various information about transportation.',0,8),
  80. (7,100,'category','various information about birds.',1,4),
  81. (8,108,'category','various information about music.',3,6),
  82. (9,111,'category','various information about fitness.',4,3),
  83. (10,116,'category','various information about cars.',0,0),
  84. (10,119,'category','various information about pets.',1,11),
  85. (10,120,'category','various information about cats.',119,5);
  86.  
  87. INSERT INTO meta_relationships
  88. (relationship_id,object_id,meta_data_id,meta_order)
  89. VALUES
  90. (1,1,1,0),
  91. (2,1,119,1),
  92. (3,1,120,2),
  93. (4,2,1,0),
  94. (5,2,119,1),
  95. (6,2,100,2),
  96. (7,3,1,0),
  97. (8,3,119,1),
  98. (9,3,100,2),
  99. (10,4,3,0),
  100. (11,5,3,0),
  101. (12,5,108,1),
  102. (13,6,4,0),
  103. (14,7,4,0),
  104. (15,7,111,1),
  105. (16,8,5,0),
  106. (17,9,6,0),
  107. (18,10,6,0);
  108.  
  109. SELECT posts.post_id,posts.post_name,posts.post_title,posts.post_description,posts.date,meta.meta_name
  110. FROM posts
  111. LEFT JOIN meta_relationships ON meta_relationships.object_id = posts.post_id
  112. LEFT JOIN meta_data ON meta_relationships.meta_data_id = meta_data.meta_data_id
  113. LEFT JOIN meta ON meta_data.meta_id = meta.meta_id
  114. WHERE meta.meta_name IN ('entertainment') 
  115. AND NOT EXISTS (SELECT meta_relationships.meta_order FROM meta_relationships WHERE meta_relationships.meta_order = 0
  116. AND meta_relationships.meta_order = 1);
Success #stdin #stdout 0s 3360KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
4|post4|Went to a movie|Last night the family went and watched a movie.|2015-12-12|entertainment
5|post5|Joined a band|I joined a band named led zeppelin.|2015-12-12|entertainment
https://ideone.com/vHQ0pU
language:
SQLite (sqlite 3.27.2)
created:
9 years 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!