Conditional Query of some sort

[ignace]

Hi, I have 2 tables:

 

panden

id, pandenid, ..

 

fotos

id, pandenid, hoofdfoto, volgorde

 

I now want to construct a query that takes the picture that has hoofdfoto=1 or when it has no such value for that field it should take the highest number stored in volgorde. I tried

 

SELECT pd.id,ft.filename,max(volgorde)

FROM panden AS pd

LEFT JOIN fotos AS ft ON pd.pandenid=ft.pandenid

GROUP BY pd.pandenid

 

Without any luck. I am not even sure this is possible as the system is MySQL v4.0 which can't be upgraded because the application that uses it is very unstable and they have no idea what kind of implications it may have, another reason is the number of users that use the system daily.

 

Regards,

Ignace

 

 

[fenway]

I don't see your hoofdfoto restriction -- but you'll have to do this in two parts.  Add that restriction to your where clause, and then if that's null, you'll have to get the "overall" max value.

[ignace]

The last query I tried was:

 

SELECT pd.pandid,ft.filename,max(ft.volgorde) AS volgorde
FROM panden AS pd
LEFT JOIN fotos AS ft ON pd.pandid=ft.pandenid
WHERE ft.filename IS NOT NULL
GROUP BY pd.pandid
ORDER BY pd.pandid,if(ft.hoofdfoto=1,ft.hoofdfoto,volgorde) DESC

 

Except it ignores ft.hoofdfoto entirely while it should favor ft.hoofdfoto if it's value equals 1

 

@fenway damn I really would have wanted to do this in one query, thanks

[fenway]

That's because you're using a left join -- you need it in the ON clause.

[ignace]

But, the problem is hoofdfoto (main picture) will not always be 1 so if i write:

 

SELECT pd.pandenid,ft.filename,max(ft.volgorde) AS volgorde
FROM panden AS pd
LEFT JOIN fotos AS ft ON pd.pandenid=ft.pandenid AND ft.hoofdfoto=1
WHERE ft.filename IS NOT NULL
GROUP BY pd.pandid
ORDER BY pd.pandid,if(ft.hoofdfoto=1,ft.hoofdfoto,volgorde) DESC

 

I only get the records which have ft.hoofdfoto=1 while it should take the highest value for volgorde if hoofdfoto=0 so something like ON pd.pandenid=ft.pandenid AND (ft.hoofdfoto=1 XOR ?). But I have no clue as to how I could achieve this. Maybe ft.volgorde=volgorde (with an OR instead of XOR)?

[fenway]

So only == 0 is different?