Return multiple rows with subquery in SQL select clause

Question

Is there a way to return multiple rows in a SQL select clause, when your subquery does not have a join/key field? My query currently looks like this. I'm wanting to return list of users and a list of contracts when there is no key between the users and contracts.

There is a handful of users, but a whole lot of contracts and I'm wanting to generate a list of each contractID next to each userID.

select 
userid,
(select contractid from contracts) as contractid
from users

New here, but the suggestion for a cross join did what I wanted. thanks!

If you want to associate contracts with users, you must have a join field. If, on the other hand, you just want a list of user IDs and contract IDs, you can use the UNION ALL keyword. — RobJarvis
– RobJarvis, Commented Apr 29, 2021 at 21:48
"Is there a way to return multiple rows in a Scalar Subquery?" -- No, but you can use other strategies to get the result you want. — The Impaler
– The Impaler, Commented Apr 29, 2021 at 22:07
Use the str_agg function learn.microsoft.com/en-us/sql/t-sql/functions/… EDIT: Never mind after re-reading your question I think I had the wrong idea, but I'll leave the comment here just in case. — Gonkers
– Gonkers, Commented Apr 30, 2021 at 4:17

The Impaler · Accepted Answer · 2021-04-29 22:09:59Z

1

You can generate all possible combinations of users with constracts by performing a CROSS JOIN. For example:

select 
  u.*,
  c.*
from users u
cross join contracts c

You can, then, filter the result by appending WHERE <condition> as needed.

answered Apr 29, 2021 at 22:09

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

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

Collectives™ on Stack Overflow

Return multiple rows with subquery in SQL select clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related