Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

Use a subquery (as displayed) or 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.

share|improve this answer
    
Is join same as inner join? –  Ashwin Jan 14 '13 at 4:07
    
@Ashwin: Yes, the keyword INNER is redundant noise. –  Erwin Brandstetter Jan 14 '13 at 4:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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