Hi,

I have two tables, event and photo. Event holds all the information
for an event whilst photo holds all photos uploaded for that event.

Now I have a problem. I want to get a list of all the event.ids but I
want them ordered by the amount of photos uploaded in the other table.
The trouble here is that some events do not have photos uploaded and
though should be bottom but still be there. I have tried over and
over again but I think I have got my selfed confused. If anyone has
any ideas I would be very grateful.

Thanks

Iain

Sorry, I figured it out in the end. My sql looks like this:

SELECT * FROM event LEFT JOIN (SELECT count(*), event_id FROM photos
GROUP BY event_id ORDER BY count(*) DESC) As a ON event.id =
a.event_id;


On Dec 18, 2:21 pm, Iain Adams <aca04...@shef.ac.uk> wrote:
> Hi,
>
> I have two tables, event and photo. Event holds all the information
> for an event whilst photo holds all photos uploaded for that event.
>
> Now I have a problem. I want to get a list of all the event.ids but I
> want them ordered by the amount of photos uploaded in the other table.
> The trouble here is that some events do not have photos uploaded and
> though should be bottom but still be there. I have tried over and
> over again but I think I have got my selfed confused. If anyone has
> any ideas I would be very grateful.
>
> Thanks
>
> Iain

On Dec 18, 11:00 pm, Iain Adams <aca04...@shef.ac.uk> wrote:
> Sorry, I figured it out in the end. My sql looks like this:
>
> SELECT * FROM event LEFT JOIN (SELECT count(*), event_id FROM photos
> GROUP BY event_id ORDER BY count(*) DESC) As a ON event.id =
> a.event_id;
>
> On Dec 18, 2:21 pm, Iain Adams <aca04...@shef.ac.uk> wrote:
>
> > Hi,
>
> > I have two tables, event and photo. Event holds all the information
> > for an event whilst photo holds all photos uploaded for that event.
>
> > Now I have a problem. I want to get a list of all the event.ids but I
> > want them ordered by the amount of photos uploaded in the other table.
> > The trouble here is that some events do not have photos uploaded and
> > though should be bottom but still be there. I have tried over and
> > over again but I think I have got my selfed confused. If anyone has
> > any ideas I would be very grateful.
>
> > Thanks
>
> > Iain

I think you've overcomplicated it...

SELECT e.*,COUNT(p.event_id) qty FROM event e
LEFT JOIN photos p ON p.event_id = e.event_id
GROUP BY p.event_id
ORDER BY qty DESC

Also IMO your table naming convention is a little inconsistent.