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.

  • 2
    You go about it by first studying EAV, as was suggested in comments to your other question.
    – mustaccio
    May 13, 2019 at 18:34
  • 1
    e.g. eav
    – danblack
    May 14, 2019 at 3:35
  • Show us the desired result.
    – Rick James
    May 14, 2019 at 14:30
  • I have updated for my desired result
    – gstlouis
    May 15, 2019 at 19:48

1 Answer 1


Is this what you're looking for?

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

    products p
inner join
    attributes a on a.model_id = p.id
inner join
    attribute_entries ae on ae.FK_attribute_id = a.id
    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')
  • this provides a big result of showing me all the entries of prods for just 'ford' or just 'windstar'. What I am looking for is to only show the results of products that matches an association of both 'windstar' and 'ford' only. thx for this. At least I can technically do a find the duplicates of p.id with php, check if its 'windstar' and 'ford' and present the data.. if I don't find any other ideas
    – gstlouis
    May 16, 2019 at 15:18
  • I just modified it to provide a way to exclude unselected terms. I hope it will work for you.
    – raphael75
    May 16, 2019 at 16:30
  • I tried this and it provided the same big results unfortunately. However I did not know I could do an inner join with p.id = a.model_id since they are not associated with a FK. thanks
    – gstlouis
    May 17, 2019 at 14:58
  • another approach I though about would be if the results provide duplicates of products.id. Because if there are duplicated when I query for 'ford' and 'windstar' and it displays multiple products.id I know that product hold 'ford' and 'windstar' or the user entered a duplicate of 'forrd' and 'ford' for instance, which wouldn't be my problem.
    – gstlouis
    May 17, 2019 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.