Geek Answers Handbook

Using a GROUP_CONCAT column alias in a CASE statement using LIKE

The idea here is that GROUP_CONCAT compiles a list of option codes from the stock table connected to the option_stock tables and options grouped by stock ID. Example line:

Name Options Transmission 'Holden Commodore' '111, 145, 166, 188' 'Auto'

This performance works as it is, but I cannot help but feel a more elegant solution there?

 CREATE VIEW stock_view AS (select s.description AS Name, group_concat(o.option_code order by o.option_code ASC separator ', ') AS Options, (case WHEN group_concat(o.option_code) LIKE '%111%' then 'Auto' WHEN group_concat(o.option_code) LIKE '%112%' then 'Manual' else 'Other' end) as Transmission from stock s join option_stock ost ON s.id = ost.stock_id join options o ON o.id = ost.option_id group by s.id)

I am trying to avoid using this ugly kind of GROUP_CONCAT inside the CASE problem, but I get a message that the Options field does not exist if I use it inside the case argument as follows:

 WHEN `Options` LIKE '%111%' then 'Auto'

I know why the error occurs: this is because you cannot use the alias of another column in this way. But is there a way around this?

+4
source share
4 answers

These bits do not seem reliable enough to me:

 WHEN group_concat(o.option_code) LIKE '%111%' ... WHEN group_concat(o.option_code) LIKE '%112%' ...

LIKE '%111%' will match, for example. '111222, 145, 166, 188' is the same as '111, 145, 166, 188' . Unless, of course, there are only three-character codes, and you do not expect this to change soon.

However, I would probably use a different technique, most likely a conditional COUNT or SUM. For instance:

 (CASE WHEN SUM(o.option_code = '111') > 0 THEN 'Auto' WHEN SUM(o.option_code = '112') > 0 THEN 'Manual' ELSE 'Other' END) AS Transmission

Please also note that in your particular case, the following, although rather specific, solution should work:

 IFNULL( MIN(CASE o.option_code WHEN '111' THEN 'Auto' WHEN '112' THEN 'Manual' END), 'Other' ) AS Transmission

those. if '111' or, for some reason, both '111' and '112' are found among codes in the same group of lines, MIN() will return 'Auto' , if '112' bot not '111' , it will be evaluated to 'Manual' . Otherwise, it will be NULL, in which case the IFNULL() function will evaluate to 'Other' .

+1
source

Hard to test without actual tables (actually can't execute the statement), but what about this:

 CREATE VIEW stock_view AS (SELECT gc.Name, gc.Options, (case WHEN gc.Options LIKE '%111%' then 'Auto' WHEN gc.Options LIKE '%112%' then 'Manual' else 'Other' end) as Transmission FROM (select s.description AS Name, group_concat(o.option_code order by o.option_code ASC separator ', ') AS Options from stock s join option_stock ost ON s.id = ost.stock_id join options o ON o.id = ost.option_id group by s.id) gc);
0
source

Make a separate connection to determine the type of transmission:

 CREATE VIEW stock_view AS select s.description AS Name, group_concat(o.option_code order by o.option_code separator ', ') AS Options, if(t.option_code = '111', 'Auto', 'Manual') as Transmission from stock s join option_stock ost ON s.id = ost.stock_id join options o ON o.id = ost.option_id left join options t on t.id = ost.option_id and option_code in ('111', '112') group by s.id

Only one line will be connected to the transmission connection (the car cannot be both automatic and manual), and this approach avoids all these requests.

Creating a connection in the left connection means that the missing transfer option will be displayed as a guide. You can customize this by adding a test for null to have a default value.

0
source

You need to create two separate VIEWS because MySQL does not support a subquery in VIEW .

Try the following:

 CREATE VIEW stock_view AS SELECT s.description AS sname, GROUP_CONCAT(o.option_code ORDER BY o.option_code SEPARATOR ', ') AS soptions FROM stock s INNER JOIN option_stock ost ON s.id = ost.stock_id INNER JOIN OPTIONS o ON o.id = ost.option_id GROUP BY s.id; CREATE VIEW stock_view1 AS SELECT sname, soptions, (CASE WHEN FIND_IN_SET('111', soptions) THEN 'Auto' WHEN FIND_IN_SET('112', soptions) THEN 'Manual' ELSE 'Other' END) AS Transmission FROM stock_view;
0
source

More articles:


All Articles