Match MySQL result from keywords

[QeeR]

i am working on a business listing site ...
there are 2 tables .. business table (which contains the name and address descriptions etc) and keywords table (which will contain several keywords to define the business and each keywords will be separated by comma (,) )..
Suppose a user search data where the business name is not known i.e. entering just computer maintenance or vehicles, how do i display results which match the keywords?
And how to query both tables when user enter the business name and keywords like Softtech Solution (business name) and computer maintenance (keyword) and also display a business which might have the same name and also display other business which has the same keywords.

Thanx in advance!!!

 

[krishnandu.sarkar]

You can match the keywords of table 2 from the description column of table 1 using

select * from table1 where description like '%keywords%'

 

[vickybat]

^^" description" column is in the 2nd table. Don't you think there should be a join in order to fetch records from 1st and 2nd table?

@ QeeR

I think you will have to define a primary and foreign key relationship between table 1 and 2 in order to perform a join and fetch records from table 2 in the context of table 1.

Let foreign key of table 1 is emp_id and primary key of table 2 is dept_id- or anything applicable as per the requirement

In your case, the query will be:

SELECT name, address, description

FROM table1,table2

WHERE emp_id=dept_id

AND description LIKE '%keywords%';

 

[krishnandu.sarkar]

Yeah, I just answered his query about how to match keywords, he need to join both the tables in order to get matching from both the tables.

 

[asingh]

i am working on a business listing site ...
there are 2 tables .. business table (which contains the name and address descriptions etc) and keywords table (which will contain several keywords to define the business and each keywords will be separated by comma (,) )..
Suppose a user search data where the business name is not known i.e. entering just computer maintenance or vehicles, how do i display results which match the keywords?
And how to query both tables when user enter the business name and keywords like Softtech Solution (business name) and computer maintenance (keyword) and also display a business which might have the same name and also display other business which has the same keywords.

Thanx in advance!!!

What is the common field in both the tables..?

 

[QeeR]

What is the common field in both the tables..?

the keyword table contain a foreign key (business_id).... which is the id of the business in the business table... will this be good for querying or is there a better approach... thanks for the replies..

 

[vickybat]

^^ You got the answer yourself mate. Link the business_id and id in a primary -foreign key relationship and write the query like i mentioned before. I,m repeating the query again:

SELECT name, address, (required column of keywords table) AS "Business description"

FROM business,keyword // table name

WHERE business_id=id

AND description LIKE '%keywords%'; // mention exact names in order to match or else everything will be displayed

 

[asingh]

^^
I doubt that is also going to work. Cause it will only return exact cases where the two business ID's are equal in the table. Also there in no full inner join so how can two table be compared..?

@OP:

Can you list out the structures of both the tables. Like this.


Table A
Field1
Field2
Field3
Field4


Table B
Field1
Field2
Field3
Field4

Color the fields which can be joined in same color. And then tell us which fields are to be compared for pattern match. Are they from both the tables, or will they be user inputted and pulled from one table.

Also are you on SQL or MS Access.

 

[vickybat]

@ asingh


But that's what join is supposed to do right? Records will be displayed if there is a match between the ids's so that the keyword data from the second table can be pulled. If inner join is specified then only the exact match is pulled and that may be more than once.

If op wants to retrieve all the records from 2nd table even if no match exists, then he has to specify outer join right?

Correct me if i'm wrong buddy.

 

[asingh]

^^
If he puts in a full inner join, he should not get duplicates. I guess the OP has to tell us the exact structures, and how he wants records pulled. It is quite vague.

 

[vickybat]

^^ Got it buddy. Lets wait for OP to reply and give us the exact layout and structure.

 

[QeeR]

^^
If he puts in a full inner join, he should not get duplicates. I guess the OP has to tell us the exact structures, and how he wants records pulled. It is quite vague.

Its MySQL ...

business table
businessid PK
name
address
and some other details

keyword table
id PK
businessid FK
keywords


this is how i created the dB .. maybe its not the best approach and please suggestions are appreciated ..

Actually its for searching the database .. a user may not know the name of the companies but by entering some keywords for searching, find companies that (might) match their business practice e.g user input : software development result : companies that develop software, user input : tickets result : who can issue tickets for travelling, bus, flight etc., ..

i hope i am making myself clear..

 

[QeeR]

ohh.... and also the keywords column contains data that are separated by commas (, ) and when the process of searching starts it will first look for the business table and if no exact business name is found it will search the keyword table for that might contains the term and if found get the businessid from keyword table and match it with the businessid from the business table .... this is what i am trying to achieved ...

And again i'm not sure whether creating queries like this will be good for searching database ...

 

[asingh]

Okay...let us get the logic clear first:

1. You have two distinct tables. Business Table (BT) and Keyword Table (KT).
2. A user inputs suppose "computers".
3. We search the BT field 'name' for "computers". If found it will pull out the fields from the respective PK and cross join it to the KT FK.
4. If not found in BT, it will search in KT field 'keywords'. If found it will again give the cross-join resultant of the PK on FK.
5. The 'keywords' in the KT are in long string format delimited by a ",".

If this is what you want, you will need to use stored procedures. Let me know if this is correct, can probably try to set it up for you..!

 

[QeeR]

Okay...let us get the logic clear first:

1. You have two distinct tables. Business Table (BT) and Keyword Table (KT).
2. A user inputs suppose "computers".
3. We search the BT field 'name' for "computers". If found it will pull out the fields from the respective PK and cross join it to the KT FK.
4. If not found in BT, it will search in KT field 'keywords'. If found it will again give the cross-join resultant of the PK on FK.
5. The 'keywords' in the KT are in long string format delimited by a ",".

If this is what you want, you will need to use stored procedures. Let me know if this is correct, can probably try to set it up for you..!

YES!! Exactly .... I would really appreciate it ... thanks a lot...

 

[asingh]

^^
Okay, will give this a shot tomorrow at office, on my SQL server.

Till then others might chip in too...!

By the way:
Is this home work...?

 

[QeeR]

^^
Okay, will give this a shot tomorrow at office, on my SQL server.

Till then others might chip in too...!

By the way:
Is this home work...?

Thank you very much for the help @asingh

this is not a home work ... Its like i'm looking for more knowledge and hope that this might come in handy sometimes in the future .. i usually create some projects on my own at home and while creating this small projects it helps me to learn more, and i could not find queries like this in books (that i have read so far) all i can i do is look for help in forums.. even though i googled it i could not find the right answer or maybe my serach terms were not accurate ... thanks a lot by the way...

 

[vickybat]

I think creating a cross join will lead to cartesian product of both tables. That would mean all the column values of table 1 will get match as many times as the values in table 2. I don't know if this is the requirement but i figure creating a normal (inner join) will do.

Yes executing queries for searching is time consuming. So creating a procedure is ideal and if a search is needed, simply the procedure is called.

CREATE PROCEDURE sp_business
AS
SELECT b.name,b.address,b.others,k.keywords

FROM business b , keyword k

WHERE b.business_id=k.business_id

AND k.keywords LIKE %abc%;

GO

You can call the stored procedure name by simply typing:

sp_business

Now running this will give you the matched names and addresses from first table with all matched keywords from second table. Using the LIKE keyword will allow you to filter the keywords and give you the exact match of keywords even after satisfying the join condition.

This is my understanding and may be wrong. Asingh is far more experienced in this matter so wait for his post.

@ asingh

This is what i could figure buddy. If wrong ,please give the correct solution.

 

[asingh]

^^
It is not that easy.

First the column 'name' has to be pattern matched. If you get a match, then you run the cross join.

IF....

There is no match, then you run the pattern match on the keywords column from the second table. If matched, again the cross joined product...!

It will be P-SQL here. Slightly complex. But do-able. Plus the SP will receive a search parameter too..!

 

[vickybat]

^^ You mean PL/SQL right? Allright buddy i'll wait for your reply.

It will be fun learning from my side as well.