Merianos Nikos Merianos Nikos - 6 months ago 30
SQL Question

How to select rows using IN clause and zerofill field in MySQL

I have the following table structure:

CREATE TABLE `ss_highlight_ids` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`highlight_id` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
PRIMARY KEY (`id`),
UNIQUE KEY `highlight_id_UNIQUE` (`highlight_id`),
KEY `highlight_id_INDEX` (`highlight_id`) COMMENT ''
) ENGINE=InnoDB AUTO_INCREMENT=13580 DEFAULT CHARSET=latin1


and I like to use the IDs of another table to select all rows having the same id in the
ss_highlight_ids
.
highlight_id
.

The query I use to select the rows is the following:

SELECT
*
FROM
`ss_highlight_ids` AS `hi`
WHERE
`hi`.`highlight_id` IN(
SELECT
LPAD( `p`.`ID`, 10, '0' ) AS `ID`
FROM
ss_posts AS `p`
WHERE
`p`.`post_type` = 'football-highlights'
AND `p`.`post_date` > DATE_SUB(NOW(), INTERVAL 1 DAY)
)


but unfortunately does not return anything. So the question is if I am doing something wrong or if I should do something else.

Please note that I have data related in my DB, and I have try instead of
IN
to use
=
with a single
ID
value and works, but doesn't work with the
IN
clause.

Answer

You should use the lpad also for zerofill column because i think the zero fill is use only for rendering but not for evalutation

SELECT  *
FROM  `ss_highlight_ids` AS `hi`
WHERE
lpad(`hi`.`highlight_id`, 10 , '0')  IN(
    SELECT  LPAD( `p`.`ID`, 10, '0' ) 
    FROM  ss_posts AS `p`
    WHERE `p`.`post_type` = 'football-highlights'
    AND `p`.`post_date` > DATE_SUB(NOW(), INTERVAL 1 DAY)
)