Get multiple columns from a select subquery

Question

SELECT 
 *, 
 p.name AS name, 
 p.image, 
 p.price,
 ( 
 SELECT ps.price 
 FROM product_special ps 
 WHERE p.id = ps.id
 AND ps.date < NOW() 
 ORDER BY ps.priority ASC, LIMIT 1
 ) AS special_price,
 ( 
 SELECT ps.date 
 FROM product_special ps 
 WHERE p.id = ps.id
 AND ps.date < NOW() 
 ORDER BY ps.priority ASC, LIMIT 1
 ) AS date
FROM product p LEFT JOIN product_special ps ON (p.id = ps.id)

As you can see I'm repeating the same subquery just to get another column out. I'm wondering is there a better way of doing this?

id is the primary key in both tables. I've no problem making product_special.priority unique if that can help.

Answer 1

Assuming combination product_special.id, product_special.priority is unique

 SELECT p.*, special_price,special_date
 FROM product p
 LEFT JOIN 
 (
 SELECT ps.id, ps.price as special_price, ps.`date` as special_date
 FROM product_special ps
 INNER JOIN 
 (
 SELECT id, MIN(priority) as min_priority 
 FROM product_special
 GROUP BY id
 ) ps2 
 ON (ps2.id = ps.id)
 )a ON (a.id=p.id)

Answer 2

Inspired by dezso's answer https://dba.stackexchange.com/a/222471/127433 I'm solving the problem in PostgreSQL using arrays, like this:

SELECT 
 *, 
 p.name AS name, 
 p.image, 
 p.price,
 ( 
 SELECT ARRAY[ps.price, ps.date]
 FROM product_special ps 
 WHERE p.id = ps.id
 AND ps.date < NOW() 
 ORDER BY ps.priority ASC, LIMIT 1
 ) AS special_price_and_date
FROM product p LEFT JOIN product_special ps ON (p.id = ps.id)

Admittedly it's still just one column, but in my code, I can easily access the two values. Hope it works for you as well.

Answer 3

unless you're intending to return the fields as special_price.price and date.date why not alias the names inside the subquery? e.g.

SELECT p.*, p.name AS name, p.image, p.price, ps.special_price, ps.my_date
FROM product p
LEFT JOIN
 (SELECT
 psi.price as special_price, psi.date as my_date, psi.id 
 FROM product_special psi
 WHERE 
 p.id = psi.id AND
 psi.date < NOW()
 ORDER BY psi.priority ASC, LIMIT 1
 ) AS ps ON
 p.id = ps.id

Does your query language have a FIRST() aggregate function? Not sure if you could make the PK of product_special a composite between id and priority (both ASC sort) and change the ORDER clause to GROUP BY id, psi.priority

you MIGHT be able to remove the ORDER BY clause entirely and use HAVING MIN(psi.priority)

• Bounty: I get an error on p.id = psi.id It says invalid reference to from - clause entry for table "p" hint: there is an entry for table "p", but it can not be referenced from this part of the query.
• mpag: I'm not quite sure if that is an issue with your RDBMS or with my logic. I do see one issue with my query above, which I will now edit (specifically, there isn't a ps.id exposed to join on).
• mpag: (I believe I was leaving the OP's WHERE and JOIN logic intact and wasn't writing the query from scratch)

Answer 4

Note that the "cross apply" mechanism from SQL Server would solve this, but it isn't available in PostgreSQL. Basically, it was their solution for how to pass parameters (which tend to be references to columns external to the current table expression) to functions called as table expressions in the FROM clause. But it turned out to be useful for all kinds of situations where you want to avoid another level of subquery nesting or moving things from the FROM clause to the SELECT clause. PostgreSQL made it possible to do this by making kind of an exception -- you can pass parameters like that if the expression is a simple function call but not strictly speaking an embedded SELECT. So

left join highestPriorityProductSpecial(p.id) on true

is ok, but not

left join (select * from product_special ps where ps.id = p.id order by priority desc limit 1) on true

even though the definition of the function is precisely that.

So, that is in fact a handy solution (in 9.1 at least): make a function to extract your highest priority row by doing the limit inside the function.

But functions have the drawback that the query plan will not show what is going on inside them and I believe it will always choose a nested loop join, even when that might not be best.

• user1822: cross apply is available in Postgres starting with 9.3 (released in 2013) but they chose to adhere to the SQL standard and use the standard lateral operator. In your second query replace left join with left join lateral

Answer 5

Try the following SQL command:

SELECT p.name,p.image,p.price,pss.price,pss.date
FROM Product p OUTER APPLY(SELECT TOP(1)* 
FROM ProductSpecial ps
WHERE p.Id = ps.Id ORDER BY ps.priority )as pss

• Ahmad Abuhasna: may you please add more information to your answer
• ypercubeᵀᴹ: The code in question uses LIMIT and is not tagged with a DBMS (so it could be MySQL or Postgres or SQLite or possibly some other dbms). The code in the answer uses OUTER APPLY and TOP so it it will work in SQL Server only (and Sybase) which do not have LIMIT.
• SANTOSH APPANA: This one is applicable for sql server only for other databases we can use inner query within the select statement.
• Lucas Basquerotto: In Postgres there isn't OUTER APPLY, but there is LATERAL, which should be equivalent. An example using it: stackoverflow.com/a/47926042/4850646

Answer 6

I liked the "array" answer so much I wanted to add a complete example for PostgreSQL 9.3, using the generic information schema. This allows us to keep the correlated subquery, and all of it's power. I'll definitely keep this in my back pocket.

Note that the array elements can't have mixed types, at least on 9.3.

SELECT
 table_data.table_schema,
 table_data.table_name,
 table_data.column_data[2] AS num_fields,
 table_data.column_data[3] AS min_column
FROM ( 
 SELECT table_schema,
 table_name,
 (SELECT ARRAY[c.table_name, COUNT(1)::text, MIN(c.column_name)]
 FROM information_schema.columns c
 WHERE c.table_name = t.table_name
 AND c.table_schema = t.table_schema
 GROUP BY c.table_name
 ) AS column_data
 FROM information_schema.tables t
 WHERE table_schema = 'information_schema'
 AND table_name LIKE 'column%'
) AS table_data;
 table_schema | table_name | num_fields | min_column
--------------------+---------------------+------------+--------------------------
 information_schema | column_domain_usage | 7 | column_name
 information_schema | column_privileges | 8 | column_name
 information_schema | column_udt_usage | 7 | column_name
 information_schema | columns | 44 | character_maximum_length
 information_schema | column_options | 6 | column_name

Answer 7

I just want to put this here for last resort, for everyone who uses database engine that does not support one or more of the other answers...

You can use something like:

SELECT (col1 || col2) as col3 

(With separator, or formatting col1 and col2 to specific length.) And later draw your data using sub-strings.

I hope someone finds it useful.

Answer 8

In DB2 for z/OS, use pack and unpack functions to return multiple columns in a subselect.

SELECT 
 *, 
 p.name AS name, 
 p.image, 
 p.price,
 unpack((select PACK (CCSID 1028,
 ps.price,
 ps.date)
 FROM product_special ps 
 WHERE p.id = ps.id
 AND ps.date < NOW() 
 ORDER BY ps.priority ASC, LIMIT 1)) .* AS (SPECIAL_PRICE double, DATE date)
FROM product p LEFT JOIN product_special ps ON (p.id = ps.id);

• Venryx: How is pack and unpack different from the array[...] operator usage seen here?: dba.stackexchange.com/a/226343/222697