Menu
I have tables: documents, languages and document_languages. Documents exist in one or more languages and this relationship is mapped in document_languages.
Imagine now I want to display the documents and all of its languages on a page, and paginate my result set to show 10 records on each page. There will be a WHERE statement, specifying which languages should be retrieved (ex: en, fr, it).
Even though I only want to display 10 documents on the page (LIMIT 10), I have to return more than 10 records if a document has more than one language (which most do).
How can you combine the WHERE statement with the LIMIT in a single query to get the records I need?
498
LIMIT n is an alternative syntax to the FETCH FIRST n ROWS ONLY. The OFFSET clause specifies the number of rows of the result table to skip before any rows are retrieved, and must be used with the LIMIT clause. The OFFSET clause instructs the server where to start returning rows within the query result.
Assume we wish to select all records from 1 - 30 (inclusive) from a table called "Orders". The SQL query would then look like this: $sql = "SELECT * FROM Orders LIMIT 30"; When the SQL query above is run, it will return the first 30 records.
In MySQL, the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments that are offset and count. The value of both the parameters can be zero or positive integers.
Use sub query to filter only documents records
select * from
(select * from documents limit 0,10) as doc,
languages lan,
document_languages dl
where doc.docid = dl.docid
and lan.langid = dl.langid
Check sub query doc as well
http://dev.mysql.com/doc/refman/5.0/en/from-clause-subqueries.html http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
158
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
