Code :
CREATE TABLE news ( news_id int identity(1,1) PRIMARY KEY, news varchar(max) NOT NULL ) CREATE TABLE flux ( flux_id int identity(1,1) PRIMARY KEY, flux varchar(max) NOT NULL, company_id int NOT NULL ) --drop table company CREATE TABLE company ( company_id int identity(1,1) PRIMARY KEY, company varchar(max) NOT NULL, company_group_id int NULL, company_level int NULL, company_source int NULL ) CREATE TABLE news_in_flux ( flux_id int NOT NULL, news_id int NOT NULL ) INSERT news (news) VALUES ('My first news...'), ('Second news'), ('Third news') INSERT news (news) VALUES ('News 4'), ('News 5'), ('News 6'), ('News 7'), ('News 8'), ('News 9') SELECT * FROM news INSERT company (company,company_group_id) VALUES ('Apple', NULL), ('Microsoft',NULL), ('Google',NULL), ('iPhone Corp.',1), ('iPad Corp.',1), ('SilverLight',2), ('Youtube',3), ('Ebay', 3), ('PayPal',8) SELECT * FROM company INSERT flux (flux,company_id) VALUES ('Apple1',1), ('Apple3',1), ('Msft2',2), ('Msft4',2), ('Google7',3), ('iPhone6',4), ('iPhone5',4), ('Silv8',6), ('Silv2',6), ('You7',7), ('You2',7), ('Ebay3',8), ('Ebay7',8), ('Paypal1',9), ('Paypal7',9) SELECT * FROM flux INSERT news_in_flux (flux_id,news_id) VALUES (1,1), (2,3), (3,2), (4,4), (5,7), (6,6), (7,5), (8,8), (9,2), (10,7), (11,2), (12,3), (13,7), (14,1), (15,7) SELECT * FROM news_in_flux ORDER BY news_id SELECT * FROM flux a JOIN news_in_flux b ON a.flux_id = b.flux_id JOIN news c ON b.news_id = c.news_id WHERE a.flux_id IN (1,2,3,5,9,10,11,13,15) SELECT * FROM company UPDATE company SET company_level = NULL WITH cte (company_id, company, company_group_id, company_level, company_source) AS ( SELECT company_id, company, company_group_id, 0 company_level, company_id company_source FROM company WHERE company_group_id IS NULL UNION ALL SELECT b.company_id, b.company, b.company_group_id, a.company_level + 1, a.company_source FROM cte a JOIN company b ON a.company_id = b.company_group_id ) UPDATE company SET company_level = b.company_level, company_source = b.company_source FROM company a JOIN cte b ON a.company_id = b.company_id SELECT * FROM flux a JOIN news_in_flux b ON a.flux_id = b.flux_id JOIN news c ON b.news_id = c.news_id JOIN company d ON d.company_id = a.company_id WHERE a.flux_id IN (1,2,3,5,9,10,11,13,15) SELECT MIN(a.company), b.flux, d.news FROM company a JOIN flux b ON a.company_id = b.company_id JOIN news_in_flux c ON b.flux_id = c.flux_id JOIN news d ON c.news_id = d.news_id JOIN ( SELECT c.news_id, d.company_source, MIN(d.company_level) company_level FROM flux a JOIN news_in_flux b ON a.flux_id = b.flux_id JOIN news c ON b.news_id = c.news_id JOIN company d ON d.company_id = a.company_id WHERE a.flux_id IN (1,2,3,5,9,10,11,13,15) GROUP BY c.news_id, c.news, d.company_source ) e ON e.news_id = d.news_id AND e.company_level = a.company_level AND e.company_source = a.company_source GROUP BY b.flux, d.news
|