How to return multiple values when using SELECT EXISTS in postgresql

Question

I have the following SQL query:

SELECT EXISTS (SELECT r.id FROM Rules r INNER JOIN rule_t c on c.id=r.rule_t.id 
INNER JOIN user u on u.id = r.user_id
WHERE u.fmnum='2813'
AND c.name='default') ::int

Is there a way I can modify this so that I get two values back, the INT from the EXISTS method, and r.id?

I know that I can change the query so that I remove the EXISTS method... if the sub select returns anything at all, then I know the record exists... but I'm just wondering if its possible to do the above.

Thanks.

EDIT 1

I'm testing the following code in a new query window in pgadmin3...

SELECT *
FROM  (
   SELECT TRUE, r.id
   FROM   rules  r
   JOIN   rule_t c on c.id = r.rule_t.id 
   JOIN   user   u on u.id = r.user_id
   WHERE  u.fmnum = '2813'
   AND    c.name = 'default'
   );

But I'm getting the following error:

ERROR: subquery in FROM must have an alias LINE 2: ( ^ HINT: For example, FROM (SELECT ...) [AS] foo.

EDIT 2

SELECT *
FROM  (
   SELECT TRUE, r.id
   FROM   rules  r
   JOIN   rule_t c on c.id = r.rule_t.id 
   JOIN   user   u on u.id = r.user_id
   WHERE  u.fmnum = '2813'
   AND    c.name = 'default'
   ) AS x;

I don't understand the question. If you know that the right way to do this is by getting the value from the subselect, what are you really asking? — Gordon Linoff
– Gordon Linoff, Commented Dec 27, 2013 at 14:33
I'm asking if I can return two values, one from EXISTS and one from subquery. Some consumers of the method may want just the boolean from Exists() vs. others who would want both. The more I can do here, the less I have to do in the methods that call this sql query. — dot
– dot, Commented Dec 27, 2013 at 14:36
You can return the value in a subquery and then derive the exists value from that. — Gordon Linoff
– Gordon Linoff, Commented Dec 27, 2013 at 14:38

Erwin Brandstetter · Accepted Answer · 2013-12-27 15:06:48Z

1

SELECT 1 AS does_exist, r.id
FROM   rules  r
JOIN   rule_t c on c.id = r.rule_t.id 
JOIN   user   u on u.id = r.user_id
WHERE  u.fmnum = '2813'
AND    c.name = 'default'
LIMIT  1;  -- may or may not be needed.

This does what you seem to be asking for: you get two columns. But you get no row if nothing is found.

If you want a row, even if nothing is found, you need a subquery:

SELECT sub.t_id IS NOT NULL AS does_exist, sub.id
FROM  (SELECT 1) x  -- dummy to guarantee 1 row
LEFT JOIN (         -- LEFT JOIN is crucial
    SELECT r.id
    FROM   rules  r
    JOIN   rule_t c on c.id = r.rule_t.id 
    JOIN   user   u on u.id = r.user_id
    WHERE  u.fmnum = '2813'
    AND    c.name = 'default'
    LIMIT  1        -- may or may not be needed.
    ) ON TRUE;      -- join condition is always true

Or, simpler / faster:

SELECT 1 AS does_exist, r.id
FROM   rules  r
JOIN   rule_t c on c.id = r.rule_t.id 
JOIN   user   u on u.id = r.user_id
WHERE  u.fmnum = '2813'
AND    c.name = 'default'

UNION  ALL
SELECT 0, NULL
LIMIT  1;

edited Dec 27, 2013 at 15:06

answered Dec 27, 2013 at 14:38

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

6 Comments

dot Over a year ago

Hm.. I just copied and pasted your code but... I'm getting an error message saying that the subquery in FROM must have an alias.. Please check out Edit 1 in my post

Erwin Brandstetter Over a year ago

@dot: The alias for the subquery was missing. And my explanation was slightly incorrect, which is fixed, too.

dot Over a year ago

k. i guess we posted around the same time. I just added an Edit 2... which is kinda similar to your new post.

dot Over a year ago

I like it! Very simple and self documenting

dot Over a year ago

The only thing that would be cool is if I could still get 0 if it doesn't exist.

|

Collectives™ on Stack Overflow

How to return multiple values when using SELECT EXISTS in postgresql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related