Help with INNER JOIN and aliases


#1

Consider the following:

[php]SELECT
m.sub
, m.cdata
, m.description
, m.comment
, m.tab
, m.id
, p.cdata as ports
, cd.cdata as code
, m.changed as changed
FROM config m
INNER JOIN config p ON p.sub = m.sub
INNER JOIN config cd ON cd.command = CONCAT(’*2050’, m.sub)
WHERE
p.command = ‘*4005’
AND m.command = ‘*4002’[/php]

It reads the following tables from a MySQL database:

[code]INSERT INTO config (command, port, sub, cdata, description, type, changed, comment, id, help, defaultdata, tab, inputspec, specalt, prompt, maxsize) VALUES

(’*205001’, 0, NULL, ‘10901’, ‘Macro 1 recall’, ‘code’, 0, ‘’, 309, NULL, ‘10901’, ‘macrorecall’, NULL, NULL, NULL, 8 ),
(’*205002’, 0, NULL, ‘10902’, ‘Macro 2 recall’, ‘code’, 0, ‘’, 145, NULL, ‘10902’, ‘macrorecall’, NULL, NULL, NULL, 8 ),
(’*205003’, 0, NULL, ‘10903’, ‘Macro 3 recall’, ‘code’, 0, ‘’, 146, NULL, ‘10903’, ‘macrorecall’, NULL, NULL, NULL, 8 ),

(’*4002’, 0, ‘01’, ‘228 070 102 152 116 117 ‘, ‘Macro 1’, ‘Macro’, 0, ‘’, 233, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(’*4002’, 0, ‘02’, ‘210 123 027 ‘, ‘Macro 2’, ‘Macro’, 0, ‘’, 290, ‘Up to 15 three digit function numbers. Hint: click lookup function numbers are automatically added’, NULL, NULL, NULL, NULL, NULL, NULL),
(’*4002’, 0, ‘03’, '211 119 ', ‘Macro 3’, ‘Macro’, 0, ‘’, 200, NULL, NULL, NULL, NULL, NULL, NULL, NULL),

(’*4005’, 0, ‘01’, ‘123’, ‘Macro 1 Port Limit’, ‘prog’, 0, ‘’, 677, ‘’, ‘123’, ‘’, ‘1;2;3;12;13;23;123’, ‘1;2;3;1 and 2;1 and 3;2 and 3;1, 2, and 3’, ‘Ports:’, NULL),
(’*4005’, 0, ‘02’, ‘123’, ‘Macro 2 Port Limit’, ‘prog’, 0, ‘’, 678, ‘’, ‘123’, ‘’, ‘1;2;3;12;13;23;123’, ‘1;2;3;1 and 2;1 and 3;2 and 3;1, 2, and 3’, ‘Ports:’, NULL),
(’*4005’, 0, ‘03’, ‘123’, ‘Macro 3 Port Limit’, ‘prog’, 0, ‘’, 679, ‘’, ‘123’, ‘’, ‘1;2;3;12;13;23;123’, ‘1;2;3;1 and 2;1 and 3;2 and 3;1, 2, and 3’, ‘Ports:’, NULL),
[/code]

The above produces the first screencap attachment

I’m trying to get this to work after making the following changes to the table in order to make things more consistent as the data is used later:

('*2050', 0, '01', '10901', 'Macro 1 recall', 'code', 0, '', 309, NULL, '10901', 'macrorecall', NULL, NULL, NULL, 8 ), ('*2050', 0, '02', '10902', 'Macro 2 recall', 'code', 0, '', 145, NULL, '10902', 'macrorecall', NULL, NULL, NULL, 8 ), ('*2050', 0, '03', '10903', 'Macro 3 recall', 'code', 0, '', 146, NULL, '10903', 'macrorecall', NULL, NULL, NULL, 8 ),

The above change produces the 2nd screencap attachment

I am not a complete newbie (but I am a newbie!) but nothing I try with the INNER JOIN code fixes the problem


screen1.JPG

screen2.JPG


#2

What is the question?

http://sqlfiddle.com/#!9/11a6f8/1