Page 1 of 1

I'd love to know what is wrong with this query...

Posted: Mon Jul 03, 2006 6:15 pm
by Benjamin
Considering this is always how I write joins and they have always worked before...

Code: Select all

$FetchOptionsQuery  = "select `products_options_values_id`, `products_options_values_name` ";
  $FetchOptionsQuery .= "from `products_options_values` ";
  $FetchOptionsQuery .= "join `products_options_values_to_products_options` ";
  $FetchOptionsQuery .= "on (`products_options_values`.`products_options_values_id` = `products_options_values_to_products_options`.`products_options_values_id`) ";
#1064 - You have an error in your SQL syntax near 'on ( `products_options_values` . `products_options_values_id` = `products_options_value' at line 1
The query..

Code: Select all

select `products_options_values_id`, `products_options_values_name` from `products_options_values` join `products_options_values_to_products_options` on (`products_options_values`.`products_options_values_id` = `products_options_values_to_products_options`.`products_options_values_id`)
:evil: :evil: :evil:

Posted: Mon Jul 03, 2006 6:24 pm
by Benjamin
Yeah just caught that (incorrect table name) and modifed the post to reflect the change but the error hasn't changed.

Posted: Mon Jul 03, 2006 6:26 pm
by Weirdan
Try this:

Code: Select all

SELECT 
    `products_options_values_id`, 
    `products_options_values_name` 
FROM
    `products_options_values` `val`
INNER JOIN 
    `products_options_values_to_products_options` `opt`
ON 
    `val`.`products_options_values_id` = `opt`.`products_options_values_id` 

Posted: Mon Jul 03, 2006 6:35 pm
by Benjamin

Code: Select all

SELECT 
    `products_options_values_id`, 
    `products_options_values_name` 
FROM
    `products_options_values` `val`
INNER JOIN 
    `products_options_values_to_products_options` `opt`
ON 
    `val`.`products_options_values_id` = `opt`.`products_options_values_id` 
#1052 - Column: 'products_options_values_id' in field list is ambiguous

Code: Select all

SELECT 
    `products_options_values`.`products_options_values_id`, 
    `products_options_values_name` 
FROM
    `products_options_values` `val`
INNER JOIN 
    `products_options_values_to_products_options` `opt`
ON 
    `val`.`products_options_values_id` = `opt`.`products_options_values_id` 
#1109 - Unknown table 'products_options_values' in field list

The table is there.

Posted: Mon Jul 03, 2006 6:59 pm
by Weirdan

Code: Select all

SELECT
    `val`.`products_options_values_id`,
    `products_options_values_name`
FROM
    `products_options_values` `val`
INNER JOIN
    `products_options_values_to_products_options` `opt`
ON
    `val`.`products_options_values_id` = `opt`.`products_options_values_id`

Posted: Mon Jul 03, 2006 7:07 pm
by Benjamin
That did work, can you please explain why the standard method didn't work?

Posted: Mon Jul 03, 2006 7:20 pm
by Weirdan
Perhaps it has something to do with parens... not sure though.

Posted: Mon Jul 03, 2006 11:31 pm
by Benjamin
Ok well thank you for the help. Would have took me a long time to figure it out. It's things like this that kill my production for a few hours. :(

Posted: Tue Jul 04, 2006 2:47 am
by GM
I think it's to do with the table aliases - having defined the table aliases, the original table names are no longer recognised in the query, which is why you got your second error.

I always use the rule: If you define table aliases, always use them, even if the fieldname is not ambiguous.