The need is to sort the index and browse view of members by if they have a profile photo or not; sort with profile photos at the top of the list and no at the bottom.

We are only going to have one photo album per member; which means I don't have to find the profile photo album.  Would this query return what I want, all the members with at least one photo listed first, followed by members without a photo?

SELECT `ID` FROM `Profiles` LEFT JOIN `sys_albums` ON `Profiles`.`ID` = `sys_albums`.`Owner` ORDER BY `sys_albums`.`Owner` WHERE `sys_albums`.`ObjCount` <> 0 AND `sys_albums`.`Type` = 'bx_photos' ASC

I don't think it will; could I please get some assistance on how the query should be formed?  As I said, we are only going to have one photo album per user.

I know it won't, just trying it and I am getting a database error.

ORDER BY should be in after WHERE clause.

SELECT `ID` FROM `Profiles` LEFT JOIN `sys_albums` ON `Profiles`.`ID` = `sys_albums`.`Owner` WHERE `sys_albums`.`ObjCount` <> 0 AND `sys_albums`.`Type` = 'bx_photos' ORDER BY `sys_albums`.`Owner` ASC

I am not very sure what you want though :|

Thanks, I am very weak in mySQL; I need to take some classes on it but would have to travel 30 miles one way.  I have been reading :-).

What I want to do is to have the members block on the Home Page and Browse to list all the members with photos first, then list all the members without photos.  I know how to add a case to the code for the index block, just need to figure out how to get all the members with photos listed first in the members block.  In other words, there will be Lastest, Top, Online, and Sort by Photo,  Sort by photos will list all members; or I should have all active members, but it will sort by photo so that all photos are before no photos.  Since we only have one album, the profile photo album, I need to check to see if the profile photo album is not empty.

I am still getting a Database query error; I tried to turn on full error reporting but something is preventing it from working; it just spins and spins trying to load the error page.

I think this is what you want.

SELECT `p`.`ID` FROM `Profiles` AS `p` LEFT JOIN `sys_albums` AS `a` ON `a`.`Owner` = `p`.`ID` AND `a`.`ObjCount` > 0 AND `a`.`Type` = 'bx_photos' ORDER BY `a`.`ObjCount` DESC

Hello: I think that query will work.  Do you know how to check for items in the profile album itself?  I think the way that Dolphin works is that the first album in a members list of albums is the profile album but I don't know how to check.

Could you also take the time to explain your query?  I want to learn this mySQL stuff.

I don't care about how many profile photos they have, just if they have one.  How would I order by ID first, and then if they have a profile photo second?  Or is that too complex of a query?

I am using GROUP BY `a`.`ObjCount` and it does group all the photos together and all non-profile photos together.  Is there a way to sort the group; say by member ID?  So within the group of members with profile photos, is there a way to sort that group by member ID?

This is the answer I have found:

MySQL: How to Perform an Order By Sort before a Group By Action

Running an order by sort on a result set before applying the group by call – It’s a pretty common problem and an unfortunate result of the way in which MySQL has chosen to implement the SQL language specification.

At the heart of it all, MySQL performs any group by operation before an order by operation, meaning that you usually won’t get quite what you’re after – for example, if you are trying to get the last received feedback for every client in your database, you’ll quickly find that you fall short if you don’t fall back on this most basic of solutions to the order by before group by dilemma: running a subquery.

A subquery is a SELECT statement within another statement, and although you take a very minor performance hit, it does solve the problem very elegantly.

Essentially what you want to do is run your original statement with the order by applied, and then on this resulting statement (technically a temporary table), you then want to run the group by operation.

And luckily for us, it is as easy to implement as what it is to understand. In practice:

But I can not seem to get it to sort my results by Member ID and then group by ObjCount > 0.  Perhaps I need to change the original query.  Any help is appreciated.

My MySQL pea brain was not able to solve this, yet. 

Yea. Me either.

This is advanced MySQL. Kinda like the difference between basic math and algebra.

Well, not exactly, but the point is, one is easy the other not so easy.

Well, I have had a lot of maths, differential equations, Laplace transforms, Fourier transforms, not much help though in figuring out this problem. LOL

I am an idiot.  I kept uploading the wrong file to the server, no wonder nothing I did made a change in the results.

First I get:

Every derived table must have its own alias

Then trying to correct that:

Unknown column 'a.ObjCount' in 'group statement'

So I am still not getting anywhere.

To use the method posted, I need to rethink the whole query.

Going back to the original problem, I want to add a case to the Index Page's Member Block, by if they have a profile photo.  So all the profiles with photos need to be listed first; grouped by if profile photo, ordered by their ID number; followed by all profiles without a photo; ordered by Member ID.

SELECT p`.`ID`AS `p` FROM PROFILES LEFT JOIN `sys_albums` AS `a` ON `a`.`Owner` = `p`.`ID` AND `a`.`ObjCount` > 0 AND `a`.`Type` = 'bx_photos' ORDER BY `a`.`ObjCount` DESC";

That is returning a couple of duplicate entries even though the duplicate Member IDs only have one photo album each.

Let's walk though the query as I think I understand it; I am selecting all profile IDs from the Profiles table; I am joining the results with the sys_albums table so I can order the results of selecting the members based on if they have a photo album with Object Count greater than zero.  Why the duplicates in the results?

This is really eating up a lot of my time because of my weak MySQL skills; lots of reading on the net as well as asking here.  This is because they want to make the members blocks look pretty by grouping all the photos together.

So out of all those that came and read this post, Prashank25 is the only one that knows MySQL?

Try this one.

SELECT `p`.`ID` FROM `Profiles` AS `p` LEFT JOIN (SELECT * FROM `sys_albums` WHERE `ObjCount` > 0 AND `Type` = 'bx_photos' LIMIT 1) AS `a` ON `a`.`Owner` = `p`.`ID` ORDER BY `a`.`ObjCount` DESC

Thanks Deano, I think that will allow me to use this: