MySQL issue with subqueries in select statement

Question

I need to use subqueries to name columns in a query. I have written a statement with subqueries in the SELECT section, but I am getting the following error message (returned from Codeigniter):

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT ps_url_name FROM product_selects WHERE ps_id = '1'), psc_opt_2 A' at line 1

SELECT
    `psc_opt_1` AS (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '1'),
    `psc_opt_2` AS (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '2'),
    `psc_opt_3` AS (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '3'),
    `psc_opt_4` AS (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '4'),
    `psc_opt_5` AS (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '5')
FROM (`product_stock_control`)
WHERE `psc_prod_id` = '5'

Filename: models/products_model.php

Line Number: 602

Any ideas what is going wrong?

there are php functions that allow you to extract column names from a table.... have you check that out? it looks like you are executing this within some php code — CheeseConQueso
– CheeseConQueso, Commented Aug 9, 2012 at 16:58

Daniel Li · Accepted Answer · 2013-03-12 16:00:12Z

2

AS is a column alias operator, meaning you are selecting these variables as an identifier. You cannot use the operator to identify a variable with a query.

Reference: http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

Instead, you'll need to reverse how you use the alias operator like so:

SELECT
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '1') AS `psc_opt_1`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '2') AS `psc_opt_2`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '3') AS `psc_opt_3`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '4') AS `psc_opt_4`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '5') AS `psc_opt_5`
FROM (`product_stock_control`)
WHERE `psc_prod_id` = '5'

Ideally, you should avoid using subqueries (especially in this case) to avoid table-locking overhead.

edited Mar 12, 2013 at 16:00

answered Aug 9, 2012 at 16:57

Daniel Li

15.6k6 gold badges45 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rs. · Accepted Answer · 2012-08-09 16:58:20Z

0

correct format is

(SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '1') As `psc_opt_1`

answered Aug 9, 2012 at 16:58

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Comments

Taryn · Accepted Answer · 2012-08-09 16:59:01Z

0

Did you try this:

SELECT
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '1') as `psc_opt_1`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '2') as `psc_opt_2`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '3') as `psc_opt_3`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '4') as `psc_opt_4`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '5') as `psc_opt_5`
FROM (`product_stock_control`)
WHERE `psc_prod_id` = '5'

answered Aug 9, 2012 at 16:59

Taryn

249k57 gold badges374 silver badges409 bronze badges

Comments

landi · Accepted Answer · 2012-08-09 17:03:08Z

0

Do you want to name this columns psc_opt_1 to psc_opt_1 or what what do you exactly want to?

Naming would be like this:

SELECT
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '1') AS `psc_opt_1`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '2') AS `psc_opt_2`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '3') AS `psc_opt_3`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '4') AS `psc_opt_4`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '5') AS `psc_opt_5`
FROM (`product_stock_control`)
WHERE `psc_prod_id` = '5'

answered Aug 9, 2012 at 17:03

landi

3432 silver badges10 bronze badges

Collectives™ on Stack Overflow

MySQL issue with subqueries in select statement

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related