We are working on an application which aggregates news articles from various sources. The problem is, when querying the database, is that there are a lot of double results because some of the sources get their news from the same press agency. To clarify things a bit here's a link to an example:
As you can see, there are a lot of the same results. What we want is to filter out the double results irrespective of the source. In other words; when the query encounters the first result, display that and leave out the other same results.
I've tried filtering them out by using DISTINCT on the 'news header' / results, but that does not seem to work. Any ideas on how to get this done?
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist. My current SQL statement is as follows. SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))" So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated. Thanks, James.
I have an interface that allows the user to filter the data to be returned.
e.g. if the user selects @SeniorManager then the query should return all data related to this.
So if the user selects @SeniorManager and @Auditor then the query should return all data matching both @SeniorManager AND @Auditor.
FundID and ClientID are the ID's that link each table.
Am I correct in using UNION ALL below?? thanks.
Code Block SELECT SeniorMgr, null AS Auditor, null AS Legal FROM FundClient f INNER JOIN FundPortfolio p ON p.ClientID = f.ClientID INNER JOIN Staff s ON (s.ClientID = p.ClientID) AND (s.FundID = p.PortfolioID) AND (s.ClientID = f.ClientID) WHERE s.ClientID = @ClientID AND s.FundID = @FundID AND (@SeniorManager = null Or SeniorMgr = @SeniorManager) UNION ALL
SELECT null AS SeniorMgr, fa.Auditor, null AS Legal FROM FundClient f INNER JOIN FundPortfolio p ON p.ClientID = f.ClientID INNER JOIN (Auditor a INNER JOIN FundAuditor fa ON fa.AuditorID = a.Auditor) ON (a.ClientID = p.ClientID) AND (a.FundID = p.PortfolioID) AND (a.ClientID = f.ClientID) WHERE a.Auditor = @Auditor AND a.ClientID = @ClientID AND a.FundID = @FundID
UNION ALL
SELECT null AS SeniorMgr, null AS Auditor, fl.Legal FROM FundClient f INNER JOIN FundPortfolio p ON p.ClientID = f.ClientID INNER JOIN (Legal l INNER JOIN LegalCounsel fl ON fl.LegalID = l.Legal) ON (l.ClientID = p.ClientID) AND (l.FundID = p.PortfolioID) AND (l.ClientID = f.ClientID) WHERE l.LegalC = @Legal AND l.ClientID = @ClientID AND l.FundID = @FundID
Stored Procedure ProblemThis is probably a simple problem but i would appreciate some help.I have a stored procedure that takes the order date time plus other information fromvarious tables but the information is being returned double:ie 4 rows are being returned instead of two. Can anyone see where i am going wrong?Many thanksMartinThis is the stored procedureALTER PROCEDURE dbo.SP_RetrieveOrdersASSELECT distinct OD.DateCreated, O.OrderID,O.UserID,O.OrderTotal,O.Sent,O.Delivered,O.Paid,C.CustomerNameFROM Orders As O,Customers As C,OrderDetails as ODWHERE O.UserID=C.UserID And O.OrderTotal >0 RETURNThese are the results that are returnedDateCreated OrderID UserID OrderTotal Sent Delivered Paid CustomerName ----------------------- ----------- ------------------------------------ ---------------- ------ --------- ------ --------------- 18/10/2006 14:49:00 41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 18/10/2006 14:49:00 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill 18/10/2006 15:05:00 41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 18/10/2006 15:05:00 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill No rows affected.(4 row(s) returned)If I leave OD.DateCreated ie use ALTER PROCEDURE dbo.SP_RetrieveOrdersASSELECT distinct O.OrderID,O.UserID,O.OrderTotal,O.Sent,O.Delivered,O.Paid,C.CustomerNameFROM Orders As O,Customers As C,OrderDetails as ODWHERE O.UserID=C.UserID And O.OrderTotal >0 RETURNthen there is no problem. I get:41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill No rows affected.(2 row(s) returned)
I want to create a stored procedure that returns a list of records froma table. But depending on a userID value given only certain recordswill be returned that they have access to.I think this might be hard to do in a single SELECT statement becausethe user might also belong to a group that might have permission, etc.Can you do something like this pseudo code in a T-SQL procedure?DECLARE cur CURSOR FOR SELECT * FROM myTableOPEN curFETCH NEXT FROM curWHILE @@FETCH_STATUS = 0BEGINif( accessGranted(curRecord.id) ){ addRecordToResultSet() }else { ommitRecordFromResultSet() }END
Hey all, I've been searching for a while and haven't really found an answer for what I'm trying to do. Here it all is and someone can tell me if I'm going in the wrong direction to solve this. I have a textbox set to a parameter of "SearchString" and a DropDownList with the SelectedValue paramter of "FilterValue". The parameters work, I've tested that. So here is the problem. I am trying to use the "FilterValue" to pick the field that the "SearchString" searches with a LIKE statement (e.g. SELECT * FROM dbo.Units WHERE (@FilterValue LIKE '%'+@SearchString+'%')) This, I've found out, doesn't work. I have this setup with a DataLayer.vb in my App_Code directory handling the SQL calls, while the normal CodeBehind is in the typical "Default.vb" page. I will post code later as I don't have access to it right now, but if anyone can explain how a filter like this would work I would appreciate it! Thanks,
I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT) SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
ListingLink ID1 ID2 Active Date 9212007-10-22 17:47:31.230 9712007-10-22 17:47:32.137 9612007-10-22 17:47:32.540 9812007-10-22 17:47:33.010 3912007-10-22 18:31:15.980
I have this table of Data which will Link to topics together, Problem is I need a Query that will produce 5 Rows if the Input is ID 9. Or One Row if the ID was 2. Not Conditional just example inputs and outputs.
This will only give one side of the Results SELECT Listing.sTitle,Listing.iListingID FROM Listing INNER JOIN ListingLink ON listingLink.ID2 = listing.ilistingID WHERE ListingLink.bIsActive = 1 AND ListingLink.ID1 =9 OR ListingLink.ID2 = 9
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
Hi All, I am facing quotes problem. Without using the quotes my query is running fine, but I need to use IIF condition so for that I need quotes adjustment. I didn't figured it out how to adjust them, try several techniques but no success. I am using dotnetnuke. {IIF,"[frmradio,form]=text"," SELECT Docs.FileName, Dept_LegalLaw.MediaID, Dept_LegalLaw.ID, Dept_LegalLaw.LevelID, Dept_LegalLaw.LawID, Dept_LegalLaw.LawDate, Dept_LegalLaw.Agreement, Dept_LegalLaw.Name, Dept_LegalLaw.NameSearch, Dept_LegalLawType.LawType, Dept_LegalLaw.LawNo, Dept_LegalMinistries.RegID, Dept_LegalLaw.IssueNo, Dept_LegalLaw.Attachment, Dept_LegalLaw.Amendment, Dept_LegalLaw.Scanned, Dept_LegalLaw.Html, Dept_LegalMinistries.Description FROM OPENQUERY(LEGALDBSERVER, 'SELECT Filename FROM SCOPE() WHERE Contains('" @FilterAnyWrd ")' ) AS Docs INNER JOIN Dept_LegalLaw ON Docs.FileName = Dept_LegalLaw.FileName INNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegID INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawID ", " "} {IIF,"'[frmradio,form]'='title'"," SELECT MediaID, Dept_LegalLaw.ID, Dept_LegalLaw.LevelID, Dept_LegalLaw.LawID, LawDate, Agreement, Name, NameSearch, Dept_LegalLawType.LawType, LawNo, Dept_LegalMinistries.RegID, IssueNo, Attachment, Amendment, Scanned, Html, Dept_LegalMinistries.Description, Dept_LegalLaw.FileName FROM Dept_LegalLaw LEFT JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID COLLATE DATABASE_DEFAULT = Dept_LegalMinistries.RegID COLLATE DATABASE_DEFAULT INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID COLLATE DATABASE_DEFAULT = Dept_LegalLawType.LawID COLLATE DATABASE_DEFAULT WHERE @FilterLawNo AND @FilterLawID AND @FilterRegID AND @FilterIssueNo AND @FilterFromDate AND @FilterToDate AND @FilterNtContNew AND @FilterAgreement AND @FilterAllWrdNew AND @FilterExWrdNew AND @FilterAnyWrdNew ORDER BY [SORTTAG] ", " "} Thanks for any help
I'm an MDX newbie.. with some help I was able to create a query that returns accurate results. All I need to do now is filter for Completed Work value of greater than 0.
I have tried to use the filter wizard in VS2005, but I have never gotten the desired results.
Platform Info: SQL Server 2005 Standard VS2005
---------Start Statement---------------------------------------------------------- WITH
MEMBER [Measures].[Completed Work by WI on dt1] AS
I've got an MDX query I need to modify, but not quite sure how to proceed. I essentially know most of tye syntax, but am at a loss as to where the components of the syntax should appear in the query. Here is the original query:
Code:
WITH MEMBER [Employee].[FTE vs FL].[CF Total] AS AGGREGATE([Client Facing Disciplines]) MEMBER [Time].[Year - Half Year - Quarter - Month - Date].[Trailing 12 Months] AS AGGREGATE(LASTPERIODS(60, STRTOMEMBER(@SelectedMonthEx, CONSTRAINED))) MEMBER [Time].[Year - Half Year - Quarter - Month - Date].[YTD] AS AGGREGATE(YTD(STRTOMEMBER(@SelectedMonthEx, CONSTRAINED)))
[Code] ....
This is how I'm sure the two lines that say what should be excluded, I'm just not sure where they should go. I believe a FILTER statement is what I need, but not sure how to format it.
[Employee].[FTE vs FL].[Discipline].&[LOYALTY], [Employee].[FTE vs FL].[Discipline].[Department].&[OLSON PR Chicago]
Hello , I am Joining two tables and trying to filter the data based on some conditions. SELECT SERVICE_TICKET.SERVICE_TICKET_CODE,BuildingAddress.BuildingAddress, BuildingAddress.UserID,SERVICE_TICKET.PROBLEM_REPORTED, SERVICE_TICKET.CONTACT_PERSON, SERVICE_TICKET.STATUS FROM BuildingAddress INNER JOIN SERVICE_TICKET ON BuildingAddress.Customer_Site_Int_ID = SERVICE_TICKET.CUST_SITE_INT_ID WHERE (JoinTable.UserID = @parameter) AND (SERVICE_TICKET.SERVICE_TICKET_CODE LIKE 'HD' + '%') See in the query at the WHERE clause I am using JoinTable.UserID column actually I know that it is wrong . Could some one tell me how the query should be so that I can get the records from the join which matches the UserID column to a parameter. Hope that my question is clear. Thanks
So I have this query that is ignoring my date filter and for the life of me I can't figure out why. Was hoping some guru could explain it to me. Here goes:
This query ignores my date filter:
SELECT rcv.Name AS MachineName, r.JobId, j.Name AS JobName, r.CreateTime AS JCreateTime, rsv.Name AS JResultStatus, rpv.Path + rpv.Name AS ResourcePool, rcvv.ResourceConfigurationVal AS Dimension
FROM dbo.Result_View AS r INNER JOIN dbo.ResourceConfiguration_View AS rcv ON r.ResourceConfigurationId = rcv.Id INNER JOIN dbo.Job_View AS j ON r.JobId = j.Id INNER JOIN dbo.ResultStatus_View AS rsv ON r.ResultStatusId = rsv.Id INNER JOIN dbo.Resource_View AS rv ON rcv.ResourceId = rv.Id INNER JOIN dbo.ResourcePool_View AS rpv ON rv.ResourcePoolId = rpv.Id RIGHT JOIN dbo.ResourceConfigurationValue_View AS rcvv ON rv.LatestResourceConfigurationId = rcvv.ResourceConfigurationId LEFT JOIN dbo.Dimension_View AS d ON rcvv.DimensionId = d.Id
WHERE (r.CreateTime > DATEADD(DAY, -15, GETDATE())) AND (rcv.Name LIKE 'PNP%') AND (d.Id = 859) OR (d.Id = 860)
If I comment out the last two joins and associated select/filters, all of the sudden the date filter works again. From everything I have read, the joins are supposed to be processed BEFORE the filters are applied in the virtual table. My DB goes back a number of years and contains millions of records. W/O the date filter, the query takes a very, very long time to run.
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1 FROM testsupplierSCNCR I am getting the result of 01/01/2007
Hello All, Can someone please help me with this problem. I have created additional profile properties for my users in the aspnet_profile table. One of the properties is named 'Division'. The division property is how I segregate users, for example, my company has multiple divisions and, the information that is displayed is dynamic according to which divison the user belongs to. Amost all of my stored proceedures are filtered by checking the value of Profile.Division of the logged in user. This system works great. The problem is, now I am working on user management and I need to create a Gridview that will display all of the users for a particular division but, I have no idea how to filter a query against the aspnet database to get the results I am looking for. Here is a small example:SELECT aspnet_Users.UserName, aspnet_Profile.PropertyNames, aspnet_Profile.PropertyValuesStringFROM aspnet_Profile INNER JOIN aspnet_Users ON aspnet_Profile.UserId = aspnet_Users.UserId******** Here is the part I need help with *********************WHERE PROFILE.DIVISION = KOr, can it even be done? Can you filter a query on the value of a profile property?Anyone have any ideas?Thanks in adavance for your help
Generally, on any screen, we design filter screen by allowing user to identify range or one value to search. But sometime in some screen, It will be more convenient for user if user can identify No matter how value to search.
For example On screen which have information of people in any province. So user would like to search it by identify no matter how value to search. There are check box of any province on filter part which enable users choose it.
Hence, if sometime user choose (by clicking on checkbox) 3 provinces : LA, Michigan, WachingtonDC to see description only 3 chosen province. and sometime user choose (by clicking on checkbox) 2 provinces : LA, Michigan to see description only 2 chosen provinces.
Please give me any idea for create Stored Procedure or any tecnique to complete my idea....
Hi everybody have this query that list all the duplicate records meaning the occurence of pubinfid in more than one row. This gets me fine. Iwant thought to insert a where clause that further filters the result where pubid between 30 and 33. I tried placing it after the FROM CLAUSE BUT DOESN'T GIVE me what I want it still includes records other than the between clause. Also I tried placing it after the HAVING clause but it includes records which has only one count.. Where could I place the where clause or is ther an alternative for this.
SELECT a.pubinfid, a.pubid, a.pubcount FROM pubssubscribe AS a INNER JOIN (SELECT pubinfid FROM pubssubscribe GROUP BY pubinfid HAVING (COUNT(*) > 1)) AS b ON a.pubinfid = b.pubinfid
I need to find a 12-digit number in a column called "SequenceNumTxt", in a VERY long table.I can query the first n lines but need to add something like "WHERE [SequenceNumTxt]="123412341234".The above does not work, gives me a syntax error; what am I doing wrong?
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example: create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example: create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way: create procedure ABCasbegin /* some checks and expressions here ... */ -- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info())) select ... from A inner join B on (A.ID = B.REF_ID) where ... and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables. For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
I have a simple ASP.NET application that allows the user to enter a Query in a TextArea and submit that to the database. Queries that contain Integer, String or other filters seem to work fine, but when the Query contains a Date or DateTime I have problems. Here is an example that works fine in Query analyzer but fails when executed in ASP.NET. "select top 10 * from Subscribers where StartDate < '09-03-2002'" In Query Analyzer no problem, in ASP.NET I get this error... Line 1: Incorrect syntax near ';' However if the Query uses '=' instead of a '<' or '' then it works without error "select top 10 * from Subscribers where StartDate ='09-03-2002'" Any help would be appreciated
how to add a date range filter to my database query. I want to be able to enter a date into ("E,6") and have VBA pull data >,= to that date range according to my "OPENED_DATE" dates. My code is pasted below:
I need to return 1 ip address for each machine. I can easily achieve this with using group by and return max(ipaddress) however I would like to filter out specific range '192.168.%' but only want to do that if machine has more then 1 ip address. If the machine has only 1 ip address then it can return it even if its '192.168.%'
below is test table to show sample data
CREATE TABLE dbo.[testip]( [machineid] INT NULL, [ipaddress] [varchar](20) NULL ) GO INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (1,'155.119.1.22') INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (1,'192.168.1.5')
I currently have a query in an access database that filters the records in a Sql Linked table for their user ID. So basically i use Environ("UserName") to filter for a field that contains their user name. I want to secure this and put it on the SQL Server so there is no way anyone can see any records other then the ones that are filtered for them. Does anyone know how to do this? I think it will be some SQL query.
Please refer to the below query. I want to filter inner join rows based on outer query column value (refer to bold text).
SELECT M.MouldId, SI.StockCode, MI.Cavity, MI.ShotCounter, CQ.SumOfCastedQty as CastedQty, MI.CounterStartup FROM MouldItem MI JOIN (SELECT JD.MouldId, JC.StockCode, SUM(JS.CastedQty) AS SumOfCastedQty FROM JobCasting AS JS INNER JOIN JobCreationDet AS JD ON JS.JobDetId = JD.Uniid INNER JOIN JobCreation AS JC ON JD.JobIdx = JC.Uniid
