Menu
  • HOME
  • TAGS

SQL Server : Recursive Advanced Query on two conditional logics Not showing hierarchy relationship

Tag: sql,sql-server,tsql,sql-server-2012,common-table-expression

Recently I got a challenge to resolve in SQL Server 2012. This is the background of the problem.

We are maintaining a self referencing entity (hierarchy) in our product entity. Each product is having parent and child relationship.

Product is having special grouping called master products which derive based on the following logic.

If there is only one product available on hierarchy with regardless of IsMasterProdcut flag it consider master account.

For other products which its immediate parent product tagged as master product or top most product which comes first consider as master product.

Graphical representation is as follow ,

http://i.stack.imgur.com/ij6Bp.jpg

This is the DDL

-- Create Table 
CREATE TABLE Product
(
    ProductID int PRIMARY KEY,
    Name      VARCHAR(30) NOT NULL,
    ParentId  int,
    IsMasterProdcut bit NOT NULL
)

-- Insert the data to the table 
-- Senario where top most product is the master product 
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (1,'Prodcut 1',NULL,0); -- <-- this is the master prodcut as non of the child as flaged
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (2,'Prodcut 2',1,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (3,'Prodcut 3',2,0);

-- Senario two where in middnle account has flag as master product 
INSERT INTO Product (ProductID,name,ParentId,IsMasterProdcut) VALUES (4,'Prodcut 4',NULL,0); -- <-- this is the master prodcut as this is top most in hirerachy . So 4 will be master prodcut of 4 and 5 , 6 and 7 will not master product
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (5,'Prodcut 5',4,0);  
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (6,'Prodcut 6',5,1); -- < -- this a a master prodcut as it is flagged as master product , So account 7 and 6 master product with be 6
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (7,'Prodcut 7',6,0);

-- Senario three where it has one product 
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (8,'Prodcut 8',0,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (9,'Prodcut 9',0,1);

-- Senario 4 Complex product 

INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (10,'Prodcut 10',0,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (11,'Prodcut 11',10,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (12,'Prodcut 12',11,1);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (13,'Prodcut 13',12,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (14,'Prodcut 14',10,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (15,'Prodcut 15',14,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (16,'Prodcut 16',15,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (17,'Prodcut 17',10,0);

Expected Result

Master Product ID   Master Product Name Product ID  Product Name
1                    Product 1          1            Product 1
1                    Product 1          2            Product 2
1                    Product 1          3            Product 3
4                    Product 4          4            Product 4
4                    Product 4          5            Product 5
6                    Product 6          6            Product 6
6                    Product 6          7            Product 7
8                    Product 8          8            Product 8
9                    Product 9          9            Product 9
10                   Product 10        10            Product 10 
10                   Product 10        11            Product 11
12                   Product 12        12            Product 12
12                   Product 12        13            Product 13
10                   Product 10        14            Product 14
10                   Product 10        15            Product 15
10                   Product 10        16            Product 16
10                   Product 10        17            Product 17

Working solution: this is the solution that I have got so far:

      BEGIN 

    CREATE TABLE #TmpMasterProduct
    (
       ProductId nvarchar(50)
    )

    INSERT INTO #TmpMasterProduct
    SELECT ProductId

    FROM (
        -- Get master accounts which are flagged as master product  
        SELECT MA.ProductId 
        FROm [dbo].[Product] AS MA WITH (NOLOCK) 
        WHERE MA.[IsMasterProdcut] = 1

        UNION
        -- Get top most prodcut which will be automatically consider as master product.

        SELECT MAT.ProductId 
        FROM DBO.[Product] As MAT WITH (NOLOCK) 
        WHERE MAT.[ParentId] IS NULL
    ) AS MasterProdcuts;


    WITH Mapping as
    (
      SELECT A.ProductId , A.ParentId
      FROM DBO.[Product] A
      WHERE  A.ProductId IN 
      (
        SELECT ProductId 
        FROM #TmpMasterProduct
      )

      UNION ALL

      SELECT A.ProductId , A.ParentId
      FROM DBO.[Product]  A
      INNER JOIN Mapping M
      ON M.ProductId = A.ParentID
     )


    SELECT  M.ParentId As MasterProductId ,  MP.Name As MasterProductName , M.ProductId As ProdcutId , CP.Name As ProductName
    From Mapping As M
    LEFT OUTER JOIN DBO.Product As MP ON MP.ProductId = M.ParentId 
    LEFT OUTER JOIN DBO.Product As CP On CP.ProductId = M.ProductId

    DROP TABLE #TmpMasterProduct

END 

But I'm deviating form the result that I want. This is the current out put I'm getting.

MasterProductId MasterProductName   ProdcutId   ProductName
NULL               NULL               1           Prodcut 1
NULL               NULL               4           Prodcut 4
5                  Prodcut 5          6           Prodcut 6
0                  NULL               9           Prodcut 9
11                 Prodcut 11         12          Prodcut 12
12                 Prodcut 12         13          Prodcut 13
6                  Prodcut 6          7           Prodcut 7
4                  Prodcut 4          5           Prodcut 5
5                  Prodcut 5          6           Prodcut 6
6                  Prodcut 6          7           Prodcut 7
1                  Prodcut 1          2           Prodcut 2
2                  Prodcut 2          3           Prodcut 3

Basically this query I wrote does not goes to deeper level. It terminates form the parent. And second observation I had is this does pick up parents nodes which are not master product.

Is my approach wrong? What is the best way I can get this done except cursors.

Best How To :

You are on the right track :) Some fixes:

You are inserting zero, not null, as ParentId into products 8 and 10 - this is why they are never picked up in your initial #TmpMasterProduct query - you'll need to change these back to NULL e.g.

INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) 
VALUES (8,'Prodcut 8',NULL,0);

(Also, if you were enforcing referential integrity with a Foreign Key from ParentId -> ProductId on the self-join, this kind of issue would be prevented)

You don't need the UNION on the master product prefilter - you can just use OR, i.e.:

INSERT INTO #TmpMasterProduct
SELECT MA.ProductId
FROM 
    [dbo].[Product] AS MA
WHERE MA.[IsMasterProdcut] = 1 OR MA.[ParentId] IS NULL;

In your recursive CTE, you'll need to keep remembering the actual MasterProductId, not necessarily the ParentId for each Master product tree, to allow for hierarchies greater than 1 deep, i.e.

WITH Mapping as
(
  SELECT A.ProductId as MasterProductId, A.ProductId , A.ParentId ...

  UNION ALL

  SELECT M.MasterProductId, A.ProductId , A.ParentId ...

You'll need to introduce a termination condition on navigating a tree, which terminates when there is a node which is itself a Master Product (this will be listed separately).

AND A.IsMasterProdcut = 0

You'll want to order the items by MasterProductId to print them out nicely.

SqlFiddle here

Pull information from SQL database and getting login errors

php,sql,database

change $username = "'rylshiel_order"; to $username = "rylshiel_order"; and you should be through. You are passing on an extra single quote here. ...

SQL: overcoming no ORDER BY in nested query

sql,sqlite

Use a join instead: SELECT a, b FROM t JOIN (SELECT DISTINCT date FROM t ORDER BY date DESC LIMIT 2) tt on t.date = tt.date; ...

Default the year based on month value

sql,sql-server

SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return "2016" Your distinct only works on the first select you've done so these are your options: 1) Use cte's with distincts with subq1 (syear, eyear,...

mysql_real_escape_string creates \ in server only not in local

php,sql

Your server has magic quotes enabled and your local server not. Remove it with the following sentence set_magic_quotes_runtime(0) As this function is deprecated and it will be deleted in PHP 7.0, I recommend you to change your php.ini with the following sentencies: magic_quotes_gpc = Off magic_quotes_runtime = Off If you...

Implement reference key in SQL Server

sql-server,sql-server-2008

It's called a "one-to-zero-or-one" relationship, as one Line might be associated to zero or one TestPacks. You can implement it by using a FK that allows NULL values. CREATE TABLE TestPack (id INT, PRIMARY KEY (id)) CREATE TABLE Line (id INT, TestPackId INT NULL, FOREIGN KEY (TestPackId) REFERENCES TestPack(id)) By...

TSQL - Error in stored procedure due to conversion failure

sql-server,sql-server-2008,tsql

I'd suggest doing it like that: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE INNER JOIN...

Query how often an event occurred at a given time

mysql,sql

This could be done using user defined variable which is faster as already mentioned in the previous answer. This needs creating incremental variable for each group depending on some ordering. And from the given data set its user and date. Here how you can achieve it select user, date, purchase_count...

Foreign key in C#

c#,sql,sql-server,database

You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx...

Add 1 to datediff result if time pass 14:30 or 2:30 PM

sql,ms-access,ms-access-2007

Could be: SELECT reservations.customerid, DateDiff("d",reservations.checkin_date, Date()) + Abs(DateDiff("s", #14:30#, Time()) > 0)AS Due_nights FROM reservations ...

SQL Server / C# : Filter for System.Date - results only entries at 00:00:00

c#,asp.net,sql-server,date,gridview-sorting

What happens if you change all of the filters to use 'LIKE': if (DropDownList1.SelectedValue.ToString().Equals("Start")) { FilterExpression = string.Format("Start LIKE '{0}%'", TextBox1.Text); } Then, you're not matching against an exact date (at midnight), but matching any date-times which start with that date. Update Or perhaps you could try this... if (DropDownList1.SelectedValue.ToString().Equals("Start"))...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012

Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...

SQL varchar variable inserts question mark

sql,sql-server-2012

there is un recognizable character in your string that is giving that ?. Delete the value and retype. see my above screen shot...

Convert AWK command to sqlite query

sql,awk,sqlite3

SQLite is an embedded database, i.e., it is designed to be used together with a 'real' programming language. It might be possible to import that log file into a database file, but the whole point of having a database is to store the data, which is neither a direct goal...

SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like

WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

sql,sql-server,sql-server-2008

You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (...

oracle sql error Case When Then Else

sql,oracle,oracle11g

Perhaps this is what you want? If there are rows in SecondTable, then do the second EXISTS: SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND (NOT EXISTS (SELECT 1 from SecondTable) OR EXISTS (SELECT 1 FROM SecondTable WHERE FirstTable.Key = SecondTable.Key and SecondTable.RowProcessed = 'Y')) AND OtherConditions ...

Get unique row by single column where duplicates exist

sql,sql-server

SELECT MIN(date),thread_id FROM messages GROUP BY thread_id HAVING COUNT(thread_id) > 1 ...

SQL Customized search with special characters

sql,sql-server,sql-server-2008

Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT...

Cannot Browse in sql to backup bak file

sql-server

Try this query RESTORE DATABASE Databasename FROM DISK = 'Z:\SQLServerBackups\tms.bak' ; ...

MySQL: Select several rows based on several keys on a given column

mysql,sql,database

If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2 ...

Matplotlib: Plot the result of an SQL query

python,sql,matplotlib,plot

Take this for a starter code : import numpy as np import matplotlib.pyplot as plt from sqlalchemy import create_engine import _mssql fig = plt.figure() ax = fig.add_subplot(111) engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') ## the data data =...

How to select next row after select in SQL Server?

sql,sql-server

The query can be written as: ; WITH Base AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Shift_Date) RN FROM #Table1 ) , WithC AS ( SELECT * FROM Base WHERE Shift2 = 'C' ) SELECT * FROM WithC UNION SELECT WithCNext.* FROM WithC C LEFT JOIN Base WithCNext ON...

How to use OFFSET and Fetch without Order by in SQL Server

sql-server,sql-server-2012,sql-order-by,fetch,offset

By adding an identity column to the temp table variable declare @TempTable table([some columns], rownr int identity(1,1) ) INSERT INTO @TempTable [some columns] select [some columns] from table1 order by col1 INSERT INTO @TempTable [same columns] select [some columns] from table2 order by col2 An automatic incrementing number is added...

Take thousand value in SQL

sql,sql-server

SELECT CONVERT(INT,YourColumn) % 1000 FROM dbo.YourTable ...

Retrieve Values As Column

mysql,sql

If types are fixed (just IMPRESSION and CLICK), you could use a query like this: SELECT headline, SUM(tracking_type='IMPRESSION') AS impressions, SUM(tracking_type='CLICK') AS clicks FROM tracking GROUP BY headline ...

T-SQL Ordering a Recursive Query - Parent/Child Structure

sql,tsql,recursion,order,hierarchy

The easiest way would be to pad the keys to a fixed length. e.g. 038,007 will be ordered before 038,012 But the padding length would have to be safe for the largest taskid. Although you could keep your path trimmed for readability and create an extra padded field for sorting....

Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql

I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...

ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables

sql-server,join

If I understood correctly this should be what you're looking for SELECT A.UserName, A.[Email ID], A.[Supervisor Email ID] FROM A INNER JOIN B ON A.UserCode = B.UserCode WHERE B.ACTIVE_FLAG = 'Y' ...

SQL Group By multiple categories

php,mysql,sql,mysqli

Just include a case statement for the group by expression: SELECT (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3' ELSE Categories.name END) as name, sum(locations.name = 'loc 1' ) as Location1, sum(locations.name = 'loc 2') as Location2, sum(locations.name = 'loc 3') as Location3, count(*) as total FROM ... GROUP BY (CASE...

Select Statement on Two different views

sql

Yes, You can use two different view in SELECT query. You have to JOIN them, if them have matched column in each other. Just treat two different views as like two different tables when using in SELECT Clause. SELECT vw1.a, vw2.b FROM View1 vw1 INNER JOIN View2 vw2 ON vw1.id...

Error connecting to MSSQL using PHP

php,sql-server,pdo,odbc,sqlsrv

Change it to: $this->link = new PDO( "sqlsrv:Server={$this->serverName},{$this->port};Database={$this->db};", $this->uid, $this->pwd ); The default SQL Server port is 1433. Note the curly brackets, they allow for class variables....

Sql injected code is inserted to my database . How to remove it

sql-server

You can use the fact that html code starts with symbol <. Then: UPDATE TableName SET SomeColumn = CASE WHEN CHARINDEX('<', SomeColumn) > 0 THEN SUBSTRING(SomeColumn, 1, CHARINDEX('<', SomeColumn) - 1) ELSE SomeColumn END If this is not true then we will need more information about data. May be it...

Setting time limit in SQL Query

sql-server

Assuming that you want between 10:00 AM and 5:00 PM, you can use this SELECT CASE WHEN CAST(GETDATE() AS TIME) BETWEEN '10:00:00' AND '17:00:00' THEN 1 ELSE 0 END In this context, Select * from table makes no sense, unless you have a time column and want to evaluate that....

INSERT INTO fails due to incorrect conversion T-SQL

sql-server,tsql

The problem is that there's no implicit conversion from varchar (your literal) to sql_variant. Just add an explicit conversion and you're done: cast('FooBar' as sql_variant) ...

Join SQL query Results and Get-ChildItem Results

sql-server,sql-server-2008,powershell

OK so if the SQL query does not have results then NULL is returned and, in essence, nothing is added to the $dbResults array. Instead lets append the results to a custom object. I don't know what PowerShell version you have so I needed to do something that I know...

left join table, find both null and match value

sql,sql-server,join

Try FULL OUTER JOIN. This is the sqlfiddle. It will produce the op you are expecting SQLFiddle select t1.years, t1.numOfppl, t2.years, t2.numOfppl from t1 full outer join t2 on t1.years=t2.years ...

Title search in SQL With replacement of noice words [on hold]

sql,sql-server,sql-server-2008

I think you want something like this: DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) ) INSERT INTO @nw VALUES ( 1, 'and' ), ( 2, 'on' ), ( 3, 'of' ), ( 4, 'the' ), ( 5, 'view' ) DECLARE @s VARCHAR(100) = 'view This of is the Man';...

Using Sum in If in Mysql

mysql,sql,select,sum

Using least would be much easier: SELECT LEAST(SUM(my_field), 86400) FROM my_table ...