PostgreSQL: subquery has too many columns when attempting to add filters

Question

SELECT users.id as user_id, boats.id as boat_id
FROM users
CROSS JOIN boats
WHERE users.id NOT IN 
   (SELECT users.id as user_id, boats.id as boat_id FROM users 
    LEFT JOIN rentals ON (users.id=rentals.user_id)
    LEFT JOIN boats on (boats.id=rentals.boat_id)
    WHERE users.id=rentals.user_id)
ORDER BY users.id

How can I split these JOINs up so I no longer get the "subquery has too many columns" error?

You are comparing one column (`users.id`) with two columns from the sub-query - obviously that won't work. You need to remove `boats.id` from the sub-query — , Jun 28 '19 at 19:52
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS You have a syntax error. Read the grammar. Show as much as possible that constituent subexpressions are OK. Make it clear that your question is about *that error* & ask re your overall goal later in a new post. — philipxy, Jun 28 '19 at 22:29
(This isn't causing your syntax error, but--) Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Jun 28 '19 at 22:30
Possible duplicate of [SQL: subquery has too many columns](https://stackoverflow.com/questions/12238621/sql-subquery-has-too-many-columns) — philipxy, Jun 28 '19 at 22:38

score 1 · Answer 1 · answered Jun 28 '19 at 19:52

Your subquery must have the exact number of columns than the external values you are comparing against.

For example, you could use a single column:

SELECT users.id as user_id, boats.id as boat_id
FROM users
CROSS JOIN boats
WHERE users.id NOT IN 
   (SELECT users.id FROM users -- fixed here
    LEFT JOIN rentals ON (users.id=rentals.user_id)
    LEFT JOIN boats on (boats.id=rentals.boat_id)
    WHERE users.id=rentals.user_id)
ORDER BY users.id

Or use a tuple with two columns:

SELECT users.id as user_id, boats.id as boat_id
FROM users
CROSS JOIN boats
WHERE (users.id, boats.id) NOT IN -- fixed here
   (SELECT users.id as user_id, boats.id as boat_id FROM users 
    LEFT JOIN rentals ON (users.id=rentals.user_id)
    LEFT JOIN boats on (boats.id=rentals.boat_id)
    WHERE users.id=rentals.user_id)
ORDER BY users.id

score 1 · Accepted Answer · answered Jun 28 '19 at 20:06

Your query is a bit inscrutable. I assume this is the logic that you really want:

SELECT u.id as user_id, b.id as boat_id
FROM users u CROSS JOIN
     boats b
WHERE not exists (SELECT 1
                  FROM rentals r
                  WHERE r.user_id = u.id AND
                        r.boat_id = b.id
                 );

This returns the user/boat combinations that don't have a rental.

score 0 · Answer 3 · answered Jun 28 '19 at 19:57

You just leave off the boats.id in the subquery:

SELECT users.id as user_id, boats.id as boat_id
FROM users
CROSS JOIN boats
WHERE users.id NOT IN 
   (SELECT users.id as user_id
    FROM users 
    LEFT JOIN rentals ON (users.id=rentals.user_id)
    LEFT JOIN boats on (boats.id=rentals.boat_id)
    WHERE users.id=rentals.user_id)
ORDER BY users.id

However, there's no reason to add the LEFT JOIN to boats in the subquery as it's not used. Also, the WHERE clause is negating the LEFT JOIN to rentals anyway, so this could simplify to:

SELECT users.id as user_id, boats.id as boat_id
FROM users
CROSS JOIN boats
WHERE not exists (
    SELECT 1
    FROM rentals 
    WHERE rentals.user_id = users.id
);

I'm also a bit skeptical that you want a CROSS JOIN there, but I don't have enough info to determine exactly what you want.

PostgreSQL: subquery has too many columns when attempting to add filters

3 Answers3