CODEWITHSUNDEEP
phpmysqldatabase
Mohammed Sharfuddin Shawon
Mohammed Sharfuddin Shawon

Reputation: 25

Select items matching the names and those with the same category

I don't know how to word this problem so i'll just show you guys what i need.

I have a table like this:

+----+----------------+------------+
| id |      name      |    med     |
+----+----------------+------------+
|  1 | seclo          | omeprazole |
|  2 | something else | other      |
|  3 | ometid         | omeprazole |
+----+----------------+------------+

and until now i have been using the following query to get the records.

SELECT * FROM item WHERE (name LIKE '%{$search}%' OR med LIKE '%{$search}%')

but now i want to search for the name (or the med) and want the records which has the same med as the ones matching the name.

Like if I put "sec" i want record 1 and 3 because record 3 has the same med as which matches record 1.

i hope the question is clear. thanks in advance.

Upvotes: 0

Views: 158

Answers (3)

Nimesh Patel
Nimesh Patel

Reputation: 804

// try this ..

SELECT * FROM `item` WHERE `med` IN (SELECT `med` FROM `item` WHERE (`name` LIKE '%{$search}%' OR `med` LIKE '%{$search}%') OR `name` LIKE '%{$search}%')

Upvotes: 0

kiks73
kiks73

Reputation: 3758

You should use a subquery that refer the same table to get the med needed: 

SELECT * 
FROM item 
WHERE med in (
    SELECT med 
    FROM item 
    WHERE (name LIKE '%{$search}%' OR med LIKE '%{$search}%')
OR name LIKE '%{$search}%')

Upvotes: 0

Gaurav Kandpal
Gaurav Kandpal

Reputation: 1310

This will help you

SELECT * FROM item WHERE med IN (SELECT med from item WHERE name LIKE '%{$search}%' OR med LIKE '%{$search}%')

Upvotes: 1

Related Questions