CODEWITHSUNDEEP
mysql
David Jones
David Jones

Reputation: 10239

MySQL: How to query multiple tables and apply a LIMIT to only one?

Suppose I have one table with items and another table with subitems. I would like to return all of the subitems associated with a limited number of items. In essence I would like to join these two queries:

SELECT * FROM subitem

SELECT * FROM item LIMIT 10

where subitem.item = item.id:

I tried this:

SELECT * FROM subitem INNER JOIN item ON subitem.item = item.id LIMIT 10

However, this query only returns 10 subitems (as you would expect). I would like to retrieve all the subitems while limiting only the number of items to 10. How can I achieve this?

Upvotes: 4

Views: 3566

Answers (4)

Anil
Anil

Reputation: 1

how to use limit in this query

select tbl_thread.id,user_id,name,tbl_User.alert_flag,thread_name,thread_status,(select CAST(count(*) as UNSIGNED) from tbl_message where thread_id=tbl_thread.id AND read_status='0' and msg_from='EU' ) as unread,thread_timestamp,(select CAST(max(id) as UNSIGNED) from tbl_message where thread_id=tbl_thread.id) as max_id from tbl_thread,tbl_User where tbl_User.UserID=user_id ;

Upvotes: -1

Akash KC
Akash KC

Reputation: 16310

Try with following query: 

select * from item,subitem where id = item and 
  id in (select id from item limit 10)

If there is problem with IN in LIMIT, try with following query:

select * from (select id from item limit 10) as i1, subitem where id =item ;

Upvotes: 3

Ray
Ray

Reputation: 2728

Maybe something like:

SELECT * FROM subitem WHERE item IN (SELECT id FROM item LIMIT 10);

Upvotes: 0

Holger Brandt
Holger Brandt

Reputation: 4354

This will get you 10 items. However, you should add a WHERE clause and ORDER BY clause to get the items you are looking for.

SELECT * FROM subitem INNER JOIN 
              (SELECT * FROM items LIMIT 10) AS I
              ON subitem.item = I.id

Upvotes: 5

Related Questions