MYSQL Query help

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?

Appreciate all the tips I can get.

Bless

You can use a subselect for this

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.

1 Like

Thanks man. This was very helpful. Can I translate it like this:

Select all from dresses where product_id is equal to the product_id of this result?

Close. I would translate it as

Select all from dresses where product_id is equal to the a product_id of from this result.

2 Likes

you betcha

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')

Great. I got it now. Thanks alot

Must expand my question…

Is it possible to get the results/set separately? The main query for themselves, and the associated id’s for themselves.

sure… just run two queries

Hey. Looking for a solution with one query to the database if possible

The product_ids are part of the main result are they not? In that case you should be able to obtain them from the main result in your program.

For example in PHP you could do $productIds = array_unique(array_column($result, 'product_id'));

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

Thats why I need them separately.

Kind of a “I know you didn’t want these, but here they are anyway” display?

Why filter the results when it sounds like you want a sort (ORDER BY)? Seems that would be more acceptable.

Exactly :wink:

If you hover over a product, then you should be able to see if the product has other sizes and colors as well

Or… “Similar products you might be interested in” :slight_smile:

1 Like

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 

would be a better query to use.

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.

What do you see for solutions here?

I’m guessing that what you are referring to as “temporary” I would call “aliases”.

It is possible, and indeed common, to give different names to both tables and fields.

For example, if I SELECTed SUM(some_number_field) I would write the SELECT more like
SELECT SUM(some_number_field) AS a_better_name

Similar can be done with tables. eg. a rough example:

SELECT table.something 
, same_table.another_thing 
FROM table 
JOIN (SELECT 
  another_thing 
  FROM table ) AS same_table
ON same_table.id = table.id 

Lets try a new example…

I got a list. Full of carmodels and colors.

The user selects BMW blue & red.

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.

Do you feel me brother?

That should be doable as a join. Try an outer left join of “all values” on the aliased “preferred values” ordered by the aliased preferred values.

Does that return what you need?

1 Like

Is this what you are looking for.

SELECT
            d.trademark,
            d.color,
            COUNT(*) total
  FROM
            dresses d
GROUP
        BY
            d.trademark,
            color
ORDER
       BY
            d.trademark,
            total DESC