MySQL - How to order data from a secondary table
Posted: Thu Mar 18, 2010 8:53 pm
OK, sorry my title gives no real explanation. I have no idea how to explain what I am asking in once sentence 
Basically I have two tables... First table is called lists. This table includes columns like the id and title etc... It's formatted like this:
The second table is called lists_meta. This table is formatted like this:
This basically stores all the custom data regarding the list so I don't have a billion columns in my lists table... Similar to wordpress if you're familiar with it 
Basically what I want to do is when I request results from the lists table, I am wanting to order those results by a certain meta_key's meta_value from the lists_meta table...
So an example would be I have a meta_key in the lists_meta table called list_link.. I want to order my results by the meta_value of any rows that have list_link as the meta_key
Here is my current query.. It's orders by meta_value but I have no idea how to only order by the meta_value of rows with list_link as the meta_key...
Hopefully I am making sense
Let me know if not and i'll try explain a little better 
Basically I have two tables... First table is called lists. This table includes columns like the id and title etc... It's formatted like this:
Code: Select all
id | list_title | list_descriptionCode: Select all
id | list_id | meta_key | meta_valueBasically what I want to do is when I request results from the lists table, I am wanting to order those results by a certain meta_key's meta_value from the lists_meta table...
So an example would be I have a meta_key in the lists_meta table called list_link.. I want to order my results by the meta_value of any rows that have list_link as the meta_key
Here is my current query.. It's orders by meta_value but I have no idea how to only order by the meta_value of rows with list_link as the meta_key...
Code: Select all
SELECT i.* FROM lists AS l LEFT JOIN lists_meta AS lm ON l.id = lm.list_id GROUP BY l.id ORDER BY lm.meta_value ASC