limit by list- sql

[felito]

I am trying this code, that works well.

 

SELECT U.username, U.update_photo
FROM users U

INNER JOIN lists_has_users H 
ON U.id_user = H.users_id_user

INNER JOIN lists L
ON L.id_lists = H.lists_id_lists

WHERE L.users_id_user = ?   

 

Now the problem. This query will select all the users of the lists. What i want is limit the number of output members by list.

 

For example, two members by each list (id_lists).

 

Any idea? thanks

 

[fenway]

You mean only members that are part of 2 lists?

[felito]

To clarify.

 

I have this structure:

table users
id_user username update_photo
1 Pedro photo
2 Joao photo
3 Carlos photo
4 José photo
5 António photo
6 Toni photo

table  lists
id_lists name_list users_id_user
1 primeira_lista 9 // owner of the list
2 segunda_lista 9 // owner of the list

table lists_has_users
users_id_user lists_id_lists
1 1 - first
2 1 - second
3 1 --------
4 2 - first
5 2 - second
6 2 --------

 

 

The outuput that I want is:

1 Pedro photo

2 Joao photo

3 Carlos photo

4 José photo

5 António photo

6 Toni photo

 

 

My idea is a sample of two members of each list. If the user want to see more members he can click in view more button that will open a new page with all members of this list.

 

Did you understand what i mean?

[fenway]

You want a random 2 from each list/

[felito]

yes, random or ordered alphabetically. This query is more difficult than I thought.  :-\

 

thanks

[fenway]

I answer this all the time the same way -- http://explainextended.com/2009/03/06/advanced-row-sampling/.