-1

I have this kind of setup.

TABLE images;
id    url                                 name
1     http://imgur.com/image1.png         image1.png
2     http://imgur.com/image2.png         image2.png
3     http://imgur.com/image3.png         image3.png
4     http://imgur.com/image4.png         image4.png

TABLE colors;
image_id  hex
1         9a8166
1         9a8130
1         000000
1         cdd69a
1         9a5430
1         662730
1         000030
1         665466
2         665430
2         9aac66
2         002700
2         cdac66
2         cd819a
2         302730
2         300030
2         002730

How would I go about getting all images that contain a list of hex codes?

If I do hex in ([list of hex codes]) I'll get pretty much all images since there's a high chance they have at least one color on the list.

I'd like to get images that have all colors, then I can deal with getting images that have at least n-1 colors from the list or whatever.

Edit:

So in this case.

If I searched [blue] I'd get the images 1 and 2.

If I searched [blue, red] I'd get the images 1 and 2.

If I searched [blue, red, pink] I'd get the image 2.

Daviid
  • 630
  • 4
  • 17
  • 1
    Include your expected results please. – Stu Apr 25 '22 at 18:07
  • It sounds like you want a [tag:relational-division] query. Perhaps this can be answered by this old answer of mine: https://stackoverflow.com/questions/7407001/mysql-select-ids-which-occur-on-different-rows-with-multiple-specific-values-fo/7407078#7407078 – Bill Karwin Apr 25 '22 at 18:13
  • 1
    Added more explanation with an image, @BillKarwin I'll look into it, thanks. – Daviid Apr 25 '22 at 18:20

1 Answers1

0

Put list of hex in a new list_of_hex_codes table with one column hex (and an id of course). Lets assume that there are n of hex, so there will be n rows in this table.

Create a query count(a.image_id) c, a.*, b.* from colors a, list_of_hex_codes b where a.hex = b.hex order by c

If count of a.image_id in the query will be n -> that means the image contains a whole list of hex codes. If count of a.image_id in the query will be (n - 1) -> that means the image contains n - 1 colors. And so on.

Vladimir.V.Bvn
  • 1,050
  • 1
  • 13
  • 13