CODEWITHSUNDEEP
mysqlsqlsqlitejoin
Saim Malik
Saim Malik

Reputation: 15

How can I sort values in a table based on a column from another table and a column from the original table?

I'm having difficulty figuring out a query that will sort my table by a column from another table and also sort it by column from the table itself.

Suppose I have two tables, TableA(id, name, b_id) and TableB(id, name). I'm trying to create a query such that I can output rows in TableA ordered by TableB.name and TableA.name.

For example:

TableA
------
1 A4 11
2 A2 12
3 A3 13
4 A1 11

TableB
------
11 B1
12 B2
13 B3

Expected Output
---------------
4 A1 11
1 A4 11
2 A2 12
3 A3 13

To get rows ordered by TableB.name, the following worked:

SELECT * FROM TableA WHERE b_id IN (SELECT id FROM TableB);

(Wrong) Output
--------------
1 A4 11
4 A1 11
2 A2 12
3 A3 13

And to get rows order by TableA.name, the following worked:

SELECT * FROM TableA ORDER BY name;

(Wrong) Output
--------------
4 A1 11
2 A2 12
3 A3 13
1 A4 11

How can I combine the two queries to get a result like "Expected Output" above?

Upvotes: 0

Views: 38

Answers (2)

peak
peak

Reputation: 116949

output rows in TableA ...

So you might wish to consider using a LEFT JOIN to ensure you don't lose any rows in TableA:

SELECT a.*
FROM TableA a LEFT JOIN
     TableB b
     ON a.b_id = b.id
ORDER BY b.name, a.name;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270723

Is this what you want?

SELECT a.*
FROM TableA a JOIN
     TableB b
     ON a.b_id = b.id
ORDER BY b.name, a.name;

That said, for your sample in the question, this seems to work:

select a.*
from tablea a
order by a.b_id, a.name;

Upvotes: 1

Related Questions