CODEWITHSUNDEEP
sql-servert-sql
Justin Turner
Justin Turner

Reputation: 3

Return multiple rows with subquery in SQL select clause

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!

Upvotes: 0

Views: 99

Answers (1)

The Impaler
The Impaler

Reputation: 48770

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.

Upvotes: 1

Related Questions