SQL One to Many JOIN Without Subquery?

I've got a question related to a 1 to n query which I was hoping someone could shed some light on.

Here's what I'm looking to accomplish.

I have several homes each home may have multiple occupants. I'd like to select 5 homes and all the associated occupants. I know of two ways of doing. The most common of these is a select statement to get the 5 homes and then a second select statement using IN condition to get associated occupants. The other way to do this is a variation of th开发者_如何学Ce two queries using one query with a subquery.

Is there any other way to accomplish without the use of a subquery?

Update:

Seems the consensus is this is not possible with the use of a subquery.

---

You'd normally use a JOIN to get related data from both tables

SELECT
   *
FROM
   (SELECT TOP 5 * FROM Homes ORDER BY something) H
   JOIN
   Occupants O ON H.HomeID = O.HomeID

However, the TOP 5 needs to be applied to Homes only. It'd be LIMIT with MySQL, not TOP too.

Edit:

A window/ranking function still needs a sub-query or some indirection such as a CTE to allow filtering on the generated number.

SELECT
   *
FROM
   (SELECT *, ROW_NUMBER() OVER (ORDER BY something) AS RowNum
    FROM Homes) H
   JOIN
   Occupants O ON H.HomeID = O.HomeID
WHERE
   RowNum <= 5

;WITH CTE AS
(
   SELECT
    *, ROW_NUMBER() OVER (ORDER BY something) AS RowNum
   FROM
    Homes
 )
SELECT
   *
FROM
   CTE H
   JOIN
   Occupants O ON H.HomeID = O.HomeID
WHERE
   RowNum <= 5

---

You could do something like this, depending on DB engine support:

select *
from
   (
       select top 5
           home_id,
           ... -- other columns
       from 
           home
       order by
           awesomeness desc
   ) h
       inner join occupant o on o.home_id = h.home_id

---

Use an inner join. Each home will be listed multiple times, once for each occupant in that home.

select Home.HomeName, Occupant.OccupantName from Home 
inner join Occupant on Home.Id=Occupant.HomeId

And then you'll get:

Home1 OccupantA
Home1 OccupantB
Home2 OccupantC
Home2 OccupantD

---

If you are using SQL Server (2005 and higher) you could use a CTE

WITH MyTopFive (MyId, MyOtherField, SomeField) AS
(
SELECT TOP 5 MyId, MyOtherField, SomeField FROM Homes ORDER BY SomeField
)

SELECT * 
FROM Mytopfive t5
JOIN Occupants o ON o.myid = t5.myid

gbn has the best generic database solution though.

---

sql server specific

yes you could use a join to do this. you did not show your db schema but if I take a guess you probably want something like this:

 select *, RANK() OVER (PARTITION BY home.homeid ORDER BY home.homeid DESC) as houserank
 from home
 join occupant on home.homeid = occupant.homeid
 where  houserank < 6