Contents
How to find all parents, children using SQL query?
It will not only find Parent of children for a particular level but you can use it to find both till N level. For the users, who want to work with hierarchical data relationships, this will work like a dream. For my example, I will create two tables to manage Master and relationship entries.
How to find all parents of a particular record?
For those who require to get only Query Part 1: Find only Parents of a particular record The following query is used to get the record and it’s all parent category records from the above tables. This query will produce a result as follows.
How to find the first parent of an employee?
Finding the first parent (manager) of an employee that makes twice as much in a self-referencing table? Anyone who is a manager of your manager is also your indirect manager so Omar has Alex and James as indirect managers. I need a query that will get me the first indirect manager who has twice salary or more than the employee.
How to count number of Records in a child table?
There is another table which uses Table1 primary key as foreign key. I want to count number of records in this foreign key table with that select like this: Column ‘dbo.Table1.NameLang1’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Please suggest how to fix this?
How to display parent column in SQL Server?
If you do not want the ‘Parent’ column to be blank, but want to show a ‘-‘ dash then use this query. The CASTing of the NULL might look odd, but SQL Server defaults the data type to INT unless specified in a manner like you see in my query. I am facing same situation.
How do I query for system views in a SQL Server database?
A couple of simple tests also do not return the system view, which is present. You can try them without a WHERE and not get any system views. I could test for SQL Server version, but If the view gets added to SQL Server 2016 in a Service Pack, I want my query to have only checked for the presence of the view.
How to give access to only views to a user?
There are many ways to grant access to views to an user. 1.You can create Database Role and that role can have Permission to Select on all views. –Execute the results you get for the above statement which will grant select on all the views to DB role db_viewselect. 2.Now then add the user (s) as member of that role.