From: | "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | "Subquery must return only one column" & query optimization |
Date: | 2008-12-15 12:12:54 |
Message-ID: | E6A0649F1FBFA3408A37F505400E7AC2118531@email.attiksystem.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I was trying to run this query this morning:
--------------------------
SELECT
r.*,
(
SELECT
rl.reminder_header,
rl.reminder_footer
FROM reminder_levels AS rl
WHERE rl.lookup =
(
SELECT MAX(reminder_level_lookup)
FROM reminders
WHERE customer_id = r.customer_id
)
)
FROM reminders AS r
--------------------------
Postgresql replied that:
--------------------------
ERROR: subquery must return only one column
SQL state: 42601
--------------------------
Is there a way to avoid writing:
--------------------------
SELECT
r.*,
(
SELECT
rl.reminder_header
FROM reminder_levels AS rl
WHERE rl.lookup =
(
SELECT MAX(reminder_level_lookup)
FROM reminders
WHERE customer_id = r.customer_id
)
) AS reminder_header,
(
SELECT
rl.reminder_footer
FROM reminder_levels AS rl
WHERE rl.lookup =
(
SELECT MAX(reminder_level_lookup)
FROM reminders
WHERE customer_id = r.customer_id
)
) AS reminder_footer
FROM reminders AS r
--------------------------
... which works, but runs twice the same subselect block:
--------------------------
FROM reminder_levels AS rl
WHERE rl.lookup =
(
SELECT MAX(reminder_level_lookup)
FROM reminders
WHERE customer_id = r.customer_id
)
--------------------------
Thanks,
Philippe Lang
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-12-15 13:17:57 | Re: "Subquery must return only one column" & query optimization |
Previous Message | Louis-David Mitterrand | 2008-12-15 06:51:32 | Re: optimizing a query |