{"id":673,"date":"2012-12-14T10:13:44","date_gmt":"2012-12-14T10:13:44","guid":{"rendered":"http:\/\/codeatelier.wordpress.com\/?p=405"},"modified":"2016-09-10T15:20:30","modified_gmt":"2016-09-10T15:20:30","slug":"query2","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/query2\/","title":{"rendered":"Query2"},"content":{"rendered":"

The first Location from second table should be added to the first table Location.<\/p>\n

\r\nCREATE TABLE projectlist(ProjectId   INT NOT NULL PRIMARY KEY AUTO_INCREMENT,\r\n                         ProjectName VARCHAR(50),\r\n                         Location    VARCHAR(50));\r\n                             \r\nCREATE TABLE LocationList(LocaId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,\r\n                          ProjectId INT,\r\n                          Location    VARCHAR(50));\r\n\r\nINSERT INTO projectlist(ProjectName)\r\n                 VALUES('Project A'),\r\n                       ('Project B'),\r\n                       ('Project C'),\r\n                       ('Project D'),\r\n                       ('Project E'),\r\n                       ('Project F'),\r\n                       ('Project G'),\r\n                       ('Project H');\r\n                           \r\nINSERT INTO LocationList(ProjectId, Location)\r\n                  VALUES(1, 'Location A'),\r\n                        (1, 'Location C'),                      \r\n                        (3, 'Location B'),\r\n                        (4, 'Location C'),\r\n                        (5, 'Location D'),\r\n                        (2, 'Location C'),\r\n                        (2, 'Location B'),\r\n                        (2, 'Location A'),\r\n                        (6, 'Location A'),\r\n                        (6, 'Location B'),\r\n                        (7, 'Location B'),\r\n                        (8, 'Location D'),\r\n                        (8, 'Location A');\r\n<\/pre>\n
\"Query<\/p>\n
\r\nUPDATE projectlist JOIN (SELECT LocationList.ProjectID,\r\n                                LocationList.Location\r\n                           FROM LocationList\r\n                           JOIN (SELECT LocationList.ProjectID,\r\n                                       min(LocaId) minLocaId\r\n                                  FROM LocationList\r\n                                 GROUP BY LocationList.ProjectID) l1\r\n                             ON LocationList.LocaId = l1.minLocaID) l2 ON projectList.ProjectID = l2.ProjectID\r\n   SET ProjectList.Location = l2.Location;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
The first Location from second table should be added to the first table Location. CREATE TABLE projectlist(ProjectId INT NOT NULL PRIMARY KEY AUTO_INCREMENT, ProjectName VARCHAR(50), Location VARCHAR(50)); CREATE TABLE LocationList(LocaId INT NOT NULL PRIMARY KEY AUTO_INCREMENT, ProjectId INT, Location VARCHAR(50)); INSERT INTO projectlist(ProjectName) VALUES(‘Project A’), (‘Project B’), (‘Project C’), (‘Project D’), (‘Project E’), (‘Project F’),… Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-673","post","type-post","status-publish","format-standard","hentry","category-queries"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/673","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=673"}],"version-history":[{"count":1,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/673\/revisions"}],"predecessor-version":[{"id":1608,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/673\/revisions\/1608"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=673"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=673"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=673"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}