CODEWITHSUNDEEP
mysql
Nicholas Cardot
Nicholas Cardot

Reputation: 964

How do I use mysql to match against multiple possibilities from a second table?

I'm not entirely sure how to ask this question, so I'll lead by providing an example table and an example output and then follow up with a more thorough explanation of what I'm attempting to accomplish.

Imagine that I have two tables. In the first is a list of companies. Some of these companies have duplicate entries due to being imported and continuously updated from different sources. For example, the company table may look something like this:

| rawName           | strippedName |
| Kohl's            | kohls        |
| kohls.com         | kohls        |
| kohls Corporation | kohls        |

So in this situation, we have information that has come in from three different sources. In an attempt to allow my program to understand that each of these sources are all the same store, I created the stripped name column (which I also use for creating URL's and whatnot).

In the second table, we have information about deals, coupons, shipping offers, etc. However, since these come in from their various sources, the end up with the three different rawNames that we identified above. For example, the second table might look something like this:

| merchantName      | dealInformation       |
| kohls.com         | 10% off everything... |
| kohl's            | Free shipping on...   |
| kohls corporation | 1 Day Flash Sale!     |
| kohls.com         | Buy one get one...    |

So here we have four entries that are all from the same company. However, when a user on the site visits the listing for Kohls, I want it to display all the entries from each source.

Here is what I currently have, but it doesn't seem to be doing the trick. This seems to only work if I set the LIMIT in that sub-query to 1 so that it only brings back one of the rawNames. I need it to match against all of the rawNames.

SELECT * FROM table2
WHERE merchantName = (SELECT rawName FROM table1 WHERE strippedName = '".$strippedName."')

Upvotes: 0

Views: 209

Answers (1)

Sam Dufel
Sam Dufel

Reputation: 17598

The quickest fix is to replace your mercahantName = with merchantName IN

SELECT * FROM table2
WHERE merchantName IN (SELECT rawName FROM table1 WHERE strippedName = '".$strippedName."')

The = operator needs to have exactly one value on each side - the IN keyword matches a value against multiple values.

Upvotes: 1

Related Questions