How to find an entry from filtering with two associated tables

Question

This questions has spinned off of another question I probably did not ask correctly from the get go.

I have some tables, where attributes and attributes_entries that hold a pair of dynamic values for products.

The user table (just used as an example):

id     name       whatever
22     thanos     big guy

The products table:

id   code    price
659  0H040   99.99
660  0H040   99.99

The attributes table:

id   title           model      model_id
1    nickname        products   659
2    manufacturer    products   659
3    rhod            products   660
4    manufacturer    products   null
5    age             user       null
6    age             user       22

The attribute_entries table:

id     title      FK_attribute_id
1      windstar   1
2      ford       2
3      75.3       3

I have to find the product that matches the attributes and attribue_entries a user is looking for.

So, if the search filter is manufacturer=ford&nickname=windstar and these are found for product 659 then provide in data set.

How would I go about doing this? I have a fiddle with queries from my previous question but I can't seem to get the proper results.

The desired result is something along the lines of:

attr_title      attr_entry_title    attr_model      attr_model_id
nickname        windstar            products        659
manufacturer    ford                products        659

But what I really need is to get the product id that simply has a match of both these value pairs manufacturer=ford&nickname=windstar.

Since I do not have a FK association to products, I think this is going to be a problem. I didn't associate the products because some attributes are entered globally under a model like products or users, so when someone enters a product or user they can see a dynamic input under attributes called manufacturer, and can enter a value for this to associate it to the model (ex.products).

I think I'm going to have to redo this structure since a reverse lookup of attributes and attribute_entries I have no idea how to find its model id value that has no association.

Answer 1

Is this what you're looking for?

select
    a.title as 'attr_title',
    ae.title as 'attr_entry_title',
    a.model as 'attr_model',
    p.id as 'attr_model_id'

from
    products p
inner join
    attributes a on a.model_id = p.id
inner join
    attribute_entries ae on ae.FK_attribute_id = a.id
where
    ae.title in('ford','windstar')

You might be able to modify the where clause like this to exclude unselected terms:

ae.title not in (select title from attribute_entries where title not in ('ford', 'windstar')