Limit number of results in joined table

[imstillatwork]

example: Table A has many children in table B. During my JOIN I want to limit the number of children (B) per row in Parent (A)

how would I do this?

thanks!

 

[JonFer]

What are the unique keys for the tables? Can you post some sample records with the important fields? Do you care which of the child records you exclude?

 

[imstillatwork]

nothing special at all. heres an example. I dont need any specific rows from table A, just a limited amount, say...2, even though there is 3 B_ID=s in table A.

table A
_____________________
|A_ID | A_NAME | B_ID |
|---------------------|
| 1 | bah | 4 |
|---------------------|
| 2 | hum | 4 |
|---------------------|
| 3 | bug | 3 |
|---------------------|
| 4 | data | 3 |
|---------------------|
| 5 | data | 3 |
|---------------------|

table B
______________
|B_ID | B_NAME |
|--------------|
| 3 | stuff |
|--------------|
| 4 | here |
|--------------|

 

[PHV]

Have you tried to play with the DISTINCT predicat or the GROUP BY clause ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[imstillatwork]

No, but I'm not sure how that would get what I was looking for either, unless your hinting at something.

I want the results like this:

_____________________________
|A_ID | A_NAME | B_ID | B_NAME|
|-----|--------|------|-------|
| 1 | bah | 4 | here |
|-----|--------|------|-------|
| 2 | hum | 4 | here |
|-----|--------|------|-------|
| 3 | bug | 3 | stuff |
|-----|--------|------|-------|
| 4 | data | 3 | stuff |
|-----|--------|------|-------|

I will do the grouping in my server side scripting.

 

[PHV]

Something like this ?
SELECT A.A_ID, A.A_NAME, A.B_ID, B.B_NAME
FROM tableA A INNER JOIN tableB B ON A.B_ID=B.B_ID
WHERE A.A_ID<=4

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

PHV - I don't see how that works for a general solution.

imstillatwork - One option is to use a subquery to rank the rows in tableA for each B_ID and then put a criteria that the rank is <= to your maximum rows. Assuming the A_ID field is unique, you can do add this subquery in the WHERE clause:

Select ...
From tableA Inner Join tableB On ...
Where (Select Count(*) from tableA as A where A.B_ID=tableA.B_ID And A.A_ID <= tableA.A_ID) <= [Enter max A rows]

It will give you the tableA rows with the lowest A_IDs. You can also switch the criteria in the subquery to get the highest IDs.

 

[PHV]

JonFer, I just guessed the lack of INNER JOIN.

 

[imstillatwork]

From tableA Inner Join tableB On ...
Where (Select Count(*) from tableA as A where A.B_ID=tableA.B_ID And A.A_ID <= tableA.A_ID) <= [Enter max A rows]

that makes sense. I'll play with that. If nothing else i can manipulate the recordset after its returned to the web server and do whatever i need in coldfusion, but would rather let the database take care of database work.

thanks!