CODEWITHSUNDEEP
mysqlsql
Maxim Chuprov
Maxim Chuprov

Reputation: 77

mysql order data from table using relations from another table

I have 2 tables
wares: Information about wares. Items have "usual" or "digital" type.

id | name | itemtype

CREATE TABLE IF NOT EXISTS `wares` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `itemtype` enum('usual','digital') NOT NULL DEFAULT 'usual',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

   INSERT INTO `wares` (`id`, `name`, `itemtype`) VALUES
    (1, 'ware1', 'usual'),
    (2, 'ware2', 'usual'),
    (3, 'ware3', 'usual'),
    (4, 'ware4', 'usual'),
    (5, 'ware5', 'usual'),
    (6, 'ware6', 'digital'),
    (7, 'ware7', 'usual'),
    (8, 'ware8', 'digital'),
    (9, 'ware9', 'usual'),
    (10, 'ware10', 'digital');

relations: table with relations between items from table wares. Some items with type "usual" related with item with type "digital". Not all items linked. 

id_usualware | id_digitalware

CREATE TABLE IF NOT EXISTS `relations` (
  `id_usualware` int(11) NOT NULL,
  `id_digitalware` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `relations` (`id_usualware`, `id_digitalware`) VALUES
(1, 6),
(4, 8),
(7, 10);

http://sqlfiddle.com/#!2/2831a/13

I need to select data from table wares using table relations ordering below. If item with type "usual" has relation with item "digital", then this digital item follows this "usual" in result. How can i do it using MySQL?

id | name  | itemtype
 1 | ware1 | 'usual'  
 6 | ware6 | 'digital'
 2 | ware2 | 'usual'
 3 | ware3 | 'usual'
 4 | ware4 | 'usual'
 8 | ware8 | 'digital'
 5 | ware5 | 'usual'
 7 | ware7 | 'usual'
 10| ware10| 'digital'
 9 | ware9 | 'usual'

Upvotes: 3

Views: 97

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The following should get the ids in the right order:

select coalesce(r.id_digitalware, w.id)
from wares w left join
     relations r
     on r.id_digitalware = w.id
order by coalesce(r.id_usualware, w.id),
         (w.itemtype = 'usual') desc;

To get the full rows, you need another join:

select w2.*
from wares w left join
     relations r
     on r.id_digitalware = w.id left join
     wares w2
     on w2.id = coalesce(r.id_digitalware, w.id)
order by coalesce(r.id_usualware, w.id),
         (w.itemtype = 'usual') desc;

Here is the SQL Fiddle.

Upvotes: 2

Related Questions