Best Way For A Set-based Query
Jul 23, 2005
I have a database where each row has a set of keywords separated by
commas eg .
ROW 1 ID,<other columns>,"green,hat,man,boat"
ROW 2 ID,<other columns>,"green,boat,seaside"
ROW 3 ID,<other columns>,"red,hat"
I want users to be able to find similar records to the one they are
looking it (this is art BTW). ie if they are looking at row 1 and
click "Find Similar Works of Art" I want to do a query where I find
records that have a commonality of keywords and obviously the ones
that match the most keywords should be shown first. The keywords are
currently just stored comma-separated in a single column but I am not
attached to this. Nor are they guaranteed to come in any order (ie
they are not alpha sorted) but they could be.
Number of records is around 15000 on a hosted server.
Any tips for best query/algorithm/data storage to achieve this? or
references to useful sources?
Thanks,
RB
View 3 Replies
ADVERTISEMENT
Nov 7, 2007
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
View 1 Replies
View Related
Jun 5, 2008
I have a vb.net page that I need to display a list of employees who work in a specific office, based on a MatterID passed in a query string. But, I don't know how to get a value returned from one sql statement into a second. Here's what I'm trying to do...
From the QueryString, we know that the MatterID = 4 ( xxx.aspx?MatterID=4)
Knowing that the Matterid=4, I query the database to get the OfficeId for that MID (Select OfficeID from tMatter where Mid=4) ~This returns an OfficeID of 6
So, then I need to do another query to get the employees where OfficeID = 6 (Select EmployeeID from tEmployees where OfficeID = 6)
How do I do these in one query, or how do I use the Calculated Value for the OfficeID in the 2nd statement?
View 3 Replies
View Related
Aug 2, 2012
linking two tables together to get an end result
find below the code i have used
The first part of the query provides me with the info i need
SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
[Code].....
View 1 Replies
View Related
Nov 2, 2004
Hi everyone -
I have a simple query that will return a result set that
has a unique value in it (primary key) -
I would like to cycle through each of these values
returned from the query, and create a new row
in another table.
I do not want to use a cursor -
The first query is pretty simple....
select p_id from table1 where active=0
the query i need to execute for each of the p_id that
is returned in the first example is....
insert into table2 (p_id_field, text_field) values (@p_id, 'text')
the @p_id is filled in from the primary query...
please advise
thanks
tony
View 1 Replies
View Related
May 29, 2006
hello,
I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group
Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales
Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...
Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...
Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: 1,2,3,4,5,6,7,8,...
Thank you
View 10 Replies
View Related
Mar 19, 2007
i've two table Rate and Topic
Sample Table
Table name==>Topic
---------------------
Topic|TID|
-----|---|
hddgg|1
trgsd|2
vnvnv|3
sdfsd|4
dfsdf|5
dsdff|6
........
........
-----------------
above table TID is primary key
next table
Table Name==>Rate
-------------------
RID|TID|Rate
---|---|--------
1--|2--|4
2--|5--|3
3--|2--|3
4--|2--|2
5--|1--|1
6--|2--|1
7--|2--|2
8--|5--|3
9--|1--|4
.........
........
above table RID is primary key and TID is foreign key..
i need Top 3 Topic but based on Maximum Rate of value
any query.........
View 2 Replies
View Related
Apr 25, 2007
I have a SQL query that finds a value which I need to pass into an SSIS variable so I can use it later to set the filename for my data flow task. So far, I have been unsuccessful in determining how this is done.
google searches, etc seem to be fruitless for me, there are so many hits but none really seem to address what I want to do because there are so many things you can do with variables. Likewise the books online and the boks I have on SSIS also only give a very high level of info. I need some examples, if anyone could help I would appreciate it.
Here is the basic gist of my query:
exec SP_GET_FILE_NAME @file_name OUTPUT
I need to take the resulting @file_name and store it in an ssis variable. How?
View 1 Replies
View Related
Jan 5, 2008
in the following procedure i want to pass in a value and as a result of the value I want to change the actual body of the query:
example, I pass in the value 'TS2' and I now want my query to see if TS2MIN < 70
i think I am doing it right, but it is throwing an error saying that "Conversion failed when converting the varchar value 'TS2MIN' to data type int."
any idea on what i am doing wrong?
ALTER proc SPU_YearlyTemp 905,'TS2',70,254,2007
@SITEID INT,
@SENSOR VARCHAR(10),
@MIN INT,
@MAX INT,
@YEAR INT
AS
DECLARE @S AS VARCHAR(12)
SET @S=(
SELECT
CASE @SENSOR
WHEN 'TS1' THEN 'TS1MIN'
WHEN 'TS2' THEN 'TS2MIN'
WHEN 'TS3' THEN 'TS3MIN'
WHEN 'TS4' THEN 'TS4MIN'
WHEN 'TS5' THEN 'TS5MIN'
WHEN 'TS6' THEN 'TS6MIN'
WHEN 'TS7' THEN 'TS7MIN'
WHEN 'TS8' THEN 'TS8MIN'
END AS SENSORVALUE)
select top(10)*
into #tempMin
from sitecontroldataarchive
where siteid = @SITEID
and @S < @MIN --this is where the error occurs,
SELECT * FROM #tempMin
View 11 Replies
View Related
Jun 4, 2007
Hello Everyone,
How do you display a dataset query result in a textbox?
Thanks for the easy answer...
View 10 Replies
View Related
Jan 23, 2002
Hi guys,
Can anyone please tell me how to create a table based on a query.
Thanks,
Joe
View 2 Replies
View Related
Jun 5, 2000
Can i update a field in a table in 1 dbase based upon data in another table in another dbase.
could somebody let me know if this is possible.
Thanks
View 1 Replies
View Related
Sep 20, 2002
Using SQL Server 7 w/SP4.
I need to insert into another table the results of a query. Thought about using a temp table for the first query results, then querying those for the insert, but can't figure out how to pass the search results as a value.
Example:
SELECT ID, NAME FROM tbl2 WHERE ID BETWEEN 1 AND 50
IF @@ROWCOUNT > 0 BEGIN
INSERT INTO tblAudit
(ID, NAME)
VALUES
(@ID, @NAME)
END
Took a look at using SELECT INTO and INSERT INTO, but counldn't get those to work, either.
Thanks for any help.
View 3 Replies
View Related
Feb 12, 2002
I need to select certain rows based on a "datetime" column. I need to select rows from 8am yesterday until 8am today.
In Oracle I would use:
select * from foo where TIMESTAMP >= trunc(sysdate - 1) + 8/24 AND TIMESTAMP < trunc(sysdate) + 8/24.
This would start at 8am yesterday and end at 7:59am today.
How would I do this with T-SQL?
thank you,
Mark
Fiesta_donald@email.com
View 1 Replies
View Related
Feb 5, 2007
I'll try to simplify the problem as much as I can.
There's survey. It has different types of question answer pairs, but the problem is in Multiple Choices, Multiple Answers questions, where user can choose, for example 3 checkboxes from 5 at the web form, for the question.
I am emphasizing three tables:
tbl_questions
IDquestion (primary)
question (textual question)
tbl_answers
IDanswer (primary)
IDquestion (foreign,to table tbl_questions)
answer (textual answer)
tbl_results
IDresult (primary)
IDquestion (foreign, to tbl_question)
result (string based on CSV, may contain few IDanswers, because visitor may choose few checkboxes, for example it can be "4,6,7")
Inside those tables are data (I'll put here just two questions):
Example:
tbl_questions
IDquestion_question
1_________Which OS do you use?
2_________Which databases do you use?
tbl_answers
IDanswer_IDquestion_answer
1________1_________Windows
2________1_________Linux
3________1_________Mac OS
4________2_________SQL Server
5________2_________MS Access
6________2_________MySQL
7________2_________Oracle
8________2_________Other
9________2_________No, I do not use databases
tbl_results
IDresult_IDquestion_results
........
23_______1________1,2
24_______2________4,6,7
........
Is there a possibility to make query, using columns, but also the data inside the CSV format in results (varchar or char) column?
Result of the query would be:
IDquestion_IDanswer_answer
1_________1________Windows
1_________1________Linux
2_________4________SQL Server
2_________6________MySQL
2_________7________Oracle
Further queries based on this query should be able to make statistics (GROUP BY).
View 4 Replies
View Related
Feb 2, 2005
Does anyone know of a vendor who provides a packaged application that allows a user to pass T-SQL commands from a browser, to our IIS web server, to our SQL Server database?
Thanks!!!
View 1 Replies
View Related
Mar 3, 2005
Hi,
I don't know if anyone has encountered this before but here goes:
I've a select clause below:
result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'"
queryLocalName is an input field that allows the user to search for non-English characters in the database.
What I'm wondering is what kind of effect is the N in the where clause is having?
I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.
View 2 Replies
View Related
Nov 24, 2014
I have a table that is something like this:
Animal | Count
Dog |10
Dog |5
Dog |16
Dog |2
Cat |1
Cat |12
Cat |14
Cat |7
I need to write a query that will return something like this:
Dog_Count| Cat_ Count
10 |10
5 |5
16 |16
2 |2
View 4 Replies
View Related
Jun 21, 2008
Hello!
suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like
DEPT ABSENT
-----------------------------
Accounts 10
EDP Section 0 **
Admin 2
Stationary 0**
if no employee is absent in the department it has to display Zero
View 2 Replies
View Related
Jun 24, 2008
good day dudes! here's my 2nd questione for ye'all...
my databases:
workshift
- shiftid
- shiftname
- timestart
- timeend
- flexwindow
- status
employeeplottedsched
- employeeid
- month
- year
- day1shiftid
- day2shiftid
- day3shiftid
...
...
...
- day29shiftid
- day30shiftid
- day31shiftid
how can I query the plotted shift sched of employees for a particular day? like I would like to know what shift the employees were scheduled
from day1 to 15 of May 2008? the days that I would like to query would be dynamic so it can be day1 to 15 or day3 to day5 or just day20...
any of you guys nice enough to enlighten me?
U + U + D + D + L + R + L + R + Sel + Start...
View 9 Replies
View Related
Mar 27, 2015
I have 2 independent queries which run just fine, the 2nd one returns a dollar value. I need this dollar value as a column in my first query so that I can return it in a reporting program. The 2 queries are pasted below.
SELECT dbo.vw_tphillip_Customer_Inv.InvoiceNumber AS "InvoiceNumber",
dbo.vw_tphillip_Customer_Inv.TransactionType AS "TransactionType",
dbo.vw_tphillip_Customer_Inv.Date AS "Date",
dbo.vw_tphillip_Customer_Inv.DueDate AS "DueDate",
dbo.vw_tphillip_Customer_Inv.CustomerName AS "CustomerName",
[Code] ....
View 6 Replies
View Related
Mar 5, 2006
Supose I have the following select:
Select Name, Age, (select TelNums from Telephone)
From Person
The problem is that (select TelNums from Telephone) can return more than 1 record:
tel1
tel2
.
.
.
I was wondering how I can make a select to return the tel numbers like: 'tel1,tel2,tel2'
»»» Ken.A
View 3 Replies
View Related
Jul 23, 2005
SQL2K on W2KserverI need some help revamping a rather complicated query. I've given thetable and existing query information below. (FYI, changing thedatabase structure is right out.)The current query lists addresses with two particular types('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check eachcontact for address type 'AM39DK3KD9' and then (2) if the contact hastype 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if thecontact does not have that type then select types ('MN30D843J2','SC93JDL39D'). (Context - the current query selects two standardaddress types "Main" and "Secondary"; we've added new data and now havetypes "Alternate Main" and "Alternate Secondary". If the Contact hasAlternate addresses, I need to select those; if not, I need to selectthe standard addresses. There are other address types in use, so Imust specify which types to select.)Can anyone point me in the right direction?Thanks very much! jamilehCREATE TABLE [CONTACTS] ([CONTACT_X] [char] (10),[LONGNAME] [char] (75),[ACTIVE] [bit])CREATE TABLE [CONTACTADDRESSES] ([CONTACT_X] [char] (10),[ADDRESS_X] [char] (10),[ADDRESSTYPE_REFX] [char] (10),[ACTIVE] [bit])CREATE TABLE [ADDRESSES] ([ADDRESS_X] [char] (10),[ADDRESSLINE1] [char] (60),[ADDRESSLINE2] [char] (60),[CITY] [char] (20),[STATE] [char] (2),[ZIPCODE] [char] (11),[PHONE] [char] (10))CREATE TABLE [REFERENCETABLE] ([REFERENCETABLE_X] [char] (10),[ADDRESS_X] [char] (10),[DESCRIPTION] [char] (60))CREATE TABLE [MASTERTABLE] ([CONTACT_X] [char] (10),[RECORDTYPE] [char] (1),[ACTIVE] [bit])CREATE VIEW vw_CONTACTInfo_ListLocASSELECT CONTACTS.CONTACT_X, CONTACTS.LONGNAME,CONTACTADDRESSES.ADDRESSTYPE_REFX,Type_REFERENCETABLE.DESCRIPTION AS Type_DESCRIPTION,CONTACTADDRESSES.ADDRESS_X, ADDRESSES.ADDRESSLINE1,ADDRESSES.ADDRESSLINE2, ADDRESSES.CITY, ADDRESSES.STATE,ADDRESSES.ZIPCODE, ADDRESSES.PHONEFROM CONTACTS INNER JOIN CONTACTADDRESSES ONCONTACTS.CONTACT_X = CONTACTADDRESSES.CONTACT_X INNER JOINADDRESSES ON CONTACTADDRESSES.ADDRESS_X =ADDRESSES.ADDRESS_XINNER JOIN REFERENCETABLE Type_REFERENCETABLE ONCONTACTADDRESSES.ADDRESSTYPE_REFX =Type_REFERENCETABLE.REFERENCETABLE_XWHERE (CONTACTS.ACTIVE = 1) AND (CONTACTADDRESSES.ADDRESSTYPE_REFXIN('MN30D843J2', 'SC93JDL39D') AND (CONTACTADDRESSES.ACTIVE =1)) AND(CONTACTS.CONTACT_X IN(SELECT CONTACT_X FROM MASTERTABLE WHEREACTIVE = 1 AND RECORDTYPE = 'E'))
View 10 Replies
View Related
Sep 20, 2005
Hi,I have a SQL query:select products.name, products.notes, products.purchase_date,products.serial_no, products.total_value, products.product_code,products.quantity, products.product_group, products.lhs1_name,lhs1.department, lhs1.address1, lhs1.city, lhs1.countryfrom products, lhs1where products.lhs1_id = lhs1.idI want to insert the results of this query into a table called 'temp' in thedatabase. I used to copy and paste this into excel then import it but itdoesn't always work.Is there a way to do it all in a SQL script. Please be aware that myknowledge of SQL is fairly basic so please explain things clearly.Thanks,Darren
View 4 Replies
View Related
Mar 29, 2007
Newbie transiting from VBA to TSQL, using SQL Server 2005 Enterprise:Need help to do this:Open Table_AWITH TableADO UNTIL .EOFRead value from TableA.ColumnARun SQL Statement on TableB based on valueMove to the next recordLOOPENDHow do I do this in TSQL?Thanks,Bubbles
View 9 Replies
View Related
Mar 1, 2008
what's the best way to set user vars in ssis from a query. I have some components that set a from and to date in a table. I'd like to select those values right into some user vars in the pkg.
View 1 Replies
View Related
Jul 8, 2006
Hi
I am generating report with my datasource to an OLAP Cube. I have scenario that there are 2 dimension tables pointing to a single fact table. According to a user input, i have to use one dimension table and not the other and vice versa. I tried using IIF statement in the MDX query designer., but was facing errors.
First of all i want to know if this is possible and if yes, how?
Also , Is it possible to open a window form on clicking any report data
(Similar to assigning hyperlink, but i want to open a window form instead!!!)
regard
Sai
View 2 Replies
View Related
Apr 6, 2007
I need to create multiple reports with each report being sent to the specified user. For example, 3 Managers should each get their own report. A Stored Procedure receives a ManagerID parameter, so I need 3 instances of the report. I also need each report to be sent to its associated Manager:
Mgr ID
Manager Name
1
John Q Manager
2
Jane Q Manager
3
Jack O Lantern
I set up a rss file to run the report and can run it multiple times, passing in a different ID value each time, but it will not be data driven because I must hard-code the parameters in the ReportParmaters object of the report for each execution.
Is there a way, either by designing the report in VS2005 or on the Reporting Services server, to accomplish what I'm intending to do?
View 1 Replies
View Related
Mar 18, 2008
I have a stored procedure which selects results based on some date calculations.
In my table I have a status column and two date fields (Approval Date and Signature Date)
If the value in the status column says approved I want to select results where approval date - signature date is less than a certain number of days.
If the status is naything other than approved i want to select results where sysdate - signature date is less than the given number of days.
How can i achieve this?
View 3 Replies
View Related
Sep 22, 2006
I currently have a form that has different options in a list box such as:Status Codes
View 3 Replies
View Related
Feb 23, 2007
I have the following code which is incomplete. Where it says: txtVendorID = I need it to equal the results of the field VendorID from my query...here is my code. What do I need to add there?
Dim cmdSelect As SqlCommandDim intRecordIDintRecordID = Request.QueryString("RecordID")strConn = ConfigurationManager.AppSettings("conn")conn = New SqlConnection(strConn)cmdSelect = New SqlCommand("spMfgRepListAddaspxByRecordID", conn)cmdSelect.CommandType = CommandType.StoredProcedurecmdSelect.Parameters.AddWithValue("@RecordID", intRecordID)conn.Open()cmdSelect.ExecuteReader()txtVendorID.Text = conn.Close()
View 2 Replies
View Related
Mar 14, 2008
My SQL is very basic. How do I create a query that will accept a parameter, an integer, and based on the integer, locate all the matches in a db?
SELECT COUNT(*) AS Expr1, tblArticle.ArticleIDFROM tblArticle INNER JOIN tblArticleCategory ON tblArticle.ArticleCatID = tblArticleCategory.ACategoryIDGROUP BY tblArticle.ArticleID
This isn't setting up the query to request a parameter.What am I doing wrong here? I"m trying to get the total number of articles for a particular category ID.
View 3 Replies
View Related
May 21, 2008
Hi All,
I have a table which consists the leave details of an employee.
I have the columns like paid leaves,sick leaves,personal leaves in the above table
Problem :
For eg: An employee joined on 21 May 2008. After 6 months i.e., 21 Nov 2008 I need to update the above columns (i e., increase the no.of leaves)
So updation is to be done for every 6 months and for every 1 year.
Can anyone say me how to execute the update query based on the duration.
Thanks in advance.
Regards,
Praveen
View 4 Replies
View Related
