4

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?

1 Answer 1

7

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.

2
  • Is join same as inner join?
    – Ashwin
    Jan 14, 2013 at 4:07
  • @Ashwin: Yes, the keyword INNER is redundant noise. Jan 14, 2013 at 4:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.