Selecting same column twice

Question

I need to also select value from xcart_extra_field_values WHERE fieldid = 5. From looking around, it seems what I need to do is use a join and aliases, however I can't seem to figure out how to join the table on itself, and just using another alias for value, and trying to pull in the same table twice crashed my database. Twice.

This my current query, which works:

                            SELECT a.avail,
                                c.value,
                                b.productid,
                                a.productcode,
                         FROM xcart_products a,
                              xcart_products_lng_en b,
                              xcart_extra_field_values c,
                         WHERE a.productid IN
                             (SELECT productid
                              FROM xcart_extra_field_values
                              WHERE a.productid = productid
                                AND fieldid = 5
                                AND (LOWER(value) = 'retail'
                                     OR LOWER(value) = 'stock'
                                     OR LOWER(value) = 'c1 stock'
                                     OR LOWER(value) = 'c2 stock'
                                     OR LOWER(value) = 'g stock') )
                           AND c.fieldid = 9
                           AND (a.productid = b.productid)
                           AND (b.productid = c.productid)

This is the query that crashes the database:

                            SELECT a.avail,
                                c.value,
                                d.value,
                                b.productid,
                                a.productcode
                         FROM xcart_products a,
                              xcart_products_lng_en b,
                              xcart_extra_field_values c,
                              xcart_extra_field_values d
                         WHERE a.productid IN
                             (SELECT productid
                              FROM xcart_extra_field_values
                              WHERE a.productid = productid
                                AND fieldid = 5
                                AND (LOWER(value) = 'retail'
                                     OR LOWER(value) = 'stock'
                                     OR LOWER(value) = 'c1 stock'
                                     OR LOWER(value) = 'c2 stock'
                                     OR LOWER(value) = 'g stock') )
                           AND c.fieldid = 9
                           AND d.fieldid = 5
                           AND (a.productid = b.productid)
                           AND (b.productid = c.productid)

Answer 1

This is how I'd write it:

 SELECT xp.avail,
        xpe5.value,
        xpe9.value,
        xpl.productid,
        xp.productcode
   FROM xcart_products xp
   JOIN xcart_products_lng_en xpl
     ON xpl.productid = xp.productid
   JOIN xcart_extra_field_values xpe5
     ON xpe5.productid = xp.productid
    AND xpe5.fieldid = 5
    AND xpe5.value IN ('retail','stock','c1 stock','c2 stock','g stock')
   JOIN xcart_extra_field_values xpe9
     ON xpe9.productid = xp.productid
    AND xpe9.fieldid = 9

Assuming you have all other indexes in place, your xpe5 value check is probably what's letting the side down.

I would never let the employee input a fixed stock location into the database from a textbox.. you should have a dropdown with a set of values at the very least (maybe let the employee add values for new stock locations). I'd probably put the stock location options in a separate table and put in a foreign key to that in the main table.

Answer 2

I've got it working now. Typical when I post mysql questions.

                            SELECT a.avail,
                                    c.value,
                                    d.value,
                                    b.productid,
                                    a.productcode
                             FROM xcart_products a,
                                  xcart_products_lng_en b,
                                  xcart_extra_field_values c,
                                  xcart_extra_field_values d
                             WHERE a.productid IN
                                 (SELECT productid
                                  FROM xcart_extra_field_values
                                  WHERE a.productid = productid
                                    AND fieldid = 5
                                    AND (LOWER(value) = 'retail'
                                         OR LOWER(value) = 'stock'
                                         OR LOWER(value) = 'c1 stock'
                                         OR LOWER(value) = 'c2 stock'
                                         OR LOWER(value) = 'g stock') )
                               AND c.fieldid = 9
                               AND d.fieldid = 5
                               AND (a.productid = b.productid)
                               AND (b.productid = c.productid)
                               AND (c.productid = d.productid)

I need to also compare c.productid to d.productid. However, I'm not pleased with my own answer, as it seems terribly inefficient. This is, unfortunately, due to my own limited understanding of mysql.