mysql - How to write SQL statement to get matching data from a cross-reference table?

Question

I have the following SQL (MYSQL Database) query:

SELECT images.alt, images.product_id, images.src
FROM wp_wps_images images
INNER JOIN wp_wps_products products
ON products.product_id = images.product_id
AND products.product_id IN ("2112055640177","2112056590449","2112055378033","2112062292081","2112058490993","2112062619761","2112062488689","2112066420849","2112061833329","2112052527217")
WHERE images.alt LIKE "%Swatch%"

This is doing a great job at returning me a result set that looks like the following:

Rust (W) - Swatch   2112058490993   foobar.com
Sand - Swatch   2112058490993   barfoo.com
Tan - Swatch    2112056590449   bazfoo.com
Generic Black - Swatch  2112056590449   tazfoo.com
Patterned / Multi - Swatch  2112055640177   mazfoo.com
Tan - Swatch    2112055640177   bazfoo.com
Generic Black - Swatch  2112055640177   tazfoo.com
Generic Black - Swatch  2112055378033   tazfoo.com
Dark Tobacco - Swatch   2112055378033   makazfoo.com

I have tags table whose schema looks like this id (BIGINT), tag_id (BIGINT), product_id (BIGINT), post_id (BIGINT), tag (VARCHAR). I would like to join this table so that for the images selected I can also read their respective tag name from the tags table, but I do not know how to write the correct JOIN to achieve this:

SELECT images.alt, images.product_id, images.src, tags.tag

I am hoping the above statement would return something like:

Rust (W) - Swatch   2112058490993   foobar.com           color:rust
Sand - Swatch   2112058490993   barfoo.com               material:sand
Tan - Swatch    2112056590449   bazfoo.com               color:tan
Generic Black - Swatch  2112056590449   tazfoo.com       color:black
Patterned / Multi - Swatch  2112055640177   mazfoo.com   material:multi
Tan - Swatch    2112055640177   bazfoo.com               color:tan
Generic Black - Swatch  2112055640177   tazfoo.com       color:black
Generic Black - Swatch  2112055378033   tazfoo.com       color:black
Dark Tobacco - Swatch   2112055378033   makazfoo.com     color:dark-tobacco

Right now I have something like the following SQL statement and it's not getting me closer to my goal:

SELECT images.alt, images.product_id, images.src, tags.tag
FROM wp_wps_images images
INNER JOIN wp_wps_products products
ON products.product_id = images.product_id
AND products.product_id IN ("2112055640177","2112056590449","2112055378033","2112062292081","2112058490993","2112062619761","2112062488689","2112066420849","2112061833329","2112052527217")
INNER JOIN wp_wps_tags tags
ON tags.product_id = products.product_id
AND tags.product_id = images.product_id
AND tags.tag LIKE "%:%"
WHERE images.alt LIKE "%Swatch%"

The above statement gets me results that have lots of duplication in it and far from my goal in matching images with their tag name. The results are:

Tan - Swatch    2112052527217   bazfoo.com  color:generic-black
Tan - Swatch    2112052527217   bazfoo.com  depth:8
Tan - Swatch    2112052527217   bazfoo.com  height:13
Tan - Swatch    2112052527217   bazfoo.com  material:leather
Tan - Swatch    2112052527217   bazfoo.com  strap:12-1-2
Tan - Swatch    2112052527217   bazfoo.com  style:totes
Tan - Swatch    2112052527217   bazfoo.com  width:19
Generic Black - Swatch  2112052527217   tazfoo.com  color:generic-black
Generic Black - Swatch  2112052527217   tazfoo.com  depth:8

How can I write an SQL statement for a MYSQL database that returns the images with their respective tag name?

EDIT 1:

@Dai made a valid comment below.

Your example data output doesn't show how you'd like to get data when a single product has multiple tags

For a single product that has multiple tags I only want the tags that matches the string of the row's images.alt data. The alt data follows the following pattern: [color || material] - Swatch. Tag data follows the following string pattern: [type]:[value].

The string comparison should match the [color || material] part of the image.alt data and the right side of the colon, the [value] side, for the tags.tag data.

Answer 1

You're on the right track - two inner joins.

Q: What happens with this query:

SELECT images.alt, images.product_id, images.src, tags.tag
FROM wp_wps_images images
INNER JOIN wp_wps_products products
ON products.product_id = images.product_id
INNER JOIN wp_wps_tags tags
ON tags.product_id = images.product_id
WHERE images.alt LIKE "%Swatch%"
AND images.product_id IN ("2112055640177","2112056590449","2112055378033","2112062292081","2112058490993","2112062619761","2112062488689","2112066420849","2112061833329","2112052527217")

In other words:

1) Join exclusively on the mutual keys, then

2) Filter on the overall criteria

For the data sets in your tables:

Q: Do you still get "duplicate rows"?

Q: Are any expected rows "missing"?

Q: What happens if/when a single product has multiple rows?

Please try this query and update your post with the results.

ALSO: Look here for more suggestions:

SQL Inner-join with 3 tables?

SQL INNER JOIN – querying data from two or more tables

---

1. I made one additional change: I'm using "images.product_id" across the board.

2. It should return the "alt" text, product ID, image source and tag for each image that's LIKE "%Swatch%" and IN ("2112055640177","2112056590449","2112055378033","2112062292081","2112058490993","2112062619761","2112062488689","2112066420849","2112061833329","2112052527217").

3. If you get multiple rows ... that means you have duplicate images.

4. If the tag is null, that means there's no tag for that product ID.

5. If you don't see a tag you expect, that means there's no image matching the "WHERE" clause.

6. You can verify any of 3, 4, or 5 by copying/pasting a simple "SELECT".

Hope that helps...

Q: Why do you need to join the products table? What (if anything) are you fetching that's unique to "products"?