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