Need help with a query to mysql. I have this statement which may vary depending on what the user is filling in:
“SELECT * FROM dresses WHERE color IN (‘blue’, ‘red’) AND trademark IN (‘adidas’,‘nike’)”
Lets say that this gives me 1 result. For example… “Product_id: 3 , Name: Tight Dress , Color: Blue , Trademark: Nike”
This is good, it gives back what I want.
I have other models/options with the same product_id: 3. I want to get them as well without taking more than one trip to the database. I want to make a new query based on the result of the first query/statement, but I wonder if I can do this while im already “there”. I dont want to make a new/second query for example like this “SELECT * FROM dresses WHERE product_id = 3”
Maybe I can use Joins on the same table or something?
SELECT * FROM dresses WHERE product_id IN (
SELECT product_id FROM dresses WHERE color IN ('blue', 'red') AND trademark IN ('adidas','nike')
)
Might be quite slow, depending on how many rows there are and which indices are available.
If you need to speed it up you could add a combined index on color and trademark
CREATE INDEX search_color_trademark ON dresses(color, trademark);
This will make INSERT queries slower, but the query above a lot faster. Note that if you’re searching on other attributes you would need to add those to the index as well.
SELECT those.*
FROM dresses AS these
INNER
JOIN dresses AS those
ON those.product_id = these.product_id
WHERE these.color IN ('blue', 'red')
AND these.trademark IN ('adidas','nike')
The main request is
SELECT * FROM dresses WHERE color IN (‘blue’, ‘red’) AND trademark IN (‘adidas’,‘nike’) or whatever filters the user is filling in.
So then I want to get the associated products with the same product_id as well, but not in the same set because thats not what the user is requesting in first hand. The user is selecting/filtering a product… He should get that result… But then, also the other options for that same product even if it has not been requested
lol, whatever the rationale for showing them my point is why filter out only to again include? i.e. the difference is “I am only interested in these” vs. “I am mostly interested in these”.
How the results would be displayed would not be the database’s task, but it seems to me something more like this pseudocode
GROUP BY secondary_preference
ORDER BY secondary_preference, other_nonchosen_fields
WHERE = primary_preference
Regarding the subquery solution… Is it possible to create an extra temporary column for the results from the first check / inner select? Or, do something else that can differentiate the results when I get it back?
So, I’m making a query based on the results from the first query here…
But it wouldn’t be a bad idea if I could loop through the resultset in PHP and at the same time check if some “temporary” column isset/exists… And that way be able to identify from which part of the query they come from.
It’s just a thought. I do not know if it’s possible to do something like that.
So the “subquery inner select” gives me first bmw_blue, and bmw_red, exactly what the user wants.
With that information the " subquery outer select " then gives me the other colors associated with that exact same car as well.
The final result looks like this: "bmw_blue, bmw_red, bmw_black, bmw_green and so on.
I need this complete/final list, but I also need to know whish two colors where selected by the user and are the base in this subquery. In this case it is bmw_blue, and bmw_red.
You said it yourself, it makes a query based on the first/inner SELECT, and thats the information I also need. The result of that first check, which the whole query is based on.