Link two tables using partial word match

Hi All,

I need to link two tables using partial word match.

How can I write a SQL statement to do so? (I am using MS-Access.)

Table One: [Table Name: tblStreet] [Field Name: Street]
123 ABC Street
124 ABC Street
125 ABC Street
1 XYZ AVE
2 XYZ AVE
3 XYZ AVE
10 CBS Street
11 CBS Street
12 CBS Street
100 Apple Road
101 Apple Road
102 Apple Road

Table Two: [Table Name: tblWord] [Field Name: Word]
ABC
CBS

The output should be:

123 ABC Street
124 ABC Street
125 ABC Street
10 CBS Street
11 CBS Street
12 CBS Street

Could this be done?

Thank you in advanced.

- Grasshopper -SQL Learner wrote:

Quote:

Originally Posted by

Hi All,
>
I need to link two tables using partial word match.
>
How can I write a SQL statement to do so? (I am using MS-Access.)
>
Table One: [Table Name: tblStreet] [Field Name: Street]
123 ABC Street
124 ABC Street
125 ABC Street
1 XYZ AVE
2 XYZ AVE
3 XYZ AVE
10 CBS Street
11 CBS Street
12 CBS Street
100 Apple Road
101 Apple Road
102 Apple Road
>
Table Two: [Table Name: tblWord] [Field Name: Word]
ABC
CBS
>
The output should be:
>
123 ABC Street
124 ABC Street
125 ABC Street
10 CBS Street
11 CBS Street
12 CBS Street
>
Could this be done?
>
Thank you in advanced.
>
- Grasshopper -

SELECT t.* FROM tblStreet t WHERE EXISTS(SELECT 1 FROM tblWord w WHERE
t.Street LIKE '%'+Word+'%')

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Thank you so much Alex! It works. The only thing is that I replaced
the "%" with "*" since this is Transact SQL in Access. : )

By the way, is there any good book that I can use to learn SQL of this
kind?

- Grasshopper -

Link SQL2000 and Oracle

hi, does anyone know how to link SQL2000 to Oracle? because i need to use a sql statement which involve both the SQL2000 table and an Oracle table.

Thanks in advance.You have two choices here:

either you create two connections and retrieve from each database what is needed into datasets and use that data then (works without a problem), or you set up a linked database in SQL server that points to the Oracle database ...
If you do not know how to do that second one, go for the first solution ...|||thanks for the solutions..

Line 1: Incorrect syntax near =

Hi,

I'm getting the above error when i try to fill a Dataset through a
dataAdapter.

I presume it is to do with the sql statement. Below is the relevant
code:

string strPntUnitID = patientCodeLbl.Text;
string strPntFName = fNameLbl.Text;
string strPntLName = lNameLbl.Text;

// Create DataAdapter & Dataset
SqlDataAdapter daRelateDocToPnt = new SqlDataAdapter("SELECT patientNo,
doctorNo FROM tblPatient" +
"WHERE (pntUnitID = '"+ strPntUnitID +"') AND (pntFName = '"+
strPntFName +"')"+
"AND (pntLName = '"+ strPntLName +"')", conn);

DataSet dsDocNoToPnt = new DataSet();

// Create command builder, automatically generates the update commands
SqlCommandBuilder pntCmd = new SqlCommandBuilder(daRelateDocToPnt);

// Set the MissingSchemaAction property to AddWithKey because Fill will
not cause primary
// key & unique key information to be retrieved unless AddWithKey is
specified.
daRelateDocToPnt.MissingSchemaAction = MissingSchemaAction.AddWithKey;

// Use dataAdapter to fill DataSet
daRelateDocToPnt.Fill(dsDocNoToPnt, "Patient");

// place intDocNo into the dataset schema
dsDocNoToPnt.Tables["Patient"].Rows[0]["doctorNo"] = "intDocNo";

// Update The Database
daRelateDocToPnt.Update(dsDocNoToPnt, "Patient");

Can anyone spot the problem?

ThanksAt a quick glance, it seems that you have no space between tblPatient
and WHERE, but without seeing the actual command, it's hard to say. If
you build a SQL string dynamically, it's a good idea to provide a debug
mechanism - perhaps you can build up the string first and display it,
then instantiate the adapter using the string?

You might also want to consider writing a stored procedure to return
the results, and pass the ID and names as parameters.

Simon|||Thanks Simon , your quick glance worked, the space was the problem.|||Assimalyst (c_oxtoby@.hotmail.com) writes:
> I presume it is to do with the sql statement. Below is the relevant
> code:
> string strPntUnitID = patientCodeLbl.Text;
> string strPntFName = fNameLbl.Text;
> string strPntLName = lNameLbl.Text;
> // Create DataAdapter & Dataset
> SqlDataAdapter daRelateDocToPnt = new SqlDataAdapter("SELECT patientNo,
> doctorNo FROM tblPatient" +
> "WHERE (pntUnitID = '"+ strPntUnitID +"') AND (pntFName = '"+
> strPntFName +"')"+
> "AND (pntLName = '"+ strPntLName +"')", conn);

Rather than building the entire command this way, use parameterised
commands:

"SELECT patientNo, doctorNo FROM tblPatient " +
"WHERE (pntUnitID = @.PntUnitID AND (pntFName = @.strPntFName " +
"AND (pntLName = @.PntLName "

The use the parameters collection on the command object to define the
parameter.

If you wonder why, try your current code with someone whose last name
is O'Brien.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Limits on parameters and query complexity?

I have a huge SQL statement with subqueries in the From and Where clauses.
When I run it in SQL Query Analyzer, it runs fine, but if I put it into the
Data tab of a report, Visual Studio just hangs.
I've been able to create the parameter list manually (adding them to the
Report Parameters dialog), and then when I paste the query in without the
Where clause, it works ok. I can then start adding lines to the Where
clause, but as soon as I get enough lines for about 10 parameters or so, it
hangs up again.
I know nothing's wrong with the SQL itself since it runs in Query Analyzer
without a hitch; am I looking at a bug in RS?Sounds like a bug. If you have a good repro, please send it to me at
bradsy@.microsoft.com
--
| From: "DJM" <msnews@.puddlestheshark.com>
| Subject: Limits on parameters and query complexity?
| Date: Mon, 14 Feb 2005 16:53:09 -0700
| Lines: 14
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <uG8e6AvEFHA.548@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 64.140.73.34
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:35923
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a huge SQL statement with subqueries in the From and Where
clauses.
| When I run it in SQL Query Analyzer, it runs fine, but if I put it into
the
| Data tab of a report, Visual Studio just hangs.
|
| I've been able to create the parameter list manually (adding them to the
| Report Parameters dialog), and then when I paste the query in without the
| Where clause, it works ok. I can then start adding lines to the Where
| clause, but as soon as I get enough lines for about 10 parameters or so,
it
| hangs up again.
|
| I know nothing's wrong with the SQL itself since it runs in Query
Analyzer
| without a hitch; am I looking at a bug in RS?
|
|
|

Limiting the Records in SELECT Statement

Hi..

As everyone knows that there's a keyword in MySQL that is used to limit the records in SELECT statement as

SELECT * FROM Table LIMIT 40,10

means that select from record 40 to onward 10 records.

In SQL Server it is a long way with nested select ... can someone provide an alternate or we have to use the Nested SELECT?

Thanks

Yes

U can specify

select top n * from tablename

similarly u can also specify top n percentage in a select statement.

Check for Keyword top in sql Help

|||

Nested select (technically called a derived table) is the best way to go in SQL. Here are a few articles that cover the subject.

http://www.sqlmag.com/Article/ArticleID/43922/sql_server_43922.html

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx

http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

limiting query result based on AS clause

can some point me to a sql statement that would perfrom the equivalent of th
e
following statement?
select Desc, (select count (*) from CART_Items where
fkItemCategoryID=CART_ItemCategories.pkItemCategoryID ) itemcount
from CART_ItemCategories
where itemcount >0
typical ouput
Desc itemcount
Cat 1 0
Cat 2 2
Cat 3 1
Cat 4 0
i would simply like to limit my results to itemcount values greater than zer
o.SELECT
ic.Desc,
COUNT(ci.fkItemCategoryID)
FROM
Cart_Items ci
INNER JOIN
Cart_ItemCategories ic
ON
ci.fkItemCategoryID = ic.pkItemCategoryID
GROUP BY
ic.Desc
HAVING
COUNT(ci.fkItemCategoryID) > 0;
"Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
news:FFF7450A-A17A-479B-B169-8DE92212E3CD@.microsoft.com...
> can some point me to a sql statement that would perfrom the equivalent of
> the
> following statement?
> select Desc, (select count (*) from CART_Items where
> fkItemCategoryID=CART_ItemCategories.pkItemCategoryID ) itemcount
> from CART_ItemCategories
> where itemcount >0
>
> typical ouput
> Desc itemcount
> Cat 1 0
> Cat 2 2
> Cat 3 1
> Cat 4 0
> i would simply like to limit my results to itemcount values greater than
> zero.|||thanks a million
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT
> ic.Desc,
> COUNT(ci.fkItemCategoryID)
> FROM
> Cart_Items ci
> INNER JOIN
> Cart_ItemCategories ic
> ON
> ci.fkItemCategoryID = ic.pkItemCategoryID
> GROUP BY
> ic.Desc
> HAVING
> COUNT(ci.fkItemCategoryID) > 0;
>
>
> "Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
> news:FFF7450A-A17A-479B-B169-8DE92212E3CD@.microsoft.com...
>
>

Limitation on Conn.Execute in classic .ASP & ADO with SQL Server

Hello,

I have a project (using classic ASP & SQL Server) which adds one execute sql statement at a time to a temporary array, and then I join that array with a chr(30) (record separator), to a string variable called strSQL. I then run the following line of code:

conn.execute(strSQL)

I was wondering if there was any limitation to how large the strSQL variable can be? Reason I ask is because thru log writes I can see all of my sql execute lines exist in the variable strSQL prior to running the "conn.execute(strSQL)" command; however, not all of the lines run at the time of execution. Remember, this bug only is occuring whenever I have say over 600 sql lines to execute.

My understanding is that there was no limitation on the size of the string strSQL; however, in the interest of getting the bug fixed quick enough, I decided to just run a loop for each sql statment and run "conn.execute(strSQL)" every 50 times. This, in turn, has solved the problem and I do save all of my data; however, my original bug still exists.

Does anyone know why I have to split the sql commands and execute.com every 50 times instead of just being able to do it once ?

Please let me know. Thanks in advance.It's probably your data provider that is limiting you. Which one are you using, OleDb for SQL Server (SQLOLEDB)? The batch separator for that provider is a semicolon (;) as far as I know. Maybe you'd have better luck with that? I am doubting it but it would be worth a try.

Terri|||I am using ADO thru an ASP page to connect to SQL Server. I have tried numerous separator's (comma,vertibar) with still no luck. That's why I ended up going with the chr(30).

Doing the loop and execute.conn every 50 times through the loop has seemed to definately fix the bug. I am still just curious as to why I couldn't execute the entire command immediately.

What is strange is that I can use ADO and write a general Visual Basic 6.0 application and execute the sql string just fine. Considering ADO is used in both the VB6.0 and .ASP applications you would think it would work?

Oh well, thanks for the thought. Have a good one.|||But which PROVIDER are you using? Are you using the same provider in your VB program that you are in your ASP application (ie, what does your connection string look like for both)? Is the same MDAC version on both machines?

Terri|||Terri,

Yes, MDAC 2.8 is on the web server using the .ASP & ADO connection. However, the VB Project I've created to test out this large sql string is only on my local machine. It does not use a web server or anything like that.|||Sorry forgot to mention that the execute statement is the same in both.

Execute.Conn sqlstr

Where "sqlstr" is representing the hundreds and hundreds of insert statements I need to execute.|||But which PROVIDER are you using? What does the Conn.ConnectionString look like?

Terri|||the connection string looks like:

"DRIVER=SQL Server;SERVER=ServerName;User ID=UserID;PASSWORD=PASSWORD;DATABASE=Database"

I hope this is what you are referring too. Sorry for the confusion.|||You're using the ODBC provider. Use the OleDB provider instead and see if it makes a difference, with semicolons (;) separating your commands.

Your connection string would look like this:

"Provider=sqloledb;Data Source=ServerName;Initial Catalog=Database;User Id=UserID;Password=PASSWORD;"

Seehttp://www.connectionstrings.com for more help on connection strings.

Terri

limit the select to single row

Hello,
How can I limit the select statement to 1 single row ?
How can I limit the select statement to specific no. of rows ?
Thanks :)SELECT TOP 1
FROM ...
ORDER BY ...
SELECT TOP 10
FROM ...
ORDER BY ...
Note that without ORDER BY, which rows that are returned is undefined.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eitan M" <no_spam_please@.nospam_please.com> wrote in message
news:%23lJnZbwoFHA.860@.TK2MSFTNGP12.phx.gbl...
> Hello,
> How can I limit the select statement to 1 single row ?
> How can I limit the select statement to specific no. of rows ?
> Thanks :)
>|||The obvious answer to your first question is to use a WHERE clause that
references a unique key.
In the second case you'll probably want to take a look at the SELECT
TOP n feature or use SET ROWCOUNT.
With TOP and ROWCOUNT be aware that unless you specify a unique key in
the ORDER BY clause or use the TOP WITH TIES modifier you may get
undefined results. That ought to be common sense but too many times I
see people omit to specify a well-defined criteria for a top n subset.
David Portas
SQL Server MVP
--

limit record

hi everybody,
Beside top statement what is the best statement used to limit the display the data?
Thanks...Where

Having

Look them up in BOL|||You can also use SET ROWCOUNT, which can also be found in Books Online.|||thans for the replys guys, One more question here can I use set row count in my vb app without using stored procedure?

thank|||thans for the replys guys, One more question here can I use set row count in my vb app without using stored procedure?

thankSometimes you can, sometimes you can't. The SET ROWCOUNT command is a SQL Server specific command, that affects the current connection. If your application uses connection pooling, it is unsafe to use SET ROWCOUNT outside of a stored procedure.

-PatP