CODEWITHSUNDEEP
mysqlsql
Maxim Chuprov
Maxim Chuprov

Reputation: 77

mysql order data from table by column using relations from another table

My question is very similar to the previous. But add one condition, that i can't realize.
I have 2 tables
wares: Information about wares. Items have "usual" or "digital" type. Column "sortnum" means the primary sorting in result.

id | name | itemtype

CREATE TABLE IF NOT EXISTS `wares` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sortnum` int(11) NOT NULL,
  `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`, `sortnum`, `name`, `itemtype`) VALUES
(1, 8, 'ware1', 'usual'),
(2, 7, 'ware2', 'usual'),
(3, 4, 'ware3', 'usual'),
(4, 11, 'ware4', 'usual'),
(5, 21, 'ware5', 'usual'),
(6, 22, 'ware6', 'digital'),
(7, 2, 'ware7', 'usual'),
(8, 33, 'ware8', 'digital'),
(9, 15, 'ware9', 'usual'),
(10, 19, '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/e069f0/1

I need to select data from table wares using table relations primary ordering by 'wares'.'sortnum'. If item with type "usual" has relation with item "digital", then this digital item follows this "usual" in result. 

id | sortnum | name  | itemtype
 7 |    2    | ware7 | 'usual'
 10|    19   | ware10| 'digital'  
 3 |    4    | ware3 | 'usual'
 2 |    7    | ware2 | 'usual'
 1 |    8    | ware1 | 'usual'
 6 |    22   | ware6 | 'digital'
 4 |    11   | ware4 | 'usual'
 8 |    33   | ware8 | 'digital'
 9 |    15   | ware9 | 'usual'
 5 |    21   | ware5 | 'usual'

My version of the query doesn't work well:

SELECT w2.*, r.* 
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 ) 
WHERE w2.sortnum >0
ORDER BY 
  CASE WHEN (r.id_usualware IS NULL) THEN w2.sortnum END ASC , 
  CASE WHEN (r.id_usualware IS NOT NULL) THEN r.id_usualware END DESC

Please, help me with MySQL query.

Upvotes: 3

Views: 1811

Answers (1)

jpw
jpw

Reputation: 44871

I think you can solve this by adding another left join and changing the sort to use sortnum like this:

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)
left join wares w3 on r.id_usualware = w3.id
order by coalesce(w3.sortnum, w.sortnum), (w.itemtype = 'usual') desc;

Sample SQL Fiddle

With the sample data you provided in the fiddle this will output:

| ID | SORTNUM |   NAME | ITEMTYPE |
|----|---------|--------|----------|
|  7 |       2 |  ware7 |    usual |
| 10 |       9 | ware10 |  digital |
|  3 |       4 |  ware3 |    usual |
|  2 |       7 |  ware2 |    usual |
|  1 |       8 |  ware1 |    usual |
|  6 |      10 |  ware6 |  digital |
|  4 |      11 |  ware4 |    usual |
|  8 |       6 |  ware8 |  digital |
|  9 |      15 |  ware9 |    usual |
|  5 |      21 |  ware5 |    usual |

Actually, looking a bit further I think you can change the query to:

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

Sample SQL Fiddle for second version.

Upvotes: 2

Related Questions