Search Multiple Tables
Feb 14, 2006
Hi,
I want to search through 3 tables (TableB, TableC, TableD) to find
which hdr_ctl_nbr(s) are on those tables but not on TableA. In other words, TableA should match the combined tables B, C, and D but it doesn't, so I want to find what's missing on TableA. I know how to search TableB (see SELECT below) but how would I add TableC and TableD to this statement ? Thanks, Jeff
Select hdr_ctl_nbr, count(*) from TableA c
where c.hdr_ctl_nbr not exists (select hdr_ctl_nbr from TableB)
group by c.hdr_ctl_nbr
View 7 Replies
ADVERTISEMENT
Dec 21, 2007
Hi,
I am trying to build search engin with 11 parameters in 4 different tables in the database.
For example:
In search.aspx I have 11 textboxes namely
nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc.
And in the result.aspx page I have gridview which post data from the database if the search match.
I wrote this stored procedure. P.S please ignore the syntax.
@name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote
1 @name var(30),
2
3 @nationality (30),
4
5 @phone int,
6
7 etc
8
9 as
10
11
12
13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
14
15 WHERE
16
17 name LIKE '%' ISNULL(@name, '') '%'
18
19 OR nationality LIKE '%' ISNULL(@nationality,'') '%'
20
21 OR phone LIKE '%' ISNULL(@phone,'') '%'
22
23 etc
24
25
Also the error still exist.
What is the best way to search for multiple parameters in multiple tables ?
Thanks in advanced
View 4 Replies
View Related
Nov 30, 2006
Anyone have any recommendations on how to bring together a search across multiple tables? Like I'd love to do a search where i searched for the product name, company name or tag all in one query (though all are in separate tables)... is that possible? Individually, it's easy, but combined it's very hard.
View 3 Replies
View Related
Apr 16, 2008
hi friends,
I know how to create full text catalog with single table in single database.
But i need to know the steps to create full text catalog with multiple tables in single database.
View 2 Replies
View Related
Jan 13, 2006
Hello,We are developing an application against an MS SQL Server 2000 databasewhich requires that we implement full-text searching across columns inmultiple tables. The research that we have conducted seems to indicatethat this is not directly possible within SQL Server 2000. And we canfind no way to implement this as the catalogs that are generated aretable specific.As a work-around, we are planning to create a secondary table usedstrictly for searching which is going to denormalize and combine thesearchable data into one catalog. We just want to confirm that this isthe recommended approach or if there is an alternate solution anyonehas used. Please note that upgrading to SQL Server 2005 is not anoption.Thanks.John FlemingJoin Bytes!
View 1 Replies
View Related
Jul 29, 2015
Is it possible to search a string/value across 1000's of tables and just display the table name and column name which it is in. I don't need to know every instance of the string/value only that I can find it in a given table name.
View 9 Replies
View Related
Sep 19, 2007
Hi,
I have tried this code from http://jtkane.spaces.live.com/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry for full-text search on multiple tables & columns.
Here's my code:
SELECT * from [tStaffDir] AS e, [tStaffDir_PrevEmp] t,CONTAINSTABLE([tStaffDir], *, @Name) as AwhereA.[KEY] = e.[ID] andt.[ID] = e.[ID]
I have FT the both the tables above and I am able to get results from the [tStaffDir] table but not the [tStaffDir_PrevEmp] table.The [tStaffDir_PrevEmp] table does have a column (which is [ID]) that is indexed, unique and non-Nullable.Please advise what I should do and look out for.
Many Thanks.
View 2 Replies
View Related
Dec 28, 2007
Hi, There have been a number of posts on querying multiple tables using full text in SQL Server. I haven't quite found the answer I'm looking for, and I'm aware that in 2005 some full text functionality changed. I have an Events table which joins to a Venue table and also a many-to-many link (via a table called EventsToArtists) to an Artists table.When a user searches for "Rolling Stones Wembley" (without the apostrophes), I want the query to look in the following columns:Event.EventNameEvent.DescriptionArtists.ArtistNameVenue.VenueNameI have done quite a bit of testing on this, and haven't yet got the desired results. Rolling Stones will be found in the Artists table, and Wembley in the Venue table. At the moment, if I search for Rolling Stones, it behaves as expected. But if search for the venue name as well I get no results. I'm pretty sure this is because EventName wouldn't contain all key words and so the record is excluded from the results. At the moment I am using INNER JOINs between the tables....perhaps I should be executing a full text search on each table's catalogue and then do a UNION to join the resultsets?Hope you can point me in the right direction.Thanks
View 5 Replies
View Related
Apr 17, 2008
hi friends,
i need the steps for full text search with more than one tables in single database. I know the steps for full text search by single table in single database.
Thanks in advance
View 1 Replies
View Related
Sep 7, 2007
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:
Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK)
Categories: - CategoryID (int, PK) - CategoryName (varchar)
I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:
CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID)
But this gave me the error:
Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.
I tried converting the Content to a varchar(max) within my view but it still didn't like.
Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
View 2 Replies
View Related
Mar 21, 2008
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
View 1 Replies
View Related
Mar 23, 2007
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is:
I don't want only the records with columns that contains both searchterms.
I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.
For example I search for NETWORK and PERFORMANCE in two columns.
Jobdescr_________________________|Jobtext
Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP
Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.
I only get the first record because JobDescr contains both searchterms
I don't get the second record because none of the columns contains both searchterms
I managed to find a workaround:
SELECT T3.jobid, T3.jobdescr
FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1
INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid
INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId
It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.
Does anyone know a better solution?
Thanks in advance Bart Rouw
View 2 Replies
View Related
Jan 31, 2005
I'm heaving a heck of a time to get a sql query working. I'm being passed a series of zip codes and I need to return a list of all the rows that match those zip codes in a database of about 40,000 leads.
Can someone clue me in to what the commandtext of my query should look like?
Thanks!
View 4 Replies
View Related
Jun 16, 2015
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
View 4 Replies
View Related
Aug 25, 2015
I have an excel file that has multiple sheets and I need to import data from each separate sheet to a separate table using SSIS.
E.g. Sheet A data should go to Table A and Sheet B data should go to Table B and so on. Is it possible to do this with out using script task.
View 6 Replies
View Related
Sep 8, 2006
I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.
Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)
Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.
Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')
Which gives a result of:
RecNo.
30429
Cheque
250
Deposit
250
30429
679.98
250
This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
View 3 Replies
View Related
Nov 15, 2006
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
View 1 Replies
View Related
Jun 27, 2006
I have a couple of hundred flat files to import into database tables using SSIS.
The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.
However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.
Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.
I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?
View 9 Replies
View Related
Mar 2, 2007
I've done some simple sql's for searching a field using Like,But this one is different.
I am adding a param named @searchText
I would like to bring back all records in all the fields listedbelow that has that string in the field...
WHERE a.manufacturer = b.manufacturerIDAND a.location = c.locationIDAND a.Status = d.statusIDAND a.EquipmentType = e.IDAND a.calLab = f.ID AND a.testTechnology = g.id AND (c.locationID = @location OR @location = 0)
So, each line/field above I want to search for the string and includein the dataset.
Anyone can point me in the right direction?
Thanks,
Zath
View 7 Replies
View Related
Sep 23, 2005
I have a search page, containing 3 drop down lists. Theses are used to match data in 3 seperate collumns of a table.I can get the search to work when all boxes are completed, but I need to be able to leave some blank.A similar question was asked in http://forums.asp.net/626941/ShowPost.aspxAnd a very good solution was give: http://www.sqlteam.com/item.asp?ItemID=2077But I don't seem to be able to get any of these examples working!If I use the SET @SQL method, I get an error message saying that my input parameter has not been declared, when it has.When I use the COALESCE method, there is no data returned.(I am passing the data drop down list data fin the search page through the query string to a results page).Does anyone know of any other examples, or sample coding??Thanks...
View 2 Replies
View Related
May 16, 2008
Hi
I have a table with 9 fields that I need to check. I want to check if "text1" and "text2" is in any of thoose 9 fields. But I dont want to check the combination of "text1" and "text2" in the same column. I want for example be able to check if "text1" is in column 1 to 9 and if "text2" is in column 1 to 9. Can someone give me a suggestion on how to do this?
View 6 Replies
View Related
May 30, 2006
Is it possible to put a set of columns into a select statement using "in"
For example:
select count(*) from table1 where (col1, col2, col3, col4) in (select a.col1, a.col2, a.col3, a.col4 from table1 a where........)
This is possible in this form for Oracle and DB2 but I cannot get it to work in Sql Server.
Any ideas?
View 7 Replies
View Related
Mar 1, 2007
Hello
I am building a survey application.
I have 8 questions.
Textbox - Call reference
Dropdownmenu - choose Support method
Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
View 9 Replies
View Related
Sep 3, 2014
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies
View Related
Feb 15, 2008
I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?
Here is XML file:
Code Snippet
<ReferenceFiles>
<File>
<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>
<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>
Thanks.
View 1 Replies
View Related
Nov 29, 2007
I used the data export wizard to export a single table to a single flat file (multiple wasn't allowed). I saved the package as a *.dtsx file which I'm attempting to edit to add the additional tables.
Creating additional sources is fairly easy copy of the first source and change to the table name.
I've tried copying the destination connection and changing to a new text file, but can't get past having to add each column manually to the new destination.
How can I duplicate the mapping that must be taking place in the wizard in the *.dtsx editing environment?
This seems like a simple / common task, but I've been unable to find a solution.
Thanks, Richard
View 1 Replies
View Related
Sep 15, 2007
Hi,
I want to search multiple words that is present in the database, e.g if i am putting "porperty in south delhi" but south word is in the data base, but result does not comes. if I use like operator like this select * from MASTERSEARCH where companyname like '" + txt_Company.Text + "%' or dealsin like '%" + txt_keywords + "%'";Here I put only south word, then result comes. but I want search criteria should be any word that is present in the database.I am using this with my web site. http://www.b2bindialinks.com
View 1 Replies
View Related
Mar 11, 2008
I using multiple keywords to search and filter my data by using Stored Proc and retrieve the results in GridViewStored Proc ========ALTER PROCEDURE dbo.search
(
@eName nvarchar(30), @ID nvarchar(7), @cID nvarchar(10), @eCC nvarchar(3)
)
AS
SELECT eName, ID, cID, eCC
FROM iTrns
WHERE (ID LIKE '%' + @ID + '%') and
(eName LIKE '%' + @eName + '%') and
(cID LIKE '%' + @cID + '%') and
(eCC = @eCC)
========
My code behind========protected void Button1_Click(object sender, EventArgs e)
{
GridView1.DataSource = GetTable();
GridView1.DataBind();
}public DataSet GetTable()
{SqlConnection SqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["***"].ConnectionString);
SqlDataAdapter Adptr = new SqlDataAdapter();SqlCommand cmd = new SqlCommand("search", SqlCon);
cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddWithValue("@ID", TextBox2.Text);
cmd.Parameters.AddWithValue("@eName", TextBox1.Text);cmd.Parameters.AddWithValue("@cID", TextBox3.Text);
cmd.Parameters.AddWithValue("@eCC", TextBox4.Text);
Adptr.SelectCommand = cmd;
DataSet ds = new DataSet();return ds;
SqlCon.Close();
}
========
Sometimes it retrieves the whole data !! and sometimes retrieve nothing !!
Any hint ??
Thank you
View 21 Replies
View Related
Sep 20, 2005
I am building job search engine. I got one listbox and one dropdownlist of place and region. If I select East Anglia from places the dropdownlist of regions it will display Any region, Cambridgeshire, Norfolk, Suffolk and if I Select Greater London it will display Any region, London, London-Central, London-East etc.. etc..I am searching for jobs with respect to regions, Is it possible that if i select say East Anglia in the listbox and Any region in the dropdownlist and i can search with all the regions in that section selected.At the moment i'm using this stored procedure which doesn't include the above idea. Is there any way to modify this Thanks in AdvanceSELECT DISTINCT J.*FROM JobDetails AS J JOIN(SELECT DISTINCT J1.JobIDFROM JobDetails AS J1inner join dbo.Split1(@list, ' ') AS SON J1.JobPosition LIKE '%' + S.Data + '%'AND (@loc='0' OR J1.Location LIKE '%'+@loc+'%')AND (@region='0' OR J1.Location LIKE '%'+@region+'%')AND (@type='0' OR J1.JobType=@type)AND (@a IS NULL OR (J1.JobDate BETWEEN DATEADD(dd,-@a-1,getdate()) AND GETDATE()))) J1 ON J1.JobID = J.JobIDsavvy
View 2 Replies
View Related
Mar 20, 2012
I am working on SQL Server in VB 2008. I have a table 'Records' having 8 columns. I have a search page where I can choose 5 different parameters to search as 'Category' , 'Name' , 'Date' etc.
I can successfully search with a single criteria selected either Category Name Or Date. But I want to create a single SQL command that can search my 'Records' table for either two or all the parameters depending on the selections made by the user.
View 5 Replies
View Related
Feb 10, 2004
I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here)
SBI (Yes, No)
MBE (Yes, No)
SBI (Yes, No)
WBE (Yes, No)
I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name.
I'm lost on both the search and the results code.
I'd like to have a drop down for the search like this:
<form name="Search" action="vendor_results.asp" method="post">
<Input type=hidden name="validate" value=1>
Show all Vendors who are: <Select name="?????">
<OPTION value="YES">SBI
<OPTION value="YES">SBE
<OPTION value="Yes">MBE
<OPTION value="Yes">WBE
</select>
<Input type="submit" Value="Go">
</form>
Any ideas on how to make this work would be greatly appreciated.
View 3 Replies
View Related
Oct 21, 2014
My selection criteria is as follows:
where content like '%EditLiveJava%'
or content like '% Sys__%' ESCAPE '_'
or content like '%<div class="row"/>%'
or content like '%<a href="" title=""%'
or content like '%cmsprod%'
or content like '%Error processing inline link%'
or content like '%see log for stack trace%'
I output the content field if the search is true but would like to also output which specific 'like' has been found.
Can I do this in the one pass or do I have to read the database separately for each condition?
View 3 Replies
View Related
Oct 24, 2007
I have been working on a database and I have imported a number of lists into multiple tables.
I am having a lot of trouble writing the SQL code to be able to query across multiple tables. For instance, I want to know if john doe is on all five lists or if he is only on 3 lists (search by student name).
Can someone help me with this?
Luin.
View 2 Replies
View Related
