Retrieving data from inner join using LIMIT and OFFSET

Question

These are my two tables:
table1

qid[PK] |gid[PK] |abcd | xyz | date
---------------+---------+---------+------+------------
 00001 | qwe | 54 | a | 1994年11月29日
 00002 | asd | 0 | s | 1994年11月29日
 00003 | azx | 50 | 0.25 | 1994年11月27日

table2

qid[PK] | gid[PK] | user[PK]
------------+---------+--------
 00001 | qwe | shreya
 00001 | qwe | nagma
 00001 | qwe | koena
 00001 | qwe | paoli
 00002 | asd | anushka
 00002 | asd | angelina
 00003 | azx | jolie
 00003 | azx | scarlett
 00003 | azx | sharon
 00003 | azx | jeniffer

As you can see for each qid and gid of table1 there can be any number of rows in table2.

My requirement :
I want to retrieve all the users for the first 10 values of qid and gid from the offset.

My Query :

 select * from table1 q inner join table2 a on q.qid=a.qid
 and q.gid=a.gid order by q.date desc limit 10 offset ?

But this query will retrieve 10 rows from offset from the inner join, But I want all rows from table 2 for the 10 rows[offset] from table1.

How can this be achieved?

Answer 1

Use a subquery (as displayed) or a CTE for that purpose:

SELECT *
FROM (
 SELECT qid, gid
 FROM table1
 ORDER BY date DESC
 LIMIT 10
 OFFSET ?
 ) q
JOIN table2 a USING (qid, gid);

USING (qid, gid) is just a shortcut for ON q.qid = a.qid AND q.gid = a.gid with the side effect that the two columns are only included once in the result.

• Ashwin: Is join same as inner join?
• Erwin Brandstetter: @Ashwin: Yes, the keyword INNER is redundant noise.