CODEWITHSUNDEEP
mysqlsql
JVMX
JVMX

Reputation: 1078

Order BY one table in MySQL

I have two tables (SQL Fiddle available here):

People

ID          Company_ID        Name
1            1                Jones
2            2                Smith
3            3                Kim
4            2                Takahashi
5            3                Patel
6            1                Muler

Companies 

ID           Name
1            QQQ
2            AAA
3            MMM

I wish to order a selection of People by the Name of the Company they work for.

SELECT * FROM People WHERE (Some where clause) ORDER BY HELP!

Upvotes: 0

Views: 38

Answers (3)

Juru
Juru

Reputation: 1629

You need to link the people table with the companies table to do that. You can do that by a cartesian product, which can be done by an explicit join clause or just a comma between each table in the from. A condition is also required to tell which field from the first table needs to correspond with the field of the second table, otherwise each row in table one is linked with each row of table two, which is not what you want in this case.

So this is what it would look like:

SELECT *
FROM   TABLE_ONE,
       TABLE_TWO
WHERE
       FOREIGN_KEY_IN_TABLE_ONE = PRIMARY_KEY_IN_TABLE_TWO
ORDER BY
       COLUMN_THAT_YOU_WANT_TO_SORT_ON

Other syntax with join clauses would go like this

SELECT *
FROM TABLE_ONE INNER JOIN TABLE_TWO ON FOREIGN_KEY_TABLE_ONE = PRIMARY_KEY_TABLE_TWO
ORDER BY
       COLUMN_THAT_YOU_WANT_TO_SORT_ON

Try to read up on the join methods. INNER JOIN is not the only possibility.

Upvotes: 1

Steve
Steve

Reputation: 11963

SELECT * FROM People INNER JOIN Companies ON Company_ID = Companies.ID ORDER BY Companies.Name

Upvotes: 1

Mureinik
Mureinik

Reputation: 311883

You should join both tables:

SELECT   p.* 
FROM     People p
JOIN     Companies c ON c.ID = p.CompaniyID
WHERE    --(Some where clause) 
ORDER BY c.Name ASC

Upvotes: 2

Related Questions