CODEWITHSUNDEEP
mysql
New_ToQT
New_ToQT

Reputation: 53

Joining two tables with a LIKE statement

I am trying to join my tables with a LIKE STATEMENT and my current query is this :

SELECT
    COUNT(comment_id) AS nb,
    produit.nom
FROM
    comments
INNER JOIN produit ON comments.comment_location like (select '%'+produit.id+'%')
GROUP BY
    comment_location

and the SQL FOR THE TABLES is this :

table comments:

CREATE TABLE `comments` (
 `comment_id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL,
 `comment_date` datetime NOT NULL,
 `comment_content` varchar(255) NOT NULL,
 `comment_location` varchar(255) NOT NULL,
 PRIMARY KEY (`comment_id`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8

table produit :

CREATE TABLE `produit` (
 `id` int(11) NOT NULL,
 `nom` varchar(25) NOT NULL,
 `description` varchar(200) NOT NULL,
 `categorie` varchar(25) NOT NULL,
 `image` varchar(200) NOT NULL,
 `prix` float NOT NULL,
 `quantite` int(11) NOT NULL,
 `prix_initiale` float NOT NULL,
 PRIMARY KEY (`id`),
 KEY `FK_CATEGORIE` (`categorie`),
 CONSTRAINT `FK_CATEGORIE` FOREIGN KEY (`categorie`) REFERENCES `categorie` (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

my query does execute but doesn't return the table i was looking for but if i try replacing produit.id in (select '%'+produit.id+'%') with a constant the table shows the corresponding rows .

What am i doing wrong

Upvotes: 0

Views: 49

Answers (2)

New_ToQT
New_ToQT

Reputation: 53

SELECT COUNT(comment_id) AS nb, produit.nom AS nom FROM comments INNER JOIN produit ON comments.comment_location LIKE CONCAT('%', produit.id, '%')

GROUP BY comment_location

by @Phil

Upvotes: 0

Venkataraman R
Venkataraman R

Reputation: 12959

You need to modify query as below. You dont need SELECT again in the LIKE clause. 

SELECT
    COUNT(comment_id) AS nb,
    produit.nom
FROM
    comments
INNER JOIN produit ON comments.comment_location like CONCAT('%',produit.id,'%')
GROUP BY
    comment_location

Upvotes: 1

Related Questions