Get first 3 lines of table for each line of another table

Asked Oct 05 '16 at 15:00

Active Oct 05 '16 at 15:11

Viewed 31 times

I have to tables:

Table A:
CName           |   Id
-------------------------------
John Smith      | 1
Jane Doe        | 4
John Smith      | 9
Joe Bloggs      | 10

Table B:
Id       |   IdUser          |   state
-------------------------------
1        |    1              | PD
2        |    4              | AS
3        |    1              | JT
4        |    1              | PD
5        |    1              | PD
6        |    4              | JT
7        |    1              | JT
8        |    4              | PD
9        |    4              | JT

The relation of tables is A can have several B and B can have only one A (A1-*B).

I need to create a query to get the first 3 lines of table B, for each line of table A

So in this example the result will be:

CName           |   Id(A)   |    Id(B)     |    state
-------------------------------------------------------
John Smith      | 1         |      1       |     PD
John Smith      | 1         |      3       |     JT
John Smith      | 1         |      4       |     PD
Jane Doe        | 4         |      2       |     AS
Jane Doe        | 4         |      6       |     JT
Jane Doe        | 4         |      8       |     PD

edited Oct 05 '16 at 15:11

asked Oct 05 '16 at 15:00

Iban Arriola

2,526
9
41
88

Which DBMS are you using? – Oct 05 '16 at 15:03
And how do you determine *first 3*? – dnoeth Oct 05 '16 at 15:06
mysql is the DBMS. I didn't put but there is another column with B table id. I will edit it right now – Iban Arriola Oct 05 '16 at 15:07
Table B appears to have no PK. – Strawberry Oct 05 '16 at 15:09
I just edited the tables... it was an example to show what I wanted and I forgot the PK of table B sorry – Iban Arriola Oct 05 '16 at 15:12

Get first 3 lines of table for each line of another table

0 Answers0