MySQL - How to order data from a secondary table

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
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

MySQL - How to order data from a secondary table

Post 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 :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

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

Post by Mr Tech »

Perfect! just what I wanted :)
Post Reply