I am trying to write a query that does not use inner joins to see if it returns rows faster than a query using inner joins.
My trouble is that I don't know how to write the syntax for the 3rd table that I am comparing against.
Can I nest Exists in a Where clause?
If so how is the 2nd exists statement added to the query?
see my sample query below
================================================== ===
'With 2 tables
SELECT DISTINCT s1.ID, s1.SITE
FROM SITE_TBL s1
WHERE EXISTS (SELECT *
FROM DEVICE_TBL d1
WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D')
ORDER BY SITE
'with 3 tables this doesn't work
SELECT DISTINCT s1.ID, s1.SITE
FROM SITE_TBL s1
WHERE EXISTS (SELECT *
FROM DEVICE_TBL d1
WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D')
AND
(SELECT *
FROM BIG_TBL b1
WHERE d1.fqdn = b1.xyz)
ORDER BY SITE
================================================== ===
Thanks for the help
Jim
Hi.Like in subject. I know the varray and nested tables from oracle, and I'm trying to implement them in sql server. I've been googling for any information but with no result. Can somebody direct me ??Sorry for any english mistakesThanks for help
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1) begin select @errno = @errno | 1 end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE dbo.t1 EXISTS TABLE : nested iteration. Table Scan. Forward scan. Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1 SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01) WHERE f1 = @p1 ) SET ROWCOUNT 0
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name FROM... WHERE ... and calcd name in (@variable)
Hi,Please can somone help me with a nested SQL query. I have two tables please see belowTable 1CallIDEmployeeIDCallSummaryCallStatusTable 2CallHistoryIDCallIDDataAddedCallActionI would like to return the CallID, EmployeeID, CallSummary and CallStatus from Table 1, and also display the last CallAction from Table 2.This is a helpdesk database so a Call will have many CallActions i.e. Open, Held, Assigned Internal. How do I return the last CallAction Added against the selected CallID, I know I use the DateAdded but not sure about nested statements.The results I would like to return to the user would look like this:-Call ID: 1EmployeeID: 1Call Sumary: SQL ProblemCall Status: OpenCall Action (Last Action): Assigned Internal.
I want to write one query which will select multiple distinct records from one table For e:g Lets say in a table i have 3 fields name,tel_no,sex Now i want to list all the records which are distinct in each of these fields like distinct name,distinct address
Important Tables: Product (table of products) --ProductID --ProductName
ProductCategories (Associates a Product with one or more categories) --ProductID --CategoryID
Category (table of categories that a product may fall under) --CategoryID --CategoryName
Information:
Basically I have a product that falls into two categories. Therefore there are two records in the ProcuctCategories Table. I am trying to create a query that will find all products that are in categories 1 & 2.
Attempted Solution: SELECT * FROM Product WHERE (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =1)) AND (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =2))
This returned zero records though it should have returned the product that is in categories 1&2.
HiI have 2 tables. The first has employee information and the second haspayroll information. I need to find out people who are not in thepayroll but in the employee table.Since the payroll has multiple instances i have to filter it and findout for each payroll.I don't think i have explained it very well so here is the data set.hope someone can help me with this.Thanks in advancepritTbl EmployeePlanIDSSN100111111111110012222222221001333333333TblPayrolldetailIDNumPlanID SSN11001111111111110012222222222100122222222221001333333333Required RESULT required(Missing employees from payroll)IDNumSSN13333333332111111111
I am having trouble with what will surely be a simple query for you experts.I have 2 tables with inventory data.IMITMIDX contains the master item infoIMINVLOC contains location specific data such as quantity on hand at thatlocation.These tables have 2 commons fields, ITEM_NO and LOCI need to search the IMINVLOC table for any records where ITEM_NO and LOC donot match that in the IMITMIDX table.The following query give me zero records even though I can manually findsome records:SELECT *FROM IMINVLOC_SQL INNER JOINIMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_nowhere not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =IMINVLOC_SQL.loc)Any ideas?Thanks.
I have a NOT EXISTS Query that works, but I have been giving a new requirement from MGMT. They initially wanted to know if a record existed in the system. Now they want to know if one exists over certain time periods (i.e. last 24 hours, 48 hours, etc...)
The System:
This is an ASP.NET application that uses table1 and table2 as list items in a drop down list. Once a record is created in the system it puts a time date stamp on the record along with other fields. For reporting I only care about reporting on two fields. This will be used to show activity in the system. MGMT expects at bare minimum 3 entries per 24 hour period.
SELECT table1.Department, Table2.field2 FROM table1 INNER JOIN Table2 ON Table1.DeptID = Table2.DeptID WHERE (NOT EXISTS (SELECT Table3.Department, Table3.Field2 FROM Table3 WHERE Table1.Department = Table3.Department AND Table2.Field2 = Table3.Field2))
The above returns records where there are no entries in Table3 for table1.Department AND Table2.field2. I now have the requirement to do the same thing, but also to include records where table3.insert_date was in the last 24 hours, 48 hours, etc...
I have tried adding:
Table3.insert_date >= GetDate() - 1)
but it still only returns records with no entry at all.
SELECT table1.Department, Table2.field2 FROM table1 INNER JOIN Table2 ON Table1.DeptID = Table2.DeptID WHERE (NOT EXISTS (SELECT Table3.Department, Table3.Field2 FROM Table3 WHERE Table1.Department = Table3.Department AND Table2.Field2 = Table3.Field2 AND Table3.insert_date >= GetDate() - 1))
I have moved it all over the place in this query changed AND to OR and cannot get the desired result.
Hello Everyone,I am trying to create a query for the purpose of a nested repeater relation. The information needs to be pulled from one table. I have shortened the columns to the ones that are required.table - PagesIDPageNameParentPageIDSo, take the following example:ID 14, PageName - Service A, ParentPage ID = 6ID 15, PageName - Service B, ParentPage ID = 6ID 36 PageName - Client 1, ParentPage ID = 14ID 37 PageName - Client 2, ParentPage ID = 14ID 38 PageName - Client 3, ParentPage ID = 15ID 39 PageName - Client 4, ParentPage ID = 15 So, I want to create a query that will get my nested repeater to display as follows:Service A Client 1 Client 2Service B Client 3 Client 4What I have come up with so far is:SELECT * from tbl_Pages WHERE ParentPageID IN (Select ID From tbl_Pages)SELECT p.ParentPageID, p.PageName, p.ID FROM tbl_Pages pThe relation would be based off ParentPageID. I keep getting errors that either there is no unique value or the relation is null. What am I am missing here?
Hi,I'm probably missing something obvious (either that or doing this totally wrong).I'm trying to use a nested loop to generate the following results:Unit Day1 Day2 Day3 Day4 Day5Name1 25 45 89 54 76Name2 48 54 81 74 98What I have so far is this:WHILE @FCount < @TotalFoodUnitsBEGINSELECT (SELECT Unit FROM tbl_acc_FoodVenues WHERE UnitID = (@FCount + 1)) AS Unit WHILE @FDCount < @Days BEGIN SELECT (SELECT FdRevenue_a FROM tbl_acc_aud_SportsAudits WHERE AudDate = DATEADD(day, @FDCount, @pdStartDate)) AS Rev SET @FDCount = @FDCount + 1 END SET @FCount = @FCount + 1ENDAny suggestions please
Hi, I have the following scenario that I am not sure how to best tackle. Any advice or examples is appreciated.
I am creating a stored proc that requires a code to be passed to it. In return data gathered from 3 different tables will be returned. The big catch is that 1 of the tables resided in a differenct database.
So, here is the data layout.
Database 1, Table 1 contains the following fields: Job, CustID, ShipID, and ShipMethod.
So the first question is how should the stored proc look with an input parameter of "Job" and output of Job, CustID, ShipMethod, ShipID, Address, City, State, Zip, and CustomerName?
Secondly, which database should the stored proc reside?
Again, any advise, suggestions, pointers, etc. are appreciated.
Can anybody please tell me if a query such as this (Valid in MS Access) can work in SQL Server:
SELECT Description, Sum(Total) FROM ( SELECT Description, Total FROM Table_A UNION ALL SELECT Description, Total FROM Table_B UNION ALL SELECT Description, Total FROM Table_C ) GROUP BY Description
The group of unions work by themselves, but when I try to nest an outer query to do some a Summation(), I have syntax errors.
Any insight would be greatly appreciated. Thank you.
SELECT SUM (dbo.HOLDING.Shares_Par_Value_Qty * dbo.ASSET.Current_Prc) AS MarketValue FROM dbo.HOLDING INNER JOIN dbo.ASSET ON dbo.HOLDING.Property_Num = dbo.ASSET.Property_Num Group by dbo.HOLDING.Account_ID
Account_ID is the same in both the queries ie in both the tables Holding and Account.
I need the output like this
Select Account_Id, Branch_Cd, MarketValue from -------
But MarketValue should be calculated exactly in the above method. How do I combine these two queries. I need it asap. Help me out.
I have a database that contains a PERSONNEL table, a VISIT table, and a STARSHIP table. I am trying to generate a single column list of the personnel that are from Vulcan (PERSONNEL.PLANET) and all starships that have visited Vulcan (VISIT.PLANET). VISIT.SHIP and STARSHIP.REGISTRY columns contain the ships identifiers. How would I accomplish this? I am just beginning sql so please be nice ;)
I have the following result set #1 from the query below. As you can see, there are four different provider roles and four different physicians listed for the same case (acctnum). Is there a way I could add a nested select (or other method) which would allow me to list this case as one line item to appear in the manner of result set #2?
RESULT SET #1 (the results of the query I have now)
MRN ACCTNUM PTNAME AGE ADMDT DISCHDT LOS PROVIDER_CODE PROVIDER_ROLE PHYSNAME
12345 11111117777 DOE, JANE 48 Nov 29 2006 Nov 30 2006 1 10 ANE1 MILLER DR.
12345 11111117777 DOE, JANE 48 Nov 29 2006 Nov 30 2006 1 20 ADM MAY DR.
12345 11111117777 DOE, JANE 48 Nov 29 2006 Nov 30 2006 1 30 ATT SCHULTZ DR.
12345 11111117777 DOE, JANE 48 Nov 29 2006 Nov 30 2006 1 35 PRIN THOMAS DR.
RESULT SET #2 (this is how I desire the results to look)
MRN ACCTNUM PTNAME AGE ADMDT DISCHDT LOS ANE1PHYS ADMPHYS ATTPHYS PRINPHYS
12345 11111117777 DOE, JANE 48 Dec 13 2006 Dec 14 2006 1 MILLER DR. MAY DR. SCHULTZ DR. THOMAS DR.
Select
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(pt.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(pt.patient_sname)), '')
AS SRM_PatientName,
pm.PatientAge,
left(e.admission_date,11) as Admit_Date,
left(e.episode_date,11) as Disch_Date,
(CASE WHEN DATEDIFF(DAY, e.admission_date,e.episode_date) = 0 Then 1
ELSE DATEDIFF(DAY, e.admission_date,e.episode_date) END) AS LOS,
epi.PROVIDER_CODE,
epi.PROVIDER_ROLE,
pe.PERSON_NAME as physician_name,
From srm.episodes e inner join
dbo.PtMstr pm on pm.accountnumber=e.account_number inner join
srm.ITEM_HEADER ih ON ih.ITEM_KEY = e.EPISODE_KEY INNER JOIN
srm.PATIENTS pt ON pt.PATIENT_KEY = ih.LOGICAL_PARENT_KEY inner join
srm.CDMAB_PROV_EPI epi on epi.episode_key=e.episode_key inner join
srm.providers p on p.provider_key = epi.provider_key inner join
srm.person_element pe on pe.item_key = p.provider_key
Where e.episode_date is not null and pm.AnyProc like '%4495%'
Hello, i hope that somebody can help me. I have a problem with ms sql query. i have two tabels(wtmenSends and T1) in a database. i need pick up all email addresses from table wtmenSends but without those email addresses which are in table T1. I have written this sql query to get these Emails, but i get nothing by this query. Can somebody tell me, where is the problem? Thanks. select wtmenSends.Email from wtmenSends where not exists( select T1.* from( SELECT wtmenSends.Id, wtmenSends.Email, wtmenSends.IDMailing, wtmenSends.Title, wtmenSends.Firstname, wtmenSends.Lastname, wtmenSends.IDUser, wtmenSends.IdStatus, wtmenSends.IsSent, wtmenSends.DateSent, wtmenSends.wtobjIDClass, wtmenSends.wtobjDateCreated, wtmenSends.wtobjDateChanged, wtmenSends.wtobjUserCreated, wtmenSends.wtobjUserChanged FROM wtmenRobinsons INNER JOIN wtmenSends ON wtmenSends.Email NOT LIKE '%' + wtmenRobinsons.Filter AND wtmenRobinsons.IsDomain = 1) as T1 inner join wtmenRobinsons on wtmenRobinsons.Filter = T1.Email)
I am trying to clean up an ugly query that's based on trying to find items that exist in one table but not the other. My tables are like this: ITEMSitemID,itemName,itemDescriptionetc... ORDERITEMSOrderLineID,OrderID,itemID ORDERSOrderID,OrderCompleted Currently my query looks something like this: Select Items.* from ITEMS where not exists (select 1 from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID where ORDERITEMS.itemID=ITEMS.itemID and (ORDERS.OrderCompleted=1)) So this query is looking for ITEMS what don't have a corresponding entry in the ORDERITEMS table. As I understand it this is pretty inefficient as it is going to be executing the sub query in the Not Exists statement for each entry in the ITEMS table. Is the preferred method to do something along the lines of somehow making the sub query into a derived table and doing a left or right join? Thanks for reading! Ryan
I've been presented with a task to do a query similar to the followingand I was curious as to what the quickest query would look like.Anyone have any ideas??Some_Id Value1 A1 B1 C2 C2 A2 B3 B3 C4 C5 Q5 C5 R6 T7 P7 BThe problem is that I want to select one record for each ID. If arecord with the value of 'A' exists, then I want to select that recordfor that ID. If not, I want to select the record with the value 'B'for that ID if it exists. Otherwise, just give me the first record forthat ID that exists. The result set would look like this:Some_ID Value1 A2 A3 B4 C5 Q6 T7 BThanks for your input!
In a SP I need to know if certain records already exist but the query is parameter dependent so I can't code IF EXISTS (SELECT ...) because the proper select must be calculated. Using EXEC (@CalculatedQuery) IF @@ROWCOUNT = 0 Puts the results of @CalculatedQuery into my SP result set. This is highly undesireable.
I am using SQL Server 2008 - and what I want to do is set my variable @dh. If the @startDate and @endDate falls into the criteria for my if exists statement, I want to set @dh equal to datediff(h, logontime, logofftime) BUT if that criteria is not true, I want to set @dh = 24. How can I do that?
How to configure sqldatadapter with query like "select name ,id from tlb1 where id in (select id from tlb2 where dept=@dept)" Is the nested subquery is not allowed while configuring sqldaadapter? Swati
I need to create a query to list all the subfolders within a folder.
I have a database table that lists the usual properties of each of the folder.
I have another database table that has two columns
1. Parent folder 2. Child folder
But this table maintains the parent child relationship only to one level.
For example if i have a folder X that has a subfolder Y and Z. And Y has subfolders A and B. and B has subfolder C and D and C has subfolder E and F
The database table will look like
parentfolder child folder X Y X Z Y A Y B B C B D C E C F
I want to write a query which will take a folder name as the input and will provide me a list of all the folders and subfolders under it. The query should be based on the table (parent - child) and there should not be any restriction on the subfolder levels to search and report for.
I have been banging my head to do this but i have failed so far. Any help on this will be highly appreciated.
I'd appreciate some help with the issue below - my SQL is a bit rusty and was never that hot to be frank. I'm using SQL Server 2000 (although have a test box with 2005 Express also). I've trawled MSDN and a few forums but can't find the solution (maybe I don't know what I'mm looking for!), so any help would be marvellous...
I have a table with a field called 'IRV' containing a string of comma-separated values. I want to be able to query a point in that string and count the number of times a given value appears. So...as an example, I want to count how many times '1' appears at position 7 in the IRV. I can create SQL to do this as follows:
SELECT COUNT(X) AS is1 FROM myIRVtable WHERE (SUBSTRING(IRV, 7, 1) = '1')
So far so good. However, it is also possible that the value at position 7 in this string could be '2' (or '3', or '4', etc) - and rather than re-running the query again and again to get these values, I'd like to do it in one hit.
How can I combine all this together - anyone have any brilliant solutions?
I have a table that refers to itself, and I need to loop through it to get the total number of entries in the structure with an object_type of 40 this is the function I came up with to do this. The problem is that the nested function doesn't seem to get called.
Code Snippet using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Collections.Generic; using Microsoft.SqlServer.Server;
public partial class StoredProcedures { private struct PerformanceCriteriaCounter { public PerformanceCriteriaCounter(int id, string objecttype) { Id = id; Object_Type = objecttype; } public int Id; public string Object_Type; }
[Microsoft.SqlServer.Server.SqlProcedure(Name = "usp_PerformanceCriteriaCounter")] public static int CountPerformanceCriteria(int ParentID, int SourcePosition) { using (SqlConnection conn = new SqlConnection("context connection=true")) { int Counter = 0; int Counter2 = 0; SqlContext.Pipe.Send("Called with ParentID=" + ParentID + " SourcePosition=" + SourcePosition); conn.Open(); using (SqlCommand myCommand = conn.CreateCommand()) { myCommand.CommandText = @"SELECT CompetenceLearningObject.Object_Type, CompetenceLearningObject.LearningObject_ID FROM CompetenceLearningObjectParent INNER JOIN CompetenceLearningObject ON CompetenceLearningObjectParent.LearningObject_ID = CompetenceLearningObject.LearningObject_ID WHERE (CompetenceLearningObjectParent.Approved = 1) AND (CompetenceLearningObject.Archived = 0 OR CompetenceLearningObject.Archived IS NULL) AND (CompetenceLearningObject.Approved_Status = 1) AND (CompetenceLearningObjectParent.Parent_ID = @ParentID) AND (CompetenceLearningObjectParent.Position_ID = @SourcePosition)"; myCommand.Parameters.AddWithValue("@ParentID", ParentID); myCommand.Parameters.AddWithValue("@SourcePosition", SourcePosition); List<PerformanceCriteriaCounter> loList = new List<PerformanceCriteriaCounter>(); SqlDataReader reader = myCommand.ExecuteReader(); while (reader.Read()) { SqlContext.Pipe.Send("Adding item"); loList.Add(new PerformanceCriteriaCounter(int.Parse(reader["LearningObject_ID"].ToString()), reader["Object_Type"].ToString())); } reader.Close();
I am trying to limit a result set by ROW_NUMBER. However, I am having problems getting it working.
The following query works fine, and I get a result set with PollID, AddedDate and RowNum columns.
SELECT *, ROW_NUMBER() OVER (ORDER BY Results.AddedDate DESC) AS RowNum FROM
( SELECT DISTINCT p.PollID, p.AddedDate
FROM vw_vs_PollsWithVoteCount p
JOIN vs_PollOptions o ON p.PollID = o.PollID
) AS Results
However, as soon as I add a WHERE condition:
SELECT *, ROW_NUMBER() OVER (ORDER BY Results.AddedDate DESC) AS RowNum FROM
( SELECT DISTINCT p.PollID, p.AddedDate
FROM vw_vs_PollsWithVoteCount p
JOIN vs_PollOptions o ON p.PollID = o.PollID
) AS Results
WHERE RowNum BETWEEN 1 AND 10
The query fails with an ' Invalid column name 'RowNum' ' error.
I have tried using 'Results.RowNum' but I get the same problem.
I don't understand what the issue is. The result set has a column headed 'RowNum' so why can't I apply a WHERE clause to this column? I can apply WHERE to the PollID column, for example, with no problem.
I want to only go after Distinct email addressess that contain an @ symbol. But then I want to return all fields. How do you correctly do that for Microsoft SQL Server?IF EXISTS (SELECT DISTINCT user_username FROM usr WHERE(user_username LIKE N'%@%'))
I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.
There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, how I can see all the records whether Yes or No:
Select distinct pg.grid_id [Price Grid], pg.grid_name [Grid Name], case when exists (Select c.grid_id from customers c inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5 where c.type = 'C' and dh.dticket is not null) then 'Yes' else 'No' end [Active]
From gridhdr pg inner join customers c on c.grid_id = pg.grid_id
I have added some SQL to an Access form which updates the dbo_BM_Map table when the user hits the Apply button. There is a temp table with various fields, two being "Chapter_No" and "Initial_Mapping_Complete" which the update is based on.
I want this update to only apply to chapters that only have one name in the "Initial_Mapping_Complete" column. If a chapter has more than one then the update should ignore it. The attached screengrab shows you. The update should ignore chapter 19 as there are two people (Jim and James) in the Initial_Mapping_Complete field. Here is my code.
pdate dbo_BM_Map inner Join Temp_Progression_Populate on dbo_BM_Map.Product_ID = Temp_Progression_Populate.Product_ID Set dbo_BM_Map.Initial_Mapping_Complete = Temp_Progression_Populate.Initial_Mapping_Complete Where dbo_BM_Map.Chapter_No = Temp_Progression_Populate.Chapter_No And Temp_Progression_Populate.Initial_Mapping_Complete in (Select count(Initial_Mapping_Complete), Chapter_No from Temp_Progression_Populate Group by Chapter_No Having Count(Initial_Mapping_Complete) = 1)
