Page 1 of 1

MySQL - How to order data from a secondary table

Posted: Thu Mar 18, 2010 8:53 pm
by Mr Tech
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:

Code: Select all

id | list_title | list_description
The second table is called lists_meta. This table is formatted like this:

Code: Select all

id | list_id | meta_key | meta_value
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...

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
Hopefully I am making sense :) Let me know if not and i'll try explain a little better :)

Re: MySQL - How to order data from a secondary table

Posted: Fri Mar 19, 2010 4:37 am
by VladSun
I'm not sure what you are asking, but I'll give it a try:
[sql]SELECT     lists.* FROM     lists LEFT JOIN     lists_meta ON         lists.id = lists_meta.list_id         AND         lists_meta.meta_key = 'list_link'GROUP BY     lists.id ORDER BY     lists_meta.meta_value ASC[/sql]

Re: MySQL - How to order data from a secondary table

Posted: Sun Mar 21, 2010 7:24 pm
by Mr Tech
Perfect! just what I wanted :)