Selection first 2 from 2nd table 1

[r3ck]

Hi All.

I've trying to select the first 2 clients from each household. I'm sort of close but I can't see where to go from here.

The following code selects those households that have less than 3 clients in them. I'd like to select every household and also up to 2 clients belonging to that household.

SELECT h.RecordID, [Adults]+[Children] AS Total, c.FirstName, c.LastName, h.Street, h.Address, h.Suite
FROM tblHouseholds AS h INNER JOIN tblClients AS c ON c.RecordID = h.RecordID
WHERE ( SELECT COUNT(*)
FROM tblClients
WHERE RecordID = c.RecordID ) < 3
ORDER BY h.RecordID;

Thanks for any help.

Rick
--

 

[PHV]

select the first 2 clients from each household
How would you ORDER tblClients for each RecordID ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r3ck]

>> select the first 2 clients from each household
>How would you ORDER tblClients for each RecordID ?

So this is a pretty basic question? Changing to ORDER BY tblClients.RecordID gives the same result so that's not it.

I'm new to SQL and had trouble getting this far, although I'm reading as fast as I can. My previous experience has been writing queries and reports.

We're a non-profit and a couple of us are modifying our DB, breaking out the clients to a separate table to make writing queries easier. But I'm not a programmer (is there a Novice option for user type? I didn't see one) and I haven't yet got my head around this.

Rick
--

 

[PHV]

I think you didn't understand my question.
Let me rephrase.
How would you know which are the 2 first tblClients rows from all the rows having the same RecordID ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r3ck]

> I think you didn't understand my question.

Yeah, jumped to confusion.

I edited the code down to what I thought was the essence and went too far. I'm trying to get the oldest 2 clients for each household under the assumption that they're the head of the household.

SELECT h.RecordID,
 [Adults]+[Children] AS Total,
 c.FirstName,
 c.LastName,
 h.Street,
 h.Address,
 h.Suite,
 (Date()-[Birthday])/365 AS Age
FROM tblHouseholds AS h INNER JOIN tblClients AS c
ON h.RecordID = c.RecordID
WHERE (SELECT COUNT(*) FROM tblClients WHERE RecordID = c.RecordID))<3
ORDER BY h.RecordID,
 (Date()-[Birthday])/365 DESC;

Tnx.
Rick
--

 

[PHV]

In which tables are Adults, Children and Birthday ?

 

[r3ck]

> In which tables are Adults, Children and Birthday ?

Hi,

Birthday is in tblClients and Adults, Children are in tblHouseholds.

SELECT h.RecordID,
 [h.Adults]+[h.Children] AS Total,
 c.FirstName,
 c.LastName,
 h.Street,
 h.Address,
 h.Suite,
 (Date()-[c.Birthday])/365 AS Age
FROM tblHouseholds AS h INNER JOIN tblClients AS c
ON h.RecordID = c.RecordID
WHERE (SELECT COUNT(*) FROM tblClients WHERE RecordID = c.RecordID)<3
ORDER BY h.RecordID,
 (Date()-[c.Birthday])/365 DESC;

This returns those households containing up to 2 clients. What's needed is all households and up to 2 clients (including 0, I suppose, if there was data entry.)

I'm finding SQL very powerful but I have much work to do to be able to use it.

Thanks.

Rick
--

 

[PHV]

What about this ?

SELECT h.RecordID,
 [h.Adults]+[h.Children] AS Total,
 c.FirstName,
 c.LastName,
 h.Street,
 h.Address,
 h.Suite,
 (Date()-Nz(c.Birthday,Date()))/365 AS Age
FROM tblHouseholds AS h LEFT JOIN (
SELECT A.RecordID, A.FirstName, A.LastName, A.Birthday
  FROM tblClients AS A INNER tblClients AS B
    ON A.RecordID=B.RecordID AND A.Birthday<=B.Birthday
 GROUP BY A.RecordID,A.FirstName,A.LastName,A.Birthday HAVING Count(*)<=2
) AS c ON h.RecordID = c.RecordID
ORDER BY h.RecordID,
 (Date()-Nz(c.Birthday,Date()))/365 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r3ck]

Hi PH.

Thanks for this. It gives 'Syntax error in JOIN operation' and I'm not sure how to go about debugging yet. It's given me something to work with.

Rick
--

 

[PHV]

Sorry for the typo, replace this:
FROM tblClients AS A INNER tblClients AS B
with this:
FROM tblClients AS A INNER JOIN tblClients AS B

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r3ck]

> Sorry for the typo

Well, I should have been able to see that.

Thanks for this. I thought the solution was just a little down the road as I learned more SQL but I wasn't even close.

I'm going to munch on this a bit and probably get back with a couple questions if you don't mind. I'm guessing that the Thanks link closes the thread so I won't do that right now.

But thank-you for sharing your knowledge. It's much appreciated.

Rick
--

 

[lespaul]

there is no "close" the thread option....it's open for a specific amount of time (like a year or something???)...the thanks just puts a star on the thread so others know there was helpful information in it.

You should go ahead and give PHV a star.....

Leslie