CODEWITHSUNDEEP
mysqlwildcard
lancemonotone
lancemonotone

Reputation: 135

MySQL: SELECT value WHERE subquery returns a wildcarded value

Confusing title...my apologies.

What I've got is a table with two related rows. I need to get the value of a column in one row based on the value of a column in another row.

Given the following postmeta table: 

+----------+------------+---------------------------------------------------+--------------------+
| meta_id  | post_id    | meta_key                                          |  meta_value        |
+----------+------------+---------------------------------------------------+--------------------+
| 6917     | 661        | member_categories_0_member_categories_name        | 11                 |
+----------+------------+---------------------------------------------------+--------------------+
| 6918     | 661        | member_categories_0_member_categories_description | First description  |
+----------+------------+---------------------------------------------------+--------------------+
| 6919     | 661        | member_categories_1_member_categories_name        | 12                 |
+----------+------------+---------------------------------------------------+--------------------+
| 6920     | 661        | member_categories_1_member_categories_description | Second description |
+----------+------------+---------------------------------------------------+--------------------+

I need to get the meta_value category description based on the meta_value category ID and the post_id. For instance, if my category ID is 11 and my post_id is 661, I should get "First description".

I thought about using a subquery to get the meta_key corresponding with a meta_value of '11', but I don't know how to find the description based on the counter inside 'member_categories_x_member_categories_name'.

Unfortunately, I don't have control over the name of the meta_key. I got as far as this simple query, which returns 'member_categories_0_member_categories_name'. How do I use that value to find 'First description'?

    SELECT pm.meta_key 
    FROM postmeta pm
    WHERE pm.meta_value = "11"
    AND pm.post_id = 661

Here's the SQL for the table:

CREATE TABLE `postmeta` (
    `meta_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `post_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `meta_key` VARCHAR(255) NULL DEFAULT NULL,
    `meta_value` LONGTEXT NULL,
    PRIMARY KEY (`meta_id`),
    INDEX `post_id` (`post_id`),
    INDEX `meta_key` (`meta_key`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=30814;

INSERT INTO `postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES (6917, 661, 'member_categories_0_member_categories_name', '11');
INSERT INTO `postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES (6918, 661, 'member_categories_0_member_categories_description', 'First description');
INSERT INTO `postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES (6919, 661, 'member_categories_1_member_categories_name', '12');
INSERT INTO `postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES (6920, 661, 'member_categories_1_member_categories_description', 'Second description');

Upvotes: 0

Views: 378

Answers (3)

Ivan Buttinoni
Ivan Buttinoni

Reputation: 4145

This's raw, but quicker than extract the number from the string,

SELECT pm2.meta_key,  pm2.meta_value 
FROM postmeta pm, postmeta pm2
WHERE pm.meta_value = "11"
AND pm.post_id = 661
and pm.meta_value = pm2.meta_value
AND pm.post_id = pm2.post_id
and substring(pm.meta_key,30) = substring(pm2.meta_key,30)

I apologizes, I made some mistakes, follows the correct one:

SELECT pm2.meta_value 
FROM postmeta pm, postmeta pm2
WHERE pm.meta_value = "11" 
AND pm.post_id = 661
and pm.meta_id <> pm2.meta_id
AND pm.post_id = pm2.post_id
and substring(pm.meta_key,19,20) = substring(pm2.meta_key,19,20)

As I told this's raw but uses (IMHO) less CPU from DB and made a better uses of indexes. Of course this is safe only for 20 digits.

of course if you have more than 2 meta_key (name and description) you should add: 

AND pm2.meta_key like '%_description'
AND pm.meta_key like '%_name'

This index is unuseful (meta_key is subject of elaboration): 

INDEX `meta_key` (`meta_key`)

better add this one:

INDEX `idx_post_id_meta_value` (`post_id`,`meta_value`)

Upvotes: 0

Saic Siquot
Saic Siquot

Reputation: 6513

I am not sure if I have fully understood what you want.

This gives the full "table", if you want to add a "where" clause you can add it at the end but for best performance, add inside the subqueries.

select
  n.*, d.meta_value as meta_value_descrip
from 
  (
   select left(meta_key, 34) as xjoin, p.*
   from postmeta p where right(meta_key, 4)  = 'name'
  ) as n
left join   -- or inner join
  (
   select left(meta_key, 34) as xjoin, p.*
   from postmeta p where right(meta_key, 11) = 'description'
   ) as d
on n.post_id = d.post_id and n.xjoin = d.xjoin

Upvotes: 0

Ian Clelland
Ian Clelland

Reputation: 44132

This is ugly, but then, that table is so far from normalized that any answer short of scanning every row is going to be something like this:

SELECT pm2.meta_value 
  FROM postmeta pm1
  JOIN postmeta pm2
    ON pm1.post_id = pm2.post_id
   AND SUBSTRING(pm1.meta_key,1,LENGTH(pm1.meta_key)-5) = SUBSTRING(pm2.meta_key,1,LENGTH(pm2.meta_key)-12)
   AND pm1.meta_key like '%_name'
   AND pm2.meta_key like '%_description'
 WHERE pm1.meta_value = 11
   AND pm1.post_id = 661

The idea is to join the table to itself, linking rows that have the same post_id, and whose meta_key is 'similar' -- it needs to be exactly the same, except that one ends with _name and one ends with _description.

Upvotes: 1

Related Questions