SQL meta-related queries

Databases
1

hello i have 2 tables in mysql the table post and the table post_meta
I want to query the post table based on post_meta key/value data.
Any help why the second query not working?

example

//Show post where postmeta key[hide] = value[1]

SELECT
	*
FROM
	post AS mt0
INNER JOIN post_meta AS mt1 ON mt0.id = mt1.postid
WHERE
	(
		mt1.`key` = 'hide'
		AND CAST(mt1.`value` AS CHAR) = 1
	)

2 rows
1 Post test 1 3 1 hide 1
3 Post test 3 4 3 hide 1

//Show post where postmeta key[hide] != value[1]

SELECT
	*
FROM
	post AS mt0
INNER JOIN post_meta AS mt1 ON mt0.id = mt1.postid
WHERE
	(
		mt1.`key` = 'hide'
		AND CAST(mt1.`value` AS CHAR) != 1
	)

0 rows
//why? this query must return the post 2

DB schema

----table post

CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `post` VALUES ('1', 'Post test 1');
INSERT INTO `post` VALUES ('2', 'Post test 2');
INSERT INTO `post` VALUES ('3', 'Post test 3');


----table post_meta

CREATE TABLE `post_meta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `postid` int(11) DEFAULT NULL,
  `key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `post_meta` VALUES ('1', '1', 'name', 'leone');
INSERT INTO `post_meta` VALUES ('2', '2', 'name', 'mario');
INSERT INTO `post_meta` VALUES ('3', '1', 'hide', '1');
INSERT INTO `post_meta` VALUES ('4', '3', 'hide', '1');
2

No it doesn’t.
There is no row for post #2 and key ‘hide’ in the second table, so the INNER JOIN between the two tables will never return a row with post #2.

So you could add a row to the second table with postid 2, key ‘hide’ and value <> 1.
Or you should use a LEFT JOIN.

By the way, why do you cast the value of ‘value’ as a CHAR?

3

here the solution

SELECT *
FROM
post AS mt0
INNER JOIN post_meta mt1
ON mt0.id = mt1.postid
AND mt0.id NOT IN (
  select postid from post_meta where `key` = 'hide' AND CAST(`value` AS CHAR) = 1
);