Get Record ID from Looping Records

Question

Here is the story

I have two mysql tables:

master

id, descr
1, master 1
2, master 2

the details

id, descr, masterid
1, test 1, 1
2, test 2, 1
3, test 3, 1
4, test 1, 2
5, test 2, 2
6, test 3, 2

I need a query to get the rows from the first table and the looped column from the details

For instance:

first do:

1,master 1, 1

2,master 2, 4

execute the second

1,master 1, 2<- From the details table

2,master 2, 5<- From the details table

third run

1,master 1, 3<- From the details table

2,master 2, 6<- From the details table

Fourth frame:

1,master 1, 1

2,master 2, 4

I think I need to add a column to the main table in order to save the last selected record from the details table, but I cannot design the query.

Any ideas?

Thank. Vangelis

Answer 1

The thing is, you can only do this with SQL. But it will be session related . By this I mean this request:

SELECT
  master.*,
  o.id AS oid
FROM
  master
    INNER JOIN
    (SELECT
      d.*,
      @num:=IF(@id=masterid, @num:=@num+1, 1) AS num,
      @id:=masterid
    FROM
      (SELECT * FROM details ORDER BY masterid) AS d
      CROSS JOIN 
       (SELECT @id:=0, @num:=0) AS init
     ) AS o
    ON master.id=o.masterid
    INNER JOIN
    (SELECT 
       masterid,
       COUNT(id) AS c 
     FROM 
       details 
     GROUP BY 
       masterid) AS counts
    ON master.id=counts.masterid
    CROSS JOIN 
      (SELECT @RUN_COUNT:=IF(@RUN_COUNT IS NULL, 1, @RUN_COUNT+1)) AS r
WHERE
  o.num=IF(!(@RUN_COUNT%(counts.c)), counts.c, @RUN_COUNT%(counts.c));

: @RUN_COUNT. . : , :

(SELECT @RUN_COUNT:=IF(@RUN_COUNT IS NULL, 1, @RUN_COUNT+1)) AS r

- , , , , , . , , .

SQLFiddle , ( @RUN_COUNT NULL, , )

, (, , 4 masterid=1 3 masterid=2). - , @RUN_COUNT . , .

Answer 2

, , - php, sql, , .. php , 2, , ,

SELECT 
    m.id AS master_id, 
    m.descr, 
    d.id AS detail_id
FROM master m
JOIN details d ON d.masterid = m.id