tag:blogger.com,1999:blog-6082652835152798567.post5838052458599329669..comments2024-03-28T00:58:12.736-07:00Comments on Sql server, .net and c# video tutorial: Common Table Expressions - Part 49Unknownnoreply@blogger.comBlogger12125tag:blogger.com,1999:blog-6082652835152798567.post-83349610742950683512021-05-20T19:34:55.919-07:002021-05-20T19:34:55.919-07:00yes obviously yes obviously zoyeb shaikhhttps://www.blogger.com/profile/03387462516944944090noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-62173265789236775342020-12-26T13:58:08.863-08:002020-12-26T13:58:08.863-08:00two tables are based on relation .if there are two...two tables are based on relation .if there are two tables related to each other by using primary key and foreign key contstrants ..........then we need to include join concept.. in your query add following statements ..<br />Select DeptName, TotalEmployees<br />from tblDepartment<br />join EmployeeCount<br />on tblDepartment.DeptId = EmployeeCount.DepartmentId<br />order by TotalEmployeesvijayhttps://www.blogger.com/profile/15121955634662830956noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-81743130514013325842020-05-27T20:38:15.544-07:002020-05-27T20:38:15.544-07:00i have a question please for that which one is fas...i have a question please for that which one is fast with in CTE OR Drived tableNutritionhttps://www.blogger.com/profile/14212468588088024727noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-81949919340817383982020-05-16T15:18:37.015-07:002020-05-16T15:18:37.015-07:00With EmployeeCount(DepartmentId, TotalEmployees)
a...With EmployeeCount(DepartmentId, TotalEmployees)<br />as<br /> (Select DepartmentId, COUNT(*) as TotalEmployees<br /> from tblEmployee<br /> group by DepartmentId)<br /><br />When i try to execute the query, it throws me the below error<br />Msg 102, Level 15, State 1, Line 5<br />Incorrect syntax near ')'.<br /><br />Please explain.<br />Mitrahttps://www.blogger.com/profile/14574230845961255743noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-56089433985680080242020-04-14T21:18:45.629-07:002020-04-14T21:18:45.629-07:00You left out the where clause in the second CTE, s...You left out the where clause in the second CTE, so below returns all 4 rows:<br /><br />With EmployeesCountBy_Payroll_IT_Dept(DepartmentName, Total)<br />as<br />(<br /> Select DeptName, COUNT(Id) as TotalEmployees<br /> from tblEmployee<br /> join tblDepartment <br /> on tblEmployee.DepartmentId = tblDepartment.DeptId<br /> where DeptName IN ('Payroll','IT')<br /> group by DeptName<br />),<br />EmployeesCountBy_HR_Admin_Dept(DepartmentName, Total)<br />as<br />(<br /> Select DeptName, COUNT(Id) as TotalEmployees<br /> from tblEmployee<br /> join tblDepartment <br /> on tblEmployee.DepartmentId = tblDepartment.DeptId<br /> group by DeptName <br />)<br />Select * from EmployeesCountBy_HR_Admin_Dept <br />--UNION<br />--Select * from EmployeesCountBy_Payroll_IT_Deptteddemanhttps://www.blogger.com/profile/16678112551744530673noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-23728406770846217772019-12-02T19:43:51.681-08:002019-12-02T19:43:51.681-08:00Inside the tempdb and we can't see itInside the tempdb and we can't see itAnonymoushttps://www.blogger.com/profile/10060877289782547456noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-37918821752526690742019-04-27T20:28:04.532-07:002019-04-27T20:28:04.532-07:00Hello Venkat, can we create 3 cte with one with cl...Hello Venkat, can we create 3 cte with one with clause?Nancyhttps://www.blogger.com/profile/04800919516331579799noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-23080674104227325472018-07-03T01:48:47.265-07:002018-07-03T01:48:47.265-07:00CREATE TABLE tblEmployee2
(
Id int Primary Key,
...CREATE TABLE tblEmployee2<br />(<br /> Id int Primary Key,<br /> Name nvarchar(30),<br /> Gender nvarchar(10),<br /> DepartmentId int<br />)<br />CREATE TABLE tblDepartment1<br />(<br /> DeptId int Primary Key,<br /> DeptName nvarchar(20)<br />)<br /><br />Insert into tblDepartment1 values (1,'IT')<br />Insert into tblDepartment1 values (2,'Payroll')<br />Insert into tblDepartment1 values (3,'HR')<br />Insert into tblDepartment1 values (4,'Admin')<br /><br /><br />Insert into tblEmployee2 values (1,'John', 'Male', 3)<br />Insert into tblEmployee2 values (2,'Mike', 'Male', 2)<br />Insert into tblEmployee2 values (3,'Pam', 'Female', 1)<br />Insert into tblEmployee2 values (4,'Todd', 'Male', 4)<br />Insert into tblEmployee2 values (5,'Sara', 'Female', 1)<br />Insert into tblEmployee2 values (6,'Ben', 'Male', 3) <br />Insert into tblEmployee2 values (7,'Buddy', 'Male', 1) <br /><br /><br /><br />with CTE_on_Tblemployee2<br />as<br />(<br />select * from tblemployee2<br />)<br /><br />select t.Gender, t.DepartmentId, Count(*) as total_no_of_Employees from CTE_on_Tblemployee2 t<br />inner join tbldepartment1 td<br />on t.DepartmentId=td.DeptId<br />group by t.Gender,t.DepartmentId<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-68017351451755318562018-06-29T22:23:27.680-07:002018-06-29T22:23:27.680-07:00Query Processing - use all key components (IN, EXI...Query Processing - use all key components (IN, EXISTS, CTE)Anonymoushttps://www.blogger.com/profile/11481725786803181806noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-91990470175211237842018-04-23T09:48:22.592-07:002018-04-23T09:48:22.592-07:00Select DeptName, TotalEmployees
from tblDepartment...Select DeptName, TotalEmployees<br />from tblDepartment<br />join EmployeeCount<br />on tblDepartment.DeptId = EmployeeCount.DepartmentId<br />order by TotalEmployees<br /><br />how can you use a column from cte in select statement with joins without actually refering to cte<br />Select DeptName, EmployeeCount.TotalEmployees<br />from tblDepartment<br />join EmployeeCount<br />on tblDepartment.DeptId = EmployeeCount.DepartmentId<br />order by TotalEmployeesAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-5168312502507312972016-02-11T02:40:35.631-08:002016-02-11T02:40:35.631-08:00Hi Venkat can you please explain below queries:
;...Hi Venkat can you please explain below queries:<br /><br />;with fact as (<br /> select 1 as fac, 1 as num<br /> union all<br /> select fac*(num+1), num+1<br /> from fact<br /> where num<12)<br />select fac<br />from fact<br />where num=7<br />----------------------------------------------------------<br />;with fibo as (<br /> select 0 as fibA, 0 as fibB, 1 as seed, 1 as num<br /> union all<br /> select seed+fibA, fibA+fibB, fibA, num+1<br /> from fibo<br /> where num<12)<br />select fibA<br />from fibo<br />Jayaramhttps://www.blogger.com/profile/09237197366397863151noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-48068760431118281812013-09-06T22:09:26.461-07:002013-09-06T22:09:26.461-07:00when we create a CTE ,it stores the temporary resu...when we create a CTE ,it stores the temporary result set..so can i know where this temporary resultset will be stored.sunnyhttps://www.blogger.com/profile/04213709449472679161noreply@blogger.com