Transact SQL :: Joining To A Hierarchical Table
Oct 19, 2015
I have a lookup table with 4 levels of codes like follows:
create table #RiskElementCategory(
[RiskElementCategoryCode] [nchar](5),
[RiskElementCategoryCodeDsc [nvarchar](50),
[RiskElementCategoryCode_2] [nchar](5),
[RiskElementCategoryLevel2Dsc] [nvarchar](50),
[Code] ...
Along with some other rows with the same format. I need to join to this table using a RiskElementCode that I get from the Source system. The trick is that it can be at any level, but I don't know which level it is at. So what I have to do is somehow get the correct row from the lookup table based on the code from the source to get the correct level.
So for Example, If i receive the RiskElementCode of 'SSR', that is in column RiskElementCategoryCode_3 so I need the row that has 'NA' for anything after RiskElementCategoryCode_3 where RiskElementCategoryCode_3 = 'SSR'. If i get 'DFR' I need to get the row where RiskElementCategoryCode_4 = 'DFR' since there are no levels deeper than 4 i don't need to check anything else. If I get 'PRR', then I need the row where RiskElementCategoryCode = 'PRR' and code_2, code_3 and code_4 = 'NA'.
So besides getting the correct row based on the code, i need to get the correct row based on the level where the next levels are 'NA'. I should only get 1 row each time.
View 2 Replies
ADVERTISEMENT
Apr 20, 2015
I run into a problem when asking to show a query of employee vacation days.
table 1:
column1 is dates
e.g.
2015-01-01
2015-01-02
2015-01-03
.
.
.
2015-12-31
table2:
employeeID
vacation_date
Tom
2015-01-03
Tom
2015-01-04
David
2015-01-04
John
2015-01-08
Mary
2015-01-012
My query output need to be:
2015-01-01
2015-01-02
2015-1-03
Tom
2015-01-04
Tom
2015-01-04
David
2015-01-05
2015-01-06
2015-01-07
2015-01-08
John
2015-01-09
2015-01-10
2015-01-11
2015-11-12
Mary
... etc... all the way to 2015-12-31
when i use left outer join, i only record one employee per date.
View 4 Replies
View Related
Jun 9, 2006
Recently I was in need of a hierarchical tree data. I learned about CTE and how they can be used to build hierarchical data with simple syntax. I used CTE and was through with the task. Later during free time, I tried to compare CTE approach with the traditional SQL 2K Table Function approach. It was surprising to see the query costs when I ran both the modes at one go...
Query Cost (relative to batch) : 0.49%
Query Text : Select * From fn_GetTree(8);
Query Cost (relative to batch) : 99.51%
Query Text : with treedata (id, parentid, status, prevStatus, lvl) as (select ...)
What does that indicate? Does it mean that the Table Function approach is much faster than CTE? I am sure that I was not making unwanted Joins in the CTE mode.
Can someone explain why that huge difference is there? And what the scenarios where CTE is better over Table Functions?
View 8 Replies
View Related
Jun 2, 2015
I have a table (can't change the schema of it since it is part of an off the shelf app ) that has columns for individuals which I need to extract several pieces of information, essentially Phone, Email Address, etc. See U1 - U6
What is a better way to return this information rather than multiple joins?
Select
[AccountNumber]
,a.[AccountId]
,[Name]
,[new_LocationID]
,[ws_name]
,[new_BillingManageruserName]
,[new_AreaServiceManName]
[Code] ....
View 3 Replies
View Related
Sep 22, 2015
I know people use ROW_NUMBER() function to do the pagination but my below two query is bit complex. Sohow to use pagination there ? I used ROW_NUMBER() OVER(ORDER BY IsNull(A.OEReference, B.OEReference) ASC) as Line in one but not sure am i right or wrong.
IF IsNull(@GroupID,'') = ''
SELECT IsNull(PartGroupName, 'UnMapped') AS PartGroupName,
CASE IsNull(PartGroupName, '')
WHEN '' THEN ''
ELSE IsNull(IsNull(K.GroupID, IsNull(C.PartGroupID,'')),'')
END AS PartGroupID,
[Code] .....
View 6 Replies
View Related
Jun 4, 2015
I am looking for an alternate logic for below-mentioned code where I am inserting into a table and having left join with the same table
insert TABLE1([ID],[Key],[Return])
select distinct a.[ID],cat1,cat2 from
(select ID,[Key] Cat1 ,[Return] cat2 from @temp as temp) a left join TABLE1 oon a.ID= o.ID
and a.Cat1 = o.[Key]
and a.cat2 = o.[return]
where [key] is null order by ID
View 2 Replies
View Related
Jun 23, 2015
I have a large table with 100 Million records that has around 1 million duplicate records that need to be deleted.
I am running a script that creates a staging table called,DuplicateTable that collects all the duplicates and then I want to write a an effecient delete statement.
Is it possible to write something like:
delete from OrigTable O join DuplicateTable D
on O.Key = D.key
Or do I have to run a loop on the DuplicateTable and run a delete statement record by record ?
View 4 Replies
View Related
Apr 22, 2015
I have the data in this format
ID NAME ParentID
CV1 CV1NAME CV
CVX1 CVX1NAME CV1
CVXX1 CVXX1NAME CVX1
CV2 CV2NAME CV
CVX2 CVX2NAME CV2
CVXX2 CVXX2NAME CVX2
How can i flatten this data into this format
CVID CVNAME CVXID CVXNAME CVXXID CVXXNAME
cv1 cv1name cvx1 cvx1name cvxx1 cvxx1name
cv2 cv2name cvx2 cvx2name cvxx2 cvxx2name
View 4 Replies
View Related
Jun 22, 2015
I have hierarchical data such as:
Id Level ParentId
1 0 1
2 1 1
3 2 2
4 0 4
5 1 4
6 0 6
7 1 6
8 2 7
9 3 8
10 4 9
11 0 11
As you can see even the parent element has parentId(in this case id = parentid)
How can I select the lowest level data in the hierarchy and get this result:
Id Level ParentId
3 2 2
5 1 4
10 4 9
11 0 11
View 4 Replies
View Related
May 3, 2007
Hellofor MS SQL 2000 i am having :CREATE TABLE [dbo].[Items]([id_Items] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,[id_ItemsSup] [int] NULL,[Name] [nvarchar] (100) NOT NULL,[SubItems][int] DEFAULT (0)) ON [PRIMARY]with : UPDATE [Items] SET SubItems = (SELECT COUNT(id_Items) AS ct FROM dbo.Items WHERE id_ItemsSup = 1) WHERE id_Items = 1I get how many subItems has Item = 1how can I update the Column SubItems (for each row) ?to get the total of subItems for each Item ?thank you
View 2 Replies
View Related
Apr 29, 2015
I have these two CTE queries, the first one is the existing one which runs fine and returns what we need, the second is a new CTE query which result I need to join in to the existing CTE, now that would be two CTE's can that be done at all?The second query is working on the same table as the first one so there are overlaps in names, and they are the same, only columns I need to "join" is the "seconds" and "AlarmSessions".
;with AlarmTree as (
select NodeID, ParentID, NodeLevel, NodeName,
cast('' as varchar(max)) as N0,
cast('' as varchar(max)) as N1,
cast('' as varchar(max)) as N2,
cast('' as varchar(max)) as N3,
[code]....
View 4 Replies
View Related
Sep 28, 2015
What I am working with unfortunately is a very poorly designed and non-normalized database. Please don't criticize the design. I didn't design it, but I have to write queries against it.I have 2 tables. 1 is called EnterVolume. The other is ExitVolume. Similar columns exist in each.
CREATE TABLE [EventPortion].[EventEnterVolume](
[SequenceNumber] [int] NULL,
[TrialID] [nvarchar](255) NULL,
[TimeOfEvent] [int] NULL,
[UniversalTime] [nvarchar](255) NULL,
[SBOINumber] [int] NULL,
[SEntityPosUpdateIndex] [int] NULL,
[VolumeIndex] [int] NULL,
[code]....
The rules of the database state that for every EnterVolume row (for a given TrialID, SBOINumber, and OwnerBOI) there must be a corresponding ExitVolume row in the ExitVolume table.What I need to do is to capture the paired TimeOfEvent entries from each table for each paired row. Nothing says that an SBOI cannot enter and exit a OwnerBOI's volume several times during the same Trial.Every time a SBOI Enters an OwnerBOI's volume during a certain trial, a row is created in the EnterVolume table. And Likewise when Exiting a OwnerBOI's volume during a certain trial, a row is created in the ExitVolume table.
So here is a query that I attempted, but gave undesirable results:
SELECT EV.TimeOfEvent AS [Enter Time], XV.TimeOfEvent AS ExitTime
FROM IntegratedTest1.EventPortion.EventEnterVolume AS EV INNER JOIN
IntegratedTest1.EventPortion.EventExitVolume AS XV ON EV.TrialID = XV.TrialID AND EV.SBOINumber = XV.SBOINumber AND EV.OwnerBOI = XV.OwnerBOI
Here is some sample data:
EnterVolume
TrialID SBOI OwnerBOI TimeOfEvent
1 A D 5
1 A D 2000
[code]....
View 8 Replies
View Related
Mar 24, 2007
for MS SQL 2000
I am trying to do a hierarchical table and i want to add a ON DELETE CASCADE
CREATE TABLE [dbo].[Users](
[id_Users] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_UsersSup] [int] NULL,
[Users] [nvarchar] (100) NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [FK_Users_Sup] FOREIGN KEY
(
[id_UsersSup]
) REFERENCES [Users] (
[id_Users]
)
ON DELETE CASCADE
but MS SQL refuse to create the foreign key
even if there is 4 levels under the deleted id_Users I want to delete all the rows on all levels under
thank you for helping
View 2 Replies
View Related
Aug 29, 2014
Following is my db table
student_id student_code student_parent_id student_name
1 11 0 a
2 111 1 b
3 1111 2 c
4 11111 3 d
How to generate following op?
student_id student_code student_parent_id student_name Hierarchy
1 11 0 a 11 - 111
2 111 1 b 11-111-1111
3 1111 2 c 11-111-1111-11111
4 11111 3 d 11111
View 9 Replies
View Related
Nov 30, 2013
I have two table. Department is hierarchical table.
Department
--- id (int primary key)
--- name (varchar)
--- parent (int)
Users
--- Id
--- name
--- department_id
This query return all data from departments. But i cannot understand how get data from users
SELECT t1.name AS lvl1, t2.name as lvl2, t3.name as lvl3
FROM Department AS t1
LEFT JOIN Department AS t2 ON t2.parent = t1.id
LEFT JOIN Department AS t3 ON t3.parent = t2.id
View 1 Replies
View Related
Oct 5, 2015
I am joining on two tables, and returning the values that do not exist in #topoftheline -- well now I see that their are multiple calltimes so I want to return ONLY the most recent call time. What would I change in my syntax to only return that most recent datetime?
Create Table #topoftheline
(
callreceived datetime,
callerfirstname varchar(100),
callerlastname varchar(100),
callnotes varchar(4000)
)
[Code] ...
View 3 Replies
View Related
Jul 27, 2015
below is the sql query i am trying to write and i receive an error saying Incorrect syntax near the keyword 'Between'.
Update dbo.Claims_Primary_Adjuster_Test_Sujith
SET
Claims_Primary_Adjuster_Test_Sujith.Supervisor = Claims_Internal_Transfer.Supervisor,
Claims_Primary_Adjuster_Test_Sujith.Office = Claims_Internal_Transfer.Office,
Claims_Primary_Adjuster_Test_Sujith.Specialty = Claims_Internal_Transfer.Specialty
from dbo.Claims_Primary_Adjuster_Test_Sujith
Left Outer Join Stg.HS_DW_RV_ClaimsON Claims_Primary_Adjuster_Test_Sujith.Claim_Number = HS_DW_RV_Claims.ClaimNumber
[code]....
how to modify my code such that it works for my condition?
View 10 Replies
View Related
Apr 29, 2015
I have these two queries I would like to join, however the later is a grouped query how can I join it with the first query? Has to be joined on EventId. The second query is a total table scan.
SELECT AH.EventID,
AH.TechnicalAddress, AH.AlarmAlias, AH.AlarmPath as [OrgAlarmPath], AH.AlarmCounter as AlarmCount, AH.EventDateTime as EventTime,
AH.[Priority], AH.AlarmMessage, AH.EventText, AH.CallListName, AH.AlarmReadDate as EndTime,
AH.alh_EventEndedUserRemark as [EndRemark] --, SUM(seconds) here, and AlarmSessions here
FROM AlarmHistory AH
[Code] ...
2)
WHERE ia.EventTypeId = 0
group by ia.EventId
order by EventId desc
View 4 Replies
View Related
Sep 3, 2006
I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure. Example data:ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level------------------------------------------------------------------------------------------------------------------------------------------------1 Custom Furniture 0 02 Boxes 0 03 Toys 0 04 Bedroom 1 Custom Furniture 15 Dining 1 Custom Furniture 16 Accessories 1 Custom Furniture 17 Picture Frames 6 Accessories 28 Serving Trays 6 Accessories 29 Entertainment 1 Custom Furniture 110 Planes 3 Toys 111 Trains 3 Toys 112 Boats 3 Toys 113 Automobiles 3 Toys 114 Jewelry 2 Boxes 115 Keepsake 2 Boxes 116 Specialty 2 Boxes 1Desired output:Custom Furniture Accessories Picture Frames Serving Trays Bedroom Dining EntertainmentBoxes Jewelry Keepsake SpecialtyToys Automobiles Boats Planes Trains
View 4 Replies
View Related
May 21, 2014
An example of what I am talking about is the employee table in the Adventureworks database. This has employeeID and then ManagerID, ManagerID just being the EmployeeID of the person whom the original reports to.
I know the queries for querying this type of data and even making recursive common table expressions. What I cannot seem to find is how one goes about BUILDING said table. I see all sorts of examples where people are just doing INSERT table VALUES () manually to load the table. The problem is, I need to create a table that has potentially thousands of records.
It will essentially be a dimensional map. Don't even get me started as to they why, I will just suffice to say that is what the client and project want . I have a process that will do this now, but it is not very dynamic and very hard coded. To me, there seems like there should be some sort of standardized methodology for handling this.
View 9 Replies
View Related
Jan 26, 2015
I have tables Companies, CompaniesDetails (the company branches), Addresses and Companies_Addresses.
The addresses table contain street and city while the Companies_Addresses has the keys for both companies and branches ,i.e., they are linked to Companies and CompaniesDetails via CompanyID and CompanyDetailID and to Addresses via addressID.
Companies_Addresses
id (PK)
companyID (FK)
companyDetailID (FK)
addressID (FK)
I am able to get the branch address at the moment with this code but I would like to get the company address as well using a single select statement.
Code:
SELECT DISTINCTAddresses.city as branchCity, Addresses.street as branchStreet
FROMCompanies
LEFT JOINCompaniesDetails AS cd ON companies.companyID = cd.companyID
LEFT JOINCompanies_Addresses AS c ON c.companyDetailID = cd.companyDetailID
LEFT JOINAddresses ON c.addressID = Addresses.addressID
WHERE Companies.name LIKE 'abc'
ANDCompanies.status_indicator like 'Current'
View 8 Replies
View Related
Dec 6, 2005
If a table gets joind with itself and then joined again with itself, is it possible to perform one kind of outer join from the third table to the second tabe and having the range of records in the second table limited to the joins between the first and the second table?
Join on RegionID Join on RegionID and City
Show records not qualifying for the joint
but limited to the scope of of records
established by the first joint
_______________ ________________ _______________
| | | | | |
| RegionID One | | RegionID One | | RegionID One |
|_______________| | City New York | | City New York|
|________________| |_______________|
_______________ ________________ _______________
| | | | | |
| RegionID One | | RegionID One | | RegionID One |
|_______________| | City New York | | City New York |
|________________| |_______________|
_______________ ________________ _______________
| | | | | |
| RegionID One | | RegionID One | | RegionID One |
|_______________| | City Buffalo | | City New York |
|________________| |_______________|
Plotin
View 3 Replies
View Related
Jul 19, 2007
hi friends can any body help fo rthe below issue:
stock
========
Item cost
pen150
pencil 150
stockdt
=========
Item qty
pen10
pen15
pencil 10
pencil 15
for the above two table of stock,stockdt the o/p should come like this
Item cost qty std.cost
pen 150 25 3750
pencil 150 25 3750
View 1 Replies
View Related
Nov 13, 2007
Hi guys,
This is regarding the joins...for instance I have three Tables A, B, C. And they are related to each other. If I am joining A->B,B->C then is it necessary to join A->C too? what happens if I omit join A->C? and what happens if I include join A->C too?
Thanks,
View 7 Replies
View Related
Sep 7, 2005
Hello allLet's say I have 1 table "contract" containing the following data:id year sales45 2005 10045 2004 9589 2005 25089 2004 27512 2005 42I want to make a table with one unique row for each id and then a column for2004 sales and 2005 sales, like this:select a.id, a.sales, b.salesfrom contract a, contract bwhere a.contract=b.contract(+)and a.year=2005and b.year=2004The rows for id 45 and 89 are shown perfectly. But id 12 is not shown at allbecause it doesn't have a record for 2004!! I don't know why 'cause Iouterjoined the tables.It works perfectly when I have two distinct tables for each year (forinstance contract_2005 and contract_2004). So the problem seems to be in thefact I like to join one table with itself.Someone has a solution for this?thanks!Maarten
View 2 Replies
View Related
Sep 20, 2013
What sort of script would convert a pre-existing table into the second below?
I only want to merge the columns with Primary_IDs 1111 & 3333 to have the same Secondary_ID values, without duplicating any similar Secondary_ID values between the 2 which I've marked in red below.
Code:
TABLE A
==========
Primary_ID | Secondary_ID
1111 | 1
1111 | 2
1111 | 3
1111 | 4
3333 | 1
3333 | 2
3333 | 10
3333 | 20
3333 | 100
3333 | 200
5555 | 12
5555 | 34
7777 | 56
7777 | 78
Code:
NEW TABLE A
==========
Primary_ID | Secondary_ID
1111 | 1
1111 | 2
1111 | 3
1111 | 4
1111 | 10
1111 | 20
1111 | 100
1111 | 200
3333 | 1
3333 | 2
3333 | 3
3333 | 4
3333 | 10
3333 | 20
3333 | 100
3333 | 200
5555 | 12
5555 | 34
7777 | 56
7777 | 78
View 11 Replies
View Related
Oct 1, 2013
I am trying to convert the following sql to linq
SELECT R.UserID,R.Cntrl_nbr FROM User R
WHERE (
R.REG_ID=
(SELECT MAX(B.REG_ID)
FROM User B
WHERE R.UserID = B.UserID )
The linq I am using is
var query = (from n2 in q1
where
n2.RegServiceTs == (q1.Where(c1 => c1.UserID == n2.UserID).Max(c2 => c2.REG_ID))
select n2).SingleOrDefault();
This is slow and is there a better way to do this?
View 1 Replies
View Related
Oct 8, 2013
I need the last year sales(lastyearsales), first name, last name and city from the following tables. The relation is as following:
SalesPerson<->Employee <- EmployeeAddress<-Address
SalesPerson<->Employee <-Contact
SalesPerson Table
1.SalesPersonID
lastyearsales
Employee Table
1.EmployeeID
2.ContactID
Contact Table
2.ContactID
FirstName
LastName
EmployeeAddress Table
1.EmployeeID
3.AddressID
Address
3.AddressID
City
And my answer:
Select: C1.firstname, C1.lastname, SP.lastyearsales, A.city
From: SalesPerson as SP join Employee as E1 on Sp.salespersonID = E1.EmployeeID
Right Join Employee as E2 on E1.EmployeeID=E2.EmployeeID join Address as A on E2.AddressID = A.AddressID
Right Join Employee Contact as C1 on E1.ContactID = C1.ContactID
View 2 Replies
View Related
Jul 16, 2014
In the following example, I have a "Person" table, and a many-to-many "Relationship" table which stores the r/ship between any two people:
[Person] [Relshp]
---------------- ---------------------
| PersonId PK|<-.-. | RelshpId PK |
| PersonName | '-| PersonId |
---------------- '--| RelatedToPersonId |
| RelationshipType |
---------------------
There are 2 FK constraints, linking
1. [Relshp].[PersonId] to primary key [Person].[PersonId], and
2. [Relshp].[RelatedToPersonId] to primary key [Person].[PersonId].
What kind of index structure would best support those FK constraints?
Would it be:
a) One combined index:
CREATE INDEX IX_Relshp ON Relshp (PersonId, RelatedToPersonId)
or
b) Two indexes:
CREATE INDEX IX_RelshpP ON Relshp (PersonId)
CREATE INDEX IX_RelshpR ON Relshp (RelatedToPersonId)
or
c) Two "mirrored" combined indexes:
CREATE INDEX IX_RelshpP ON Relshp (PersonId, RelatedToPersonId)
CREATE INDEX IX_RelshpR ON Relshp (RelatedToPersonId, PersonId)
View 3 Replies
View Related
Oct 15, 2006
Hi,
Sorry if this has been asked before, I looked through the FAQ but could not find an answer to the following.
I have a Project table which contains amongst other fields, a CreatedByID field and a LastModifiedByID field. Both these fields point to a User table. What I would like to do is get the two usernames from a query on the project table.
I know how to get one usename using the following Sql command but how do I get access to the second username ?
SELECT Project.Name,User.Username FROM Project,User WHERE Project.ID=@id AND User.ID = Project.CreatedByID;
I hope my ramblings make sense
Andy
Andy
View 2 Replies
View Related
Dec 19, 2007
use default pubs database in sqlserver2000.
use authors table and publishers table.
Write a query to list first name, last of all authors
and name of the publisher (if any) present in the same city
as the author. If no publisher is present in the city
where the author is located then the column should contain a
NULL value. If there is more than one publisher in the city
where the author is located, then the details of
the author are to be repeated for each publisher.
but there is no field match between authors table and publishers table.
View 9 Replies
View Related
Jul 23, 2005
My main table has the following structure:t1 (id_primary, id_secundary, name) i.e. [(1,1,"name1"), (2,1,"name2")]I want to join this table with the following second table:t2 (id_primary, id_secundary, value) i.e. [(1, NULL, "value1"),(NULL,1,"value2")]The join should first try to find a match on id_primary and only if thatfails it should find a match on id_secundary. Every row in t1 is matchedagainst a single row in t2.The following query works:selecta.name, isnull(b.value, c.value)fromt1 a left outer join t2 b on a.id_primary = b.id_primaryleft outer join t2 c on a.id_secundary = c.id_secundaryI'm wondering though if it would be possible to write a query that only usest2 once, since it actualy is quite a complex query that is calculated twicenow. Any ideas (besides using a temp table)?
View 3 Replies
View Related
Apr 29, 2008
I have query that takes 12 minutes to execute. The query uses around 9 tables but I have narrowed down the problem to one table that has over 65 million rows. The problem table has only 3 fields
FieldOne (PrimaryKey)
FieldTwo Varchar(3000)
FieldThree Varchar(3000)
The query uses the primary key of this table to perform the join. FieldTwo and FieldThree are only used as output parameters.
I noticed if I remove FieldTwo and FieldThree from the output (but still leave the table in the query), the query executes in 1 second. However if I include FieldTwo and FieldThree in the output, the query takes over 12 minutes to execute.
I cannot index FieldTwo and FieldThree because of the field size and I cannot reduce the size of the fields because of the data that needs to be stored in it? How can I index or do something similar to speed up the table look up.
View 1 Replies
View Related
