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?
Best Answer
Use a subquery (as displayed) or CTE for that purpose:
USING (qid, gid)
is just a shortcut forON q.qid = a.qid AND q.gid = a.gid
with the side effect that the two columns are only included once in the result.