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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

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

Post 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:
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Yeah just caught that (incorrect table name) and modifed the post to reflect the change but the error hasn't changed.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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` 
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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`
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

That did work, can you please explain why the standard method didn't work?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Perhaps it has something to do with parens... not sure though.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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. :(
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
Post Reply