Queries On Recursive Self Join Tables
Oct 24, 2007
Good morning!
Or good "whatever daytime you read this"!
SQL Server 2005 has this nice new feature Common table expression or CTE, which allows quite easy to define a "drill down" in recursive self join tables.
By recursive self join tables I mean this common example:
idPerson INT <--------|
idReportsTo INT ---------|
PersonName VARCHAR
A CTE to "go down" the tree from any entry point and find all subs to a parent entry is well documented. I managed to make myself a CTE and use it a lot!
What I find myself needing too often is:
a) Look up from a deep position and find the entry that is for example 3 steps above my reference in the branch
b) Look up from a deep position and find the one that is 2nd or 3rd level (absolute) from top of the tree in the branch
I did try quite some versions, but I cannot get it to work. Any idea how you do the "drill up" with a CTE or another SQL solution.
Of course performance is always needed, so I'd like to avoid the cursors I got it working with and use now. (It is not working good I admit...)
Cheers!
Ralf
View 7 Replies
ADVERTISEMENT
Jul 13, 2004
I have a following structure
table A
userid ReferedBy
BA
C B
Table B
Userid compID
AAlpha
Bself
Cself
now the scenario is :
the user A is from the company "Alpha"
he introduces user B, who registers in the system his company bcomes "self",
now B inturn refers user C who also registers in the system and his company is now again "self".
Now I need to generate a report of number of users that have registered under one company, for eg.
for the company "Alpha" no of users becomes 2 since A refered to two users and both of them have registered.
I m stuck with the query.
thanks in advance...
regards,
Harshal
View 14 Replies
View Related
Dec 1, 2006
Hello all,for computig graphs and its transitive closure I need recursive SQL-Queries. Best it will be fast.Now I heard of a new syntax in SQL: the WITH RECURSIVE-Clause. Which Database-Editions and versions support those recursive queries? Can anyone tell me where can I find more info about it? Thanks and best regardsyelmin
View 1 Replies
View Related
Jun 14, 2007
Hi, can anyone tell me how I can get implement sorting and paging using a recursive query?
I have a created a stored procedure (bit like the simple example below), but would like to add in sorting and paging using order by and row_number().
DECLARE @CategoryID intSET @CategoryID = 12;WITH CTE_Example (CategoryID, CategoryName, ParentID, Depth, RowNum)AS(SELECT CategoryID, CategoryName, ParentID, 0 AS Depth, Row_Number() OVER (ORDER BY CategoryName) AS RowNumFROM Categories WHERE CategoryID = @CategoryIDUNION ALLSELECT Categories.CategoryID, Categories.CategoryName, Categories.ParentID, CTE_Example.Depth + 1 AS Depth, Row_Number() OVER (ORDER BY Categories.CategoryName) AS RowNumFROM CategoriesJOIN CTE_Example ON Categories.ParentID = CTE_Example.CategoryID)SELECT * FROM CTE_Example WHERE RowNum BETWEEN @Start AND @End ORDER BY RowNum
I think the problem comes down to the Union, appreciate if someone can help.
Many thanks,
Matt
View 1 Replies
View Related
Feb 18, 1999
Dear fellows,
Can anybody tell me how can i apply recusive/Tree query using select
statement.
For example I've a table structure for an Organization as follows:
TableName: Employee_tbl
Fields: emp_id, emp_name, supervisor_id
emp_id emp_name supervisor_id
---------- --------------- -------------------
101 ZAFIAN
102 BRUNNER 101
108 CALLAHAN 102
105 RUSSO 102
110 SIM 102
103 DUELL 101
and so on
1. How can I get the above records in Hirarchical format starting from top
or from anywhere else in the hierarchy?
In Oracle it can be done as follows:
SELECT emp_id,emp_name,supervisor_id
FROM employee_tbl
CONNECT BY supervisor_id = PRIOR emp_id
START WITH supervisor_id is null;
Please reply me at the following address if possible:
faisal@visualsoft-inc.com
View 1 Replies
View Related
May 22, 2008
Hi,
Trying to update a single value within a table, thus eliminating nulls. Another words, if the value is NULL update it with the next preceeding non-null value. In this example, 1 should be CO, 2 should be CO, 6 should be CO, 8 should be TT, and 10 should be TT.
For example,
1 NULL
2 NULL
3 CO
4 CO
5 CO
6 NULL
7 TT
8 NULL
9 TT
10 NULL
Any ideas? Thanks.
View 2 Replies
View Related
Dec 3, 2006
CREATE TABLE EMPLOYEE(
Initialschar(3)Not Null,
EmpNamechar(25)Not Null,
CellPhonechar(20),
ExperienceLevel char(12)Not Null,
SupervisorInitials char(3)
CONSTRAINTEmployeePKPRIMARY KEY(Initials),
CONSTRAINTExperienceLevelCK CHECK (ExperienceLevel in
('Junior', 'Senior', 'Master', 'SuperMaster')),
CONSTRAINT SupervisorFK FOREIGN KEY (SupervisorInitials)
REFERENCES EMPLOYEE (Initials)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
INSERT INTO EMPLOYEE VALUES ('JLM', 'Joe Morrone', '(603) 489-2245', 'Master', NULL)
INSERT INTO EMPLOYEE VALUES ('BNF', 'Brenda Fowler', '(603) 467-8894', 'SuperMaster', NULL)
INSERT INTO EMPLOYEE VALUES ('JG', 'John Gromek', '(603) 678-9987', 'Junior', 'BNF')
INSERT INTO EMPLOYEE VALUES ('BEK', 'Barbara Kimball', '(603) 884-2312', 'Master', 'BNF')
Write a query which reports each employee's name and
-- experience level and the name of his/her supervisor
need help with this query,
the ouput is this ;
Employee ExperienceLevel Supervisor
------------------------- --------------- -------------------------
Barbara Kimball Master Brenda Fowler
John Gromek Junior Brenda Fowler
(2 row(s) affected)
Enhance your query to report all employees, whether or
-- or not they have a supervisor.
some help would be gratifull.
View 3 Replies
View Related
Jan 27, 2006
Hey all,
I am writing a procedure to perform a search against products in a website. It is a hierarchical setup, with websites, catalogs, category hierarchies, categories and products. So I need to get all the products that match the search, that are in categories that are in category hierarchies (category-to-category relationship), that are in catalogs that are in 1 website.
I can get the category that a matching product is in, but from there I need to recursive go up the ladder of category hierarchies until it reaches a point where the parent hierarchy is null, and then join that record with the catalogs that are in the 1 website. Here's what I thought might work:
Code:
SELECT ctlgs_CatalogCategoriesToProducts.*
FROM COMMERCE_PRODUCTS
INNER JOIN ctlgs_CatalogCategoriesToProducts ON COMMERCE_PRODUCTS.ITEMNO = ctlgs_CatalogCategoriesToProducts.ItemNo
INNER JOIN ctlgs_CatalogCategoryHierarchies ctlgs_CCH ON ctlgs_CatalogCategoriesToProducts.CategoryID = ctlgs_CatalogCategoryHierarchies.CategoryID
*** some recursive stuff***
WHILE (ctlgs_CatalogCategoryHierarchies.ParentHierarchyID IS NOT NULL)
BEGIN
INNER JOIN ctlgs_CCH ON ctlgs_CatalogCategoryHierarchies.ID = ctlgs_CCH.ParentHierarchyID
END
******
INNER JOIN ctlgs_Catalogs ON ctlgs_CatalogCategoryHierarchies.ID = ctlgs_Catalogs.RootHierarchyID
INNER JOIN ctlgs_CatalogsToWebsite ON ctlgs_Catalogs.ID = ctlgs_CatalogsToWebsite.CatalogID
INNER JOIN ctlgs_Websites ON ctlgs_CatalogsToWebsite.SiteID = ctlgs_Websites.ID
WHERE
ctlgs_Websites.ID = @website
AND COMMERCE_PRODUCTS.BLOCKED = 0
AND (
COMMERCE_PRODUCTS.ITEMNO LIKE @searchTerm
OR COMMERCE_PRODUCTS.DESCRIPTION LIKE @searchTerm
OR COMMERCE_PRODUCTS.DESCRIPTION2 LIKE @searchTerm
)
I've never used WHILE before, so I'm not sure what exactly you can put in there. Any advice would be greatly appreciated, thanks!
View 3 Replies
View Related
Jan 22, 2008
Hallo,
I have 2 Tables, a Customertable with "custid" as key and "custname" as customer name, and a second Table "CustomerHierarchy" with a "custid" field (which joins to customer) and a "custhierarchy" field which joins to "Custid" in the own table (CustomerHierarchy). When "custhierarchy" is Null, I'm on the top level of the hierarchy (the hierarchy builds Headquaters, Branches, Salesoffices, a.s.o).
I must construct a SSIS packet joining to each customer in the Customertable the "custname" for the highest hierarchy in the customerHierarchy Table. The custname is only in the Customertable.
How can I do this in a SSIS packet?
Thanks for your help
Hans
View 8 Replies
View Related
Apr 2, 2008
Hi,
I have been trying to solve this issue for around 8 hours, now. Can someoone help me?
I have two tables:
Employee (
EMPLOYEE_ID INT PRIMARY KEY,
NAME VARCHAR(25)
)
E.G.:
1 Mark
2 Tracey
3 Jim
STRUCTURE (
SUPERVISOR_ID INT,
SUBORDINATE_ID INT
)
E.G.:
1 3
2 3
I need to get a query that gives me the following query:
SUPERVISOR_ID, SUPERVISOR_NAME, SUBORDINATE_ID, SUBORDINATE_NAME
I think that the SQL is going to go recursive and I think that this can be done reasonably easily (sadly, I have struck out so far), but how?
Thanks in advance,
QuietLeni
View 7 Replies
View Related
Feb 29, 2008
Want to populate a navigation tree and looking for advise on how to implement the queries with (if possible) only one trip to the database.
To simplify I will give an example using made-up tables to populate MY-ARMY treeview:
Assume Table People:
PeopleID
ParentPeopleID
Name
Rank
Table General
PeopleID
<General's attributes>
Table Commander
PeopleID
<Commander's attributes>
Table Soldier
PeopleID
<Soldier's attributes>
If I build a recursive query to see MY-ARMY, is there a way to get each unique table's attribute?
I understand that the result is not uniform, therefore needs to be broken in different JOIN SELECTs, but then... how to avoid calling the recursive query multiple times?
Thx,
Uri
View 4 Replies
View Related
Aug 25, 2015
Msg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....
View 2 Replies
View Related
Oct 17, 2006
I have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows.
The table has columns like Commodity, Unit, Quantity, Value, Month, Country
A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx"
The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4.
It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage.
I wanted to know if there is an alternate way to pull the data from server ?
I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ??
OR
Can I write a script that creates a html files for each table for all input combinations save them ?
OR
Is there exists any other solution ?
View 1 Replies
View Related
Jul 23, 2005
I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
View 4 Replies
View Related
Dec 6, 2005
Hello:
What is the difference between:
select * from table1, table2 where ......
And
select * from table1 Inner Join table2 ....
Is there a performance difference or any other difference?
thanks
View 3 Replies
View Related
Jun 18, 2004
Hi,
Can you give the equivalent query for the following Oracle query :
SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
ANDso.pid (+) = 'SO'
ANDso.cde (+) = 'RW'
In the above query a constant value is used in JOIN, so I am not aware of how to use the JOIN clause for the above constant value ( 'SO' , 'RW' )
Upto my knowledge about JOIN query equivalents :
Oracle query :-
select *from test1 a, test2 b where a.sno(+) = b.col
Equivalent SQL Server query :-
select *from test1 a JOIN test2 b on a.sno = b.col
Hope the above is correct. Here my doubt is, if in the above query, b.col is replaced by a constant value say 'abcd' , then what will be the table name used in right of the JOIN clause. Or any other equivalent for this is available ?
Eg :-
Oracle query :-
select *from test1 a, test2 b where a.sno(+) = 'abcd'
Thanks,
Sam
View 3 Replies
View Related
Jun 18, 2004
Hi,
Can you give the equivalent query for the following Oracle query :
SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
AND so.pid (+) = 'SO'
AND so.cde (+) = 'RW'
In the above query a constant value is used in JOIN, so I am not aware of how to use the JOIN clause for the above constant value ( 'SO' , 'RW' )
Upto my knowledge about JOIN query equivalents :
Oracle query :-
select *from test1 a, test2 b where a.sno(+) = b.col
Equivalent SQL Server query :-
select *from test1 a RIGHT JOIN test2 b on a.sno = b.col
Hope the above is correct. Here my doubt is, if in the above query, b.col is replaced by a constant value say 'abcd' , then what will be the table name used in right of the JOIN clause. Or any other equivalent for this is available ?
Eg :-
Oracle query :-
select *from test1 a, test2 b where a.sno(+) = 'abcd'
Thanks,
Sam
View 1 Replies
View Related
Oct 10, 2006
Hi,
First, my knowledge of MDX is very limited :o
I am wondering if it is possible to return the result of 2 different queries as one. Similarly, like using UNION in SQL. I looked into the MDX UNION but it works on sets.
Basically, I have 2 queries; one with territory sales and one with region sales. I would like to return the results of all the territories followed by the sales of the region in one go.
Thanks in advance.
View 5 Replies
View Related
Jul 9, 2013
I am trying to do an inner join on two sub-queries, and I can't figure out what I am doing wrong here. I keep getting syntax errors:
Code:
SELECT * FROM
(SELECT SSN
FROM [1099_PER]
INNER
JOIN ( SELECT SSN, name4, count(*) from [1099_PER]
group by SSN, name4
[Code] .....
View 3 Replies
View Related
Mar 3, 2008
I get a wo_ID and want to query company data. I have the following tables, work_orders (has wo_ID, wo_name and install_id), install_ids (has install_id, comp_id) and customers (comp_id, company_name). The query process goes like this: I get a wo_ID and I need to find the install_id that corresponds with that wo_ID in that same work_orders table. Then take that install_id and find out the comp_id that corresponds with that install_id from the install_ids table. Finally take that customer_id and link it to comp_id in the customers table to find out company data. I need to join it multiple times to get my final data. I have a simple one that works right now with one join and using the install_id, but I don't know how to expand it with multiple joins. Thanks.
View 4 Replies
View Related
Mar 21, 2004
I have a page that will require several hundred update queries to be sent to the database. How much of a performance increase will i get by joining them all into one statement and sending them as a batch instead of running them one by one?
Thanks.
View 5 Replies
View Related
Jul 20, 2005
I am having a problem with a query,I am not sure if i would use a join or a subquery to complete thisproblem.I have two queries, and i need to divide one by the other, but i cantseem to get anytype of join to work with them.Here is the situation.I have a projectDB table that has a list of different projects foreach employee to work on.Each project has an employee assigned to it.The start date is null until the employee starts to work on it.I want to find how many percent of all their projects that eachemployee is working on.In other words:I want to divide query A by query B to see how many percent ofprojects each employee is working on.Query A count of projects that are being worked because they have adate per employee:SELECT employee, COUNT(employee) AS cntFROM projectDBGROUP BY employee, project_start_dateHAVING (NOT (project_start_date IS NULL)) //notice the NOTQuery B: Total amount of project per employee:SELECT employee, COUNT(employee) AS cntFROM projectDBGROUP BY employee, project_start_dateAny ideas?
View 4 Replies
View Related
Jul 20, 2005
hi thereanyone had an idea to join following 2 queries to 1????----- QUERY 1 ---------------------------------------------SELECT TMS_CaseF_2.Name AS TCDomain_0,TMS_CaseF_3.Name AS TCDomain_1,TMS.CaseF.Name AS TCFolder_2,TMS_CaseF_1.Name AS TCFolder_3,TMS.TestCase.Name AS TCName_4,TMS_TestCase_1.Name AS TCName_5,TMS.LogFolder.Name AS PlannedLogFolder_6,TMS.Log.Name AS PlannedLog_7,TMS.CaseResult.RecordedCaseName AS TCRecordedName_8,TMS.TestPlan.Name AS Plan_9FROM((((((((((TMS.Build INNER JOIN TMS.LogFolder ON TMS.Build.UID =TMS.LogFolder.Build)INNER JOIN TMS.Log ON TMS.LogFolder.UID = TMS.Log.LogFolder)INNER JOIN TMS.CaseResult ON TMS.Log.UID = TMS.CaseResult.Log)INNER JOIN TMS.TestCase ON TMS.CaseResult.TestCase =TMS.TestCase.UID)LEFT JOIN TMS.CaseF ON TMS.TestCase.Parent = TMS.CaseF.UID)LEFT JOIN TMS.TestCase AS TMS_TestCase_1 ON TMS.TestCase.Parent =TMS_TestCase_1.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_1 ON TMS_TestCase_1.Parent =TMS_CaseF_1.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_2 ON TMS_CaseF_1.Parent =TMS_CaseF_2.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_3 ON TMS.CaseF.Parent =TMS_CaseF_3.UID)INNER JOIN TMS.TestPlan ON TMS.TestCase.TestPlan = TMS.TestPlan.UID)WHERE (((TMS.LogFolder.Name) Like 'TR1%')AND ((TMS.Build.Name)='Planning_VD10A'))ORDER BY TMS.CaseF.Name,TMS_CaseF_1.Name,TMS.TestCase.Name,TMS_TestCase_1.Name;------------------------------------------------------------------ QUERY 2 ---------------------------------------------SELECT TMS.CaseResult.RecordedCaseNameFROM ((TMS.Build INNER JOIN TMS.LogFolder ON TMS.Build.UID =TMS.LogFolder.Build)INNER JOIN TMS.Log ON TMS.LogFolder.UID = TMS.Log.LogFolder)INNER JOIN TMS.CaseResult ON TMS.Log.UID = TMS.CaseResult.LogWHERE (((TMS.LogFolder.Name) Like 'VD%')AND ((TMS.Build.Name)='VD10A IT_APP'));
View 1 Replies
View Related
Jan 25, 2008
Hello all,
I have been using T-SQL for a while now although the majority of my work required relativley simple queries.
I just need to know is there a way to JOIN the results of several SELECT queries, maybe through the use of functions??
A reference to any online article would be most helpful.
Cheers,
Sean
View 6 Replies
View Related
Jul 24, 2015
I have recently started working on Sql server management studio. I have been using MS access in the past. To link results of 2 queries in MS access I would open the query wizard and it would show me the list of saved queries and then I could join them as regular tables. Im trying to look for this option in management studio. When I open query designer in management studio I am only given the option to add existing tables, how can I add existing queries?
View 2 Replies
View Related
Feb 21, 2007
We have the follwoing environment
Sql2005 64 bit edition standard edition servers
Sql2000 Sp3 enterprise edition servers
when we try to access a table in sql2000 from sql2005 using linked server, the query also uses inner joins and max()
it gives the follwoing error
"Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'Col1017'."
The query looks something like this
select *FROM [X.X.X.X.].HRDE.dbo.PS_HX_LVE_FRM_SRCH A, [X.X.X.X.].HRDE.dbo.PS_NAMES B
WHERE A.EMPLID = B.EMPLID
AND A.HX_LEAVE_STATUS = 'PND'
AND B.EFFDT IN (SELECT MAX(EFFDT) FROM [X.X.X.X.].HRDE.dbo.PS_NAMES WHERE EMPLID = A.EMPLID)
View 4 Replies
View Related
May 4, 2015
Required Output
Country Category
Internet Sales Amount
Internet Order Count
[code]....
I need to perform a SQL kind of Cross join to get the Total Count as a column along side as there are .net code and json structures defined that expects output in a certain format.
View 2 Replies
View Related
Aug 17, 2007
Hi guys,
I'll appreciate any help with the following problem:
I need to retrieve data from 3 tables. 2 master tables and 1 transaction table.
1. Master table TBLOC contain 2 records :
rcd 1. S01
rcd 2. S02
2. Master table TBCODE contain 5 records:
rcd 1. C1
rcd 2. C2
rcd 3. C3
rcd 4. C4
rcd 5. C5
3. Transaction table TBITEM contain 4 records which link to 2 master table:
rcd 1. S01, C1, CAR
rcd 2. S01, C4, TOY
rcd 3. S01, C5, KEY
rcd 4. S02, C2, CAR
I use Left Join & Right Join to retrieve result below (using non-ASNI method) but it doesn't work.
Right Join method:
SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A RIGHT JOIN TBCODE B ON A.CODE = B.CODE
RIGHT JOIN TBLOC C ON A.LOC = C.LOC
GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE
When I use Non-ASNI method it work:
SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A, TBCODE B, TBLOC C
WHERE A.CODE =* B.CODE AND A.LOC =* C.LOC
GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE
Result:
LOC CODE ITEM
-----------------------------
S01 C1 NULL
S01 C2 NULL
S01 C3 CAR
S01 C4 TOY
S01 C5 KEY
S02 C1 NULL
S02 C2 CAR
S02 C3 NULL
S02 C4 NULL
S02 C5 NULL
Please Help.
Thanks.
View 3 Replies
View Related
Aug 10, 2015
I have two queries as below;
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours
FROM dbo.tblEventStaffRequired;
and
SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked
FROM tblStaffBookings
GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
View 4 Replies
View Related
Jul 22, 2015
I have an intermittent issue where some remote PC's occasionally fail to execute select queries that have a join or return multiple result sets, however simple one table select queries continue to work okay. When it does happen the PC's needs to be rebooted to get to work again. This may only happen some PC's while others continue to work away okay.
I am using a VB6 application and ADO to connect to the database and the error message I get is a General Network Error, Server Not Found when it fails to execute the query. I have ran SQL Profiler on the server and while simple select queries continue to run away okay, a query a join does not even seem to show up in the profiler. The program has been working fine for 15 years with 1000's of users and has only now become an issue on one site for a number of users. Have tried moving the database to a different server and swapping network cards on the local PC's but can't seem to find the cause. The processor and the memory don't seem to be under load, but I am not sure if there is something else in SQL that is causing it to hang under certain conditions.
There have been network analysts experts in to run scans on the network, but I have not had the results of this back yet. Other applications do not seem to be affected so if this analysis does not show up anything.
View 5 Replies
View Related
Nov 7, 2007
I am using SQL2005 (Installed on VISTA). It was running smoothly until today....for some reason I cannot access any tables in queries that I try to run in Management Studio. I can open the tables and see the information, but all queries I try to run result in the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBO.BEYLEVEL16'.
I tried to run simple select queries on other tables but I get the same error message. Do not understand, eveything was working fine yesterday.
Any suggestions greatly appreciated.
Thanks
View 9 Replies
View Related
Aug 14, 2007
I appologise if this is dumb to anyone but I need a lot of help. I had a large program with over 50 tables and over 100 queries written in access. I upsized to SQL into a server windows 2003. Everything was cool and I made all corrections such that the program was working fine. The program resided in a D: accessory drive which was going bad. I copied my program to a different drive and replaced the D drive and returned my program to the new drive. I amde sure all shares and securities were the same. Lo and Behold My program now is missing all the queries and tables and when I run it it says it is disconnected. What happened to my tables I am currentely sobbing right now.
View 3 Replies
View Related
Jun 18, 2004
I've got a large and growing database in SQL Server 7.0. I'd like to utilize a monthly stored procedure that will search various tables for records that are older than 3 months and copy this data out to corresponding history tables. Typically, most production queries are run only on the new data, but occasionally (like at year-end, for example), we will need to run some queries on data extending back 12 months -- that is, on data in the production tables AND in the history tables. Notwithstanding the fact that I've never done stored procedures*, I would like to know if it is possible to run a query that can search for data in both production and history tables at the same time. I know this sounds like a stupid question, but I read somewhere that doing this qould require some kind of Joining function that is complex...?
Thanks,
Whill96205
* Also, any good resources I could use for developing stored procedures?
View 4 Replies
View Related
