Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
Benjamin
Site Administrator
Posts: 6935 Joined: Sun May 19, 2002 10:24 pm
Post
by Benjamin » Mon Jul 03, 2006 6:15 pm
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`)
Benjamin
Site Administrator
Posts: 6935 Joined: Sun May 19, 2002 10:24 pm
Post
by Benjamin » Mon Jul 03, 2006 6:24 pm
Yeah just caught that (incorrect table name) and modifed the post to reflect the change but the error hasn't changed.
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Mon Jul 03, 2006 6:26 pm
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`
Benjamin
Site Administrator
Posts: 6935 Joined: Sun May 19, 2002 10:24 pm
Post
by Benjamin » Mon Jul 03, 2006 6:35 pm
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.
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Mon Jul 03, 2006 6:59 pm
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`
Benjamin
Site Administrator
Posts: 6935 Joined: Sun May 19, 2002 10:24 pm
Post
by Benjamin » Mon Jul 03, 2006 7:07 pm
That did work, can you please explain why the standard method didn't work?
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Mon Jul 03, 2006 7:20 pm
Perhaps it has something to do with parens... not sure though.
Benjamin
Site Administrator
Posts: 6935 Joined: Sun May 19, 2002 10:24 pm
Post
by Benjamin » Mon Jul 03, 2006 11:31 pm
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 » Tue Jul 04, 2006 2:47 am
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.