CODEWITHSUNDEEP
mysqlsqljoin
Mattia Mancina
Mattia Mancina

Reputation: 119

Sql order by two tables

I have 2 relations: tables and tablegroups. One table can be in one tablegroup and one tablegroup contains multiple tables.

I need a select that returns the following output:

All tables from the tablegroup with the sortid 0 -> the tables again sorted by their sortid

and so on for all the tables.

Is this possible in Sql(Mysql dbms)?

Table

table_id, name, pax, createdate, lastupdate, tablegroup_id, sort_id, 

'2', 'tisch 02', '6', NULL, NULL, '1', '3' 
'3', 'tisch 03', '4', NULL, NULL, '1', '1'
'4', 'tisch 04', '2', NULL, NULL, '1', '2'
'5', 'tisch 05', '8', NULL, NULL, '1', '4'
'6', 'tisch 101', '4', NULL, NULL, '2', '1'
'7', 'tisch 102', '6', NULL, NULL, '2', '2'
'8', 'stube 01', '2', NULL, NULL, '3', '2' 
'9', 'stube 02', '3', NULL, NULL, '3', '1'

Tablegroups 

tablegroup_id, name, notiz, color, customer_id, sort_id, visible
'1', 'garten', NULL, '1', '1', '2', '1'
'2', 'lounge', NULL, '2', '1', '3', '1'
'3', 'stube', '', '7', '1', '1', '1'

Expected Output:

'stube 01' 
'stube 02' 
'tisch 03'
'tisch 04'
'tisch 05'
'tisch 101'
'tisch 102'

Upvotes: 0

Views: 74

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Inner join table and tablegroups on a common tablegroup_id and ORDER BY the sort_id of tablegroups and then the sort_id of table.

SELECT t.name
       FROM table t
            INNER JOIN tablegroups tg
                       ON tg.tablegroup_id = t.tablegroup_id
       ORDER BY tg.sort_id,
                t.sort_id;

Upvotes: 1

Related Questions