This questions has spinned off of another question I probably did not ask correctly from the get go.
I have some tables, where
attributes_entries that hold a pair of dynamic values for
user table (just used as an example):
id name whatever 22 thanos big guy
id code price 659 0H040 99.99 660 0H040 99.99
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
id title FK_attribute_id 1 windstar 1 2 ford 2 3 75.3 3
I have to find the
product that matches the
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
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
users, so when someone enters a
user they can see a dynamic input under
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.
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')