logo
down
shadow

Get count of children from each parent.SQL Server 2012


Get count of children from each parent.SQL Server 2012

Content Index :

Get count of children from each parent.SQL Server 2012
Tag : sql , By : Hitesh Prajapati
Date : November 29 2020, 09:01 AM

wish of those help I'd use FULL JOIN when joining CTEs instead of LEFT JOIN to catch missing Employees in both tables. In your current variant with LEFT JOIN, if EmployeeDBA has no rows, but EmployeeDBB has rows, your query would return empty result set, even though the tables are different.
In the CTE I'd join two tables instead of calculating COUNT in subquery. Though, it is likely that optimizer would generate the same plan.
WITH 
CTEA
AS
(
    SELECT
        Emp.EmployeeId
        ,COUNT(Addr.EmployeeId) AS TotalCountA
    FROM
        #EmployeeDBA AS Emp
        LEFT JOIN #EmployeeDBAddressA AS Addr ON Addr.EmployeeId = Emp.EmployeeId
        -- LEFT JOIN for those Employees that don't have addresses
    GROUP BY Emp.EmployeeId
)
,CTEB
AS
(
    SELECT
        Emp.EmployeeId
        ,COUNT(Addr.EmployeeId) AS TotalCountA
    FROM
        #EmployeeDBB AS Emp
        LEFT JOIN #EmployeeDBAddressB AS Addr ON Addr.EmployeeId = Emp.EmployeeId
        -- LEFT JOIN for those Employees that don't have addresses
    GROUP BY Emp.EmployeeId
)
SELECT
    COALESCE(CTEA.EmployeeId, CTEB.EmployeeId) AS EmployeeId
    ,CTEA.TotalCountA
    ,CTEB.TotalCountB
FROM
    CTEA
    FULL JOIN CTEB ON CTEA.EmployeeId = CTEB.EmployeeId
    -- FULL JOIN to catch missing Employees in both tables
WHERE
    ISNULL(CTEA.TotalCountA, 0) <> ISNULL(CTEB.TotalCountB, 0)
;

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Why are newly created files inheriting from 'Parent Object' instead of the parent folder on Windows Server 2012?


Tag : windows , By : user183275
Date : March 29 2020, 07:55 AM
I hope this helps . The solution was to enable inheritance and edit the permissions on C:\windows\Temp. The answer was posted on [Drupal Answers].1

Sql server CTE get parent's children then update children with parent value


Tag : sql , By : user133834
Date : March 29 2020, 07:55 AM
may help you . If you're looking for updating actual records in the Organisations table, I believe you can try this:
;WITH cte
AS (
    SELECT h.Name
        , h.ID
        , h.OrganisationLevelID
        , h.parentId
    FROM organisations h
    WHERE ID = '3eea8c17-1bfd-46d5-9ea9-2d11652d23a4'
    UNION ALL
    SELECT p.Name
        , p.ID
        , c.OrganisationLevelID  -- note that I changed the alias from "p" to "c" to use the parent's value
        , p.parentId
    FROM organisations p
    INNER JOIN cte c
        ON c.Id = p.parentId
    )
UPDATE org
SET OrganisationLevelID = cte.OrganisationLevelID
FROM organisations org
INNER JOIN cte
    ON org.id = cte.id
create table #t (id int, parentid int, orgValueId varchar(10));

insert into #t values (1, null, 'x'), (2, 1, 'o'), (3, 1, 'm'), (4, 2, 'v'), (5, 3, 'p');

select *
from #t;

with cte as (
    select id
        , orgValueId
    from #t
    where parentid is null

    union all

    select t.id
        , c.orgValueId
    from cte c
        inner join #t t on c.id = t.parentid)
update t
    set orgValueId = c.orgValueId 
from #t t
    inner join cte c
        on t.id = c.id;

select *
from #t;

Count and get children's in parent component


Tag : angular , By : jaredsmiller
Date : March 29 2020, 07:55 AM
I wish this help you I'll answer this in two parts...
1. Providing a direct answer with a way to do what you're suggesting

Count children in parent via CSS


Tag : css , By : Mforg
Date : March 29 2020, 07:55 AM

Laravel - get count in tree structure, where count in parent is sum of counts from children


Tag : php , By : Murali Ravipudi
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • Multi Keyword search on multi-column sql
  • Computing the median of salaries under each manager in BigQuery SQL
  • How can insert value by selecting from another
  • Retrieving only the latest item for a set of data
  • update column with concatenating for that group only
  • SQL `SELECT 'some string value' AS fieldname` equivalent in Powershell?
  • How to determine the AVERAGE number of associated records
  • In Django, how can I build a set of criteria from a list?
  • Using replace() inside a loop
  • select user on create view
  • Calculate and aggregate column depending on a value in another colum (SQL)
  • Trigger to track all the insert or update on Oracle Table
  • Update column - remove first and last characters
  • T-SQL - Adding FK to table, make it do drop/null/efault automagically?
  • Get constraints and indexes on materialized views
  • SQL perform AVG after MAX
  • How do you join two tables A and B in order to get a non join column in A with data not in B?
  • Move Non-NULL Values Up Within Partition
  • Selecting rows based on two many-to-many relations
  • I have a table on which i want to do some advance calculation and get the table in a new shape
  • How to convert decimal to time in Oracle SQL?
  • Combine numbers to 1 row
  • Error: column does not exist when in fact it does? why?
  • How to fix: "Operation must use an updateable query" on update statement in Access VBA
  • Does information passes to a new table via CREATE TABLE update itself?
  • How to write a SQL statement which filters between two dates/times, with the date and time in separate columns
  • MS-Access-2016 Query Language 'ORDER BY' Clause
  • How to get totals for each age category?
  • get the first n characters of getdate()
  • Query to handle the dates
  • SQL grouping/counting on a string split function
  • ORDER BY and TOP statements in SQL works different on listing down the records and why?
  • SQL Server Computed Column: how to set it to NOT NULL, without persistence
  • Is nested select statement better than where statement?
  • Postgresql sequential scan slow performance on 500 million rows
  • Aggregate first order data to all orders - Microsoft SQL Server
  • Sorting comma delimited datasets in row
  • Question about the correct data structure
  • Showing two temp tables data in a single table
  • How can I find out the query execution time in Oracle SQL using Toad?
  • convert quarter number to month name in sql
  • Find max value from column that has a json object with key-value pairs
  • case statement based on 3 columns in 1 table
  • How to write a SQL query to select batsman wiht highest score than the previous year
  • Find Substring in SQL
  • Using VALUE to make temporary table
  • What is the correct way of writing an exclusive left-join on two tables?
  • Is it possible to run a make table query using a recordset as the source in MS Access (VBA)?
  • INSERT INTO select distinct null PostgreSQL
  • Why converting date from nvarchar(max) to date throws error?
  • how to fix wm_concat in oracle 12c
  • How to UNION a list of tables retrieved from another table with a single query?
  • Oracle query to get a relational dataset result from multimensional table
  • What algorithm is used in a hash partition that it can divide up the data into similar size partitions before seeing any
  • Why does my SQL return unwanted records with my OR in Where clause
  • SQL Multiple Joins Query-Query tuning
  • How to choose max of one column per other column
  • Query that returns rows that have both values in an IN statement
  • Select Unique records from table
  • SQL Query "Select latest record where active flag = 1"
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com