Mysql query help

Databases
21 
SELECT q.product_name
     , q.product_types
     , bids.id AS bid_id
     , bids.user_id
     , bids.bid
  FROM ( SELECT products.id                       AS product_id 
              , products.name                     AS product_name
              , GROUP_CONCAT(product_types.type 
                    ORDER BY product_types.type ) as product_types
           FROM products
         INNER 
           JOIN product_types 
             ON product_types.product_id = products.id
         INNER 
           JOIN users 
             ON users.id = products.user_id 
         GROUP 
             BY products.id 
              , products.name
       ) AS q
INNER
  JOIN bids
    ON bids.product_id = q.product_id
22

@r937

Thanks! I was thinking of splitting out the bid after as (initial select) while I was driving and wondering if that might work. But I would certainly not have thought about aliasing with a “q” to access.

That is very cool - plus I see how I can use this solution with other tables as well :slight_smile:

Thanks!!
Karen

23

@sibertius

I can’t see all the bids with this one?

Maybe I’m all thumbs :).

Thanks!
Karen

24

it’s not you, it’s this (which is incorrect) –

SELECT products.name,bids.id, ...
FROM ...
GROUP BY products.id

that’s the problem, this approach gives you only one bids.id, and an indeterminate one at that

25

Is it also possible to then search by an individual product type? Above they have been concatenated into the group product_types- but if I then wish to break out and search by an individual value in that group concatenation?

26

Hi

I was able to just a simple WHERE / LIKE(%value%) to investigate the string and match.

Not the best but does the trick :slight_smile:

27

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.