MySQL - order of columns which are "hidden" by GRO

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ospi
Forum Newbie
Posts: 1
Joined: Tue Jun 14, 2005 3:10 am

MySQL - order of columns which are "hidden" by GRO

Post by ospi »

I ran into a problem when I tried to set the order of columns which are "hidden" by the group by clause.

for example there are two tables: metacard and description.
metacard table has the IDs and description contains descriptions of metacards in different languages. So the description table holds 4 columns,
1. description_id, description ID
2. metacard_id, metacard ID
3. description, description text
4. rl_id, language of the description (1=finnish, 2=english, 3=russian)

Code: Select all

SELECT mc.metacard_id, d.description 
FROM metacard mc, description d 
WHERE mc.metacard_id IN (531,530) 
AND mc.metacard_id=d.metacard_id 
GROUP BY metacard_id
Cards 531 and 530 both hold descriptions in two languages. In the result I only need one metacard + it's description per row, so I use GROUP BY metacard_id. But I can't figure out, howto force the the language of the description and have a "fallback" language instead of dropping the whole row if the language set by the user isn't available? In other words if user has chosen english for the UI and description for a metacard in result set isn't available in english it would use the description in finnish instead of dropping the row.

Thanks in advance!
Jesse
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: MySQL - order of columns which are "hidden" by

Post by timvw »

You could use MAX and MIN functions for d.description... But i couldn't find how MySQL handles NULL values in these functions (Are they always the smallest or largest?) http://dev.mysql.com/doc/mysql/en/group ... tions.html

Code: Select all

SELECT mc.metacard_id, MAX(d.description)
FROM metacard AS mc
INNER JOIN description AS d USING (metacard_id)
WHERE mc.metacard_id IN (531, 530)
GROUP BY metacard_id
Post Reply