How do I select more data from table?

Question

create table table1(id number unique, f_name varchar2(50),l_name varchar2(50));
Insert into table1 values(1,'posh',' martin')
Insert into table1 values(2,'billgate','money ')
Insert into table1 values(3'game',' football')
Insert into table1 values(4,'england','fans ')
Insert into table1 values(5,'america',' korea')

now if the value of the textbox1.text=billgate america
then i will expect to get "money korea"
if textbox1.text=posh game england
then i will expect to retrieve "martin football fans"
so on ....
so how could i do such retrieving of data?
i am using vb.net and sql server to do this
so you can show me any method you know or you
think that this problem can solve.
with billions thanks!

Answer 1

A simple SQL select statement would be:

select *
from table1
where f_name in ()

The "quoted, comma-delimited list" is compiled from the contents of your text field. Using your last example of posh/game/england in text 1, the sql statement would look like this:

select *
from table1
where f_name in ('posh','game','england')

This will return all columns for all rows who's f_name value is in the specified list. In this case, you'd get the corresponding 3 rows from table1.

Try executing the SQL directly in SQL Server's Query Analyzer tool, to be sure you have the syntax right, and that the query is returning the data you expect. Then, you'll have to do your VB code to create SQL statements in that format.

Good luck!

Answer 2

I thnk you will need to use the In Clause to retrieve the data from database

For eg:
Select value3 from tablew where value2 in ('posh','game','england');
this wil give you 3 records and create a string of that and return to the UI as you want.

Problems that you need to handle:
1. You need to create the string dynamically from the user inputs.
2. handle Exceptions well. Else you may get unexpected results
3. Create the query based on your data model and not on My Example alone .

For eg: if there are 2 records with posh then they will be present in your result ..so u need to be aware of what is going to happen in all cases and then Code Accordingly.

Answer 3

Syntactically i donno how to do it in vb.net.
I can will explain you with following example:-
Take textbox value in a variable
..............dim searchKey = textbox.text;

Tokenize it with space, you will get String array of keywords like below.
arr[0] will have 'billgate';
arr[1] will have 'america';
arr[2] will have 'game';

Now write a query to select data:-
query = "SELECT l_name from table1 ";
for(int i=0;i if(i==0)
query+=" where "
else
query+=" OR "
query+=" f_name = '" + arr[i] + "'";
}

Your query should look like this:=
SELECT l_name from table1 where f_name = 'billgate' OR f_name = 'america' OR f_name = 'game'

Cheers
VikraM

Answer 4

"Erland Sommarskog" is a MSSQL MVP and wrote some interesting functions (was a big help to me) which pass a list or an array of integers or strings; Visit his website to see its implementation for two different MSSQL versions; namely 2000 and 2005. http://www.sommarskog.se/arrays-in-sql.html
Regards