how to search one value from two fields from 1 table

Question

I have below table :

id   productname  model      price  color  size
1     mouse       m220        50     red    50
2     keyboard    k520        50     red    50
3     mouse       mouse220    50     red    50
4     pendive     p220        50     red    50
5     laptopfan   m220        50     red    50

Now when i search for mouse m220 then it will find all product in productname and model fields.

Now suppose i am searching for mouse m220 then it will give me following output:

   id   productname  model      price  color  size
    1     mouse       m220        50     red    50
    3     mouse       mouse220    50     red    50
    5     laptopfan   m220        50     red    50

Now if suppose i am searching mouse then it will give me following output:

id   productname  model      price  color  size
1     mouse       m220        50     red    50
3     mouse       mouse220    50     red    50

SO how can i get this output?

Not really clear what you want here. You search from "mouse m220" and it should return the two rows. What is the logic there? Is it because the first word in you search was found in the productname? Why not the second word being found in the model? You need to provide quite a bit of explanation what you really want here before we can do much. — Sean Lange, Mar 12 '15 at 18:46
@SeanLange See when search `mouse m220` then it will find the similar word %mouse m220% in both column `productname` and `model`. now again when i seach for `mouse` then it will search like %mouse% in both columns. let me know if you have any doubt? — , Mar 13 '15 at 05:57
Looks like you want to find the closest match to the search word. That can be achieved using the [evenshtein-distance](http://en.wikipedia.org/wiki/Levenshtein_distance). [Here is Tsql implimentation of it](http://stackoverflow.com/questions/560709/levenshtein-distance-in-t-sql) — Mahesh, Mar 13 '15 at 12:07
The query here really isn't very difficult. The reason you still don't have an answer is because the question is not clear. If you put in the effort to provide a detailed question you will get a detailed answer. If you just ask vague questions you will get vague answers. — Sean Lange, Mar 13 '15 at 13:23

score 0 · Answer 1 · answered Mar 13 '15 at 12:01

These will be 2 simple queries as follows :

In the first query you want the rows that have productname equal to "mouse" or the rows that have model equal to "m220". This will result in a simple query as follows -

SELECT * FROM <TABLE_NAME> WHERE productname = "mouse" OR model = "m220";

Second query is even simpler. You just want the rows with productname equal to "mouse".

SELECT * FROM <TABLE_NAME> WHERE productname = "mouse";

score 0 · Answer 2 · answered Mar 14 '15 at 07:08

0

    declare @cta varchar(100),@cta1 as varchar(50),@cta2 varchar(50)
    set @cta='mouse m220'
    select @cta1=case when charindex(' ',@cta)>0 then substring(@cta,1,charindex(' ',@cta)) else @cta end ,@cta2=substring(@cta,charindex(' ',@cta)+1,(len(@cta)-charindex(' ',@cta)+1))


 SELECT * FROM <TABLE_NAME> WHERE productname = @cta1 OR model = @cta2;

answered Mar 14 '15 at 07:08

nazark

1,240
2
10
15

Yeah.. it is working in mssql.. how can i run in mysql ? – Mar 14 '15 at 13:50

score 0 · Answer 3 · edited Jul 18 '22 at 22:38

0

SELECT * FROM <TABLE_NAME> 
WHERE productname like "%mouse%" OR model like "%mouse%";

edited Jul 18 '22 at 22:38

Rohit Gupta

4,022
20
31
41

answered Jul 18 '22 at 13:08

Jyotirmaya Das

41
3

how to search one value from two fields from 1 table

3 Answers3