[RESOLVED] select limit

LordRogaine

To simplify things, I have one table in my database with two fields. Field1_people, Field2_photos. There are hundreds of people in Field1_people. In Field2_photos the people in Field1_people can place up to three photos.

I'm trying to make a select statement that limits the results to the first eight people from Field1_people, while at the same time I limit the amount of photos that are retrieved per person to one only. So there are two limits 0,8 for Field1_people and limit 0,1 for Field2_photos. How would I put both limits in a select statement?

bpat1434

GROUP BY Field1_people

If you group by the people, you'll only get one result per person while you can limit the total number of people returned.

LordRogaine

That is one solution. Thank you, but what if I wanted to limit it to two photos per person instead of one?

tjohnson_nb

I don't understand how you can have up to 3 photos in 1 field?

LordRogaine

three seperate entries for field2 by the same person in field1. So you are seeing the same name in Field1 three times, one time for each photo submitted

tjohnson_nb

I would be tempted to have 4 fields, 1 person field and 3 photo fields. Then you could select 8 persons and as many photos as you wanted?

Piranha

tjohnson_nb wrote:
I would be tempted to have 4 fields, 1 person field and 3 photo fields. Then you could select 8 persons and as many photos as you wanted?

I suggest that you search for normalization. Because what would happen if there is a change and there can be 10 photos per person? Unlimited photos?

ragedigital

Not sure it can happen in one, but I would use 2 statements:

SELECT field_1 people and LIMIT it to the 8 people (0,8)
- Then cycle through them and LIMIT the field2_photos in the 2nd SELECT (0,1)

Piranha

As long as you only want one photo per person it is quite simple really, bpat have the solution for you. If you want more than one photo you can do the following (I haven't tested it, so there may be a little bug).

SELECT a1.people, a1.photo, a2.photo, a3.photo
FROM table as a1
LEFT JOIN table as a2  // one left join for each additional photo is needed
    ON a1.people = a2.people
    AND a1.photo != a2.photo
LEFT JOIN table as a3  // in all left joins you need to make sure that the photo have not been selected before
    ON a1.people = a3.people
    AND a1.photo != a3.photo
    AND a2.photo != a3.photo
GROUP BY a1.people
LIMIT 0, 8

The result would look something like this:

Person     Photo1    Photo2    Photo3
Kalle      Kalle1    Kalle2    Kalle3
Olle     Olle1    Olle2     NULL

The null will be there if there are not 3 unique photos from the person. If you only want persons that have 3 photos you do the following instead:

SELECT a1.people, a1.photo, a2.photo, a3.photo
FROM table as a1
LEFT JOIN table as a2  // one left join for each additional photo is needed
    ON a1.people = a2.people
LEFT JOIN table as a3 
    ON a1.people = a3.people
WHERE a1.photo != a2.photo // Now you check all photos in the main select
AND a1.photo != a3.photo
AND a2.photo != a3.photo
GROUP BY a1.people
LIMIT 0, 8

tjohnson_nb

Piranha wrote:
I suggest that you search for normalization. Because what would happen if there is a change and there can be 10 photos per person? Unlimited photos?

In this case I would probably have 2 tables, one for people and one for photos. you could then select 8 people and run a 2nd query selecting as many photos as you want?

Piranha

tjohnson_nb wrote:
In this case I would probably have 2 tables, one for people and one for photos. you could then select 8 people and run a 2nd query selecting as many photos as you want?

That is one way to do it. As I understand the poster he have a person ID and a photo in that table, nothing more. And that is the way to build the tables.

About retrieving more than one photo you could do as you suggest or you could do as I suggest above.

LordRogaine

Thanks for all the help!