Tricky Little Query

[jwwceo]

I am trying to write a JOIN query but it doesn't seem to be working. I get error, #1109, saying that one of my tables doesn't exist.

 

Plus, once I get this working, I am a but confused how I can pass a criteria into this query since I am assuming that would also be done in the WHERE clause.

 

Best,

 

James

 

SELECT xcart_variants.color_id FROM xcart_variants 
WHERE

xcart_class_options.optionname
xcart_variants.variantid = xcart_variant_items.variantid
AND
xcart_variant_items.optionid = xcart_class_options.optionid

[roopurt18]

  SELECT
    v.`color_id`
  FROM `xcart_variants` v
  INNER JOIN `xcart_variant_items` i ON v.`variantid`=i.`variantid`
  INNER JOIN `xcart_class_options` o ON i.`optionid`=o.`optionid`
  WHERE
    o.optionname='some option'

[jwwceo]

I am getting this error:

 

#1054 - Unknown column 'o.optionname' in 'where clause'

 

Any idears??

[jwwceo]

SOLVED. I had a variable named wrong...THANKS!!!!!!!

[jwwceo]

How would I modify this to be an UPDATE query??? I

[roopurt18]

What do you want to update?

[jwwceo]

I'm looking to update "color.id". Sorry if this seems really simple...but I'm never seen queries with the little variables in them before...

 

James

[roopurt18]

Do you mean color.id or color_id?  There is a big difference.

 

UPDATING and SELECTING are two different things.  Are you trying to run a single query to update color_id in multiple tables?  If so, which tables do you want to update?

[jwwceo]

Sorry for the confusion. I am looking to update color_id in one table...

 

so instead of selecting it...I want to update it...

[roopurt18]

Well in which table?  What do you want to update it to? And with what criteria do you wish to choose which one to update?  All of that is sort of important information in solving the problem.

[jwwceo]

To simplify the problem, we want to change a hex color value in a table based on the text value (ie. Black) that is in another table. These tables do not relate to one another, except through a third table.

 

The table/field we are trying to update is: xcart_variants.color_id

based on criteria in this table: class_options.optionname

 

The value we wish to change it to will change from time to time, so for purposes of this thread, I suppose we can just use a variable "FFFFFF".

 

Best,

 

James

 

 

 

 

[jwwceo]

In addition to my last post...this is what I have been trying and getting errors on:

 

UPDATE xcart_variants SET color_id ='ffffff'

INNER JOIN xcart_variant_items.variantid ON xcart_variants.variantid
INNER JOIN xcart_class_options.optionid ON xcart_variant_items.optionid

WHERE

xcart_class_options.option_name = 'Black'

[fenway]

Where's the first table?

[jwwceo]

Similarly, the table I am trying to update is xcart_variants, with the field being color_id.

[fenway]

I've addressed the problem in your other duplicate thread.